Activity 6: Simple Guestbook Plugin

<?php
/**
 * Plugin Name:     Simple Guestbook
 * Description:     A hands-on plugin for learning WordPress database integration. Creates a custom table for a guestbook.
 * Version:         1.0
 * Author:          Roselle O. Lopio
 */    

if (!defined('ABSPATH')) {
    exit;
}
function sg_install() {
global $wpdb;
$table_name = $wpdb->prefix . 'guestbook_entries';
$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
    name tinytext NOT NULL,
    email varchar(100) NOT NULL,
    message text NOT NULL,
    PRIMARY KEY  (id)
) $charset_collate;";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
    
}
register_activation_hook(__FILE__, 'sg_install');
function sg_guestbook_form_shortcode() {
    if ( isset($_POST['sg_submit']) && check_admin_referer('sg_new_entry_form') ) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'guestbook_entries';

        $name = sanitize_text_field($_POST['sg_name']);
        $email = sanitize_email($_POST['sg_email']);
        $message = sanitize_textarea_field($_POST['sg_message']);

        if ($name && $email && $message) {
$wpdb->insert(
$table_name,
array(
'time' => current_time('mysql'),
'name' => $name,
'email' => $email,
'message' => $message,
                )
            );
             echo '<p class="text-green-500">Thank you for signing the guestbook!</p>';
        } else {
            echo '<p class="text-red-500">Please fill out all fields.</p>';
        }
    }

    ob_start();
    ?>
    <form method="POST" action="">
        <?php wp_nonce_field('sg_new_entry_form'); ?>
        <div style="margin-bottom: 15px;">
            <label for="sg_name">Name:</label><br>
            <input type="text" id="sg_name" name="sg_name" style="width: 100%; padding: 8px;" required>
        </div>
        <div style="margin-bottom: 15px;">
            <label for="sg_email">Email:</label><br>
            <input type="email" id="sg_email" name="sg_email" style="width: 100%; padding: 8px;" required>
        </div>
        <div style="margin-bottom: 15px;">
            <label for="sg_message">Message:</label><br>
            <textarea id="sg_message" name="sg_message" rows="4" style="width: 100%; padding: 8px;" required></textarea>
        </div>
        <div>
            <input type="submit" name="sg_submit" value="Sign Guestbook" style="padding: 10px 20px; cursor: pointer;">
        </div>
    </form>
    <?php
    return ob_get_clean();
}
add_shortcode('simple_guestbook_form', 'sg_guestbook_form_shortcode');

function sg_guestbook_display_shortcode() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'guestbook_entries';

    $results = $wpdb->get_results("SELECT * FROM $table_name ORDER BY time DESC");

    if (empty($results)) {
        return '<p>No entries yet. Be the first to sign!</p>';
    }

    ob_start();
    ?>
    <div id="guestbook-entries" style="font-family: sans-serif; border-collapse: collapse; width: 100%;">
        <h3 style="text-align: center;">Guestbook Entries</h3>
        <?php foreach ($results as $row) : ?>
            <div style="border: 1px solid #ddd; padding: 15px; margin-bottom: 15px; border-radius: 5px;">
                <p><strong><?php echo esc_html($row->name); ?></strong>
                <em style="color: #777; font-size: 0.9em;">(<?php echo date('F j, Y, g:i a', strtotime($row->time)); ?>)</em></p>
                <p style="margin-top: 5px;"><?php echo nl2br(esc_html($row->message)); ?></p>
            </div>
        <?php endforeach; ?>
    </div>
    <?php
    return ob_get_clean();
}
add_shortcode('simple_guestbook_display', 'sg_guestbook_display_shortcode');

function sg_external_db_shortcode() {
    $ext_db_user = 'root'; // Your local MySQL username
    $ext_db_password = ''; // Your local MySQL password
    $ext_db_name = 'external_company_db';
    $ext_db_host = 'localhost';

    $external_db = new wpdb($ext_db_user, $ext_db_password, $ext_db_name, $ext_db_host);

    if (!empty($external_db->error)) {
        return '<p>Error: Could not connect to the external database.</p>';
    }

    $products = $external_db->get_results("SELECT product_name, inventory_count FROM products");
    
    if (empty($products)) {
        return '<p>No products found in the external database.</p>';
    }

    ob_start();
    ?>
    <h3>Product Inventory (from External DB)</h3>
    <table style="width: 100%; border-collapse: collapse;">
        <thead>
            <tr style="background-color: #f2f2f2;">
                <th style="padding: 8px; border: 1px solid #ddd; text-align: left;">Product Name</th>
                <th style="padding: 8px; border: 1px solid #ddd; text-align: left;">Stock</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($products as $product) : ?>
                <tr>
                    <td style="padding: 8px; border: 1px solid #ddd;"><?php echo esc_html($product->product_name); ?></td>
                    <td style="padding: 8px; border: 1px solid #ddd;"><?php echo intval($product->inventory_count); ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
    <?php
    return ob_get_clean();
}
add_shortcode('external_products_list', 'sg_external_db_shortcode');