Crear un Plugin de WordPress para Capturar y Ejecutar Consultas SQL

Jun 13, 2024 | Diseño Web, Programación | 0 Comentarios

A continuaciíón se muestra la implementación de un Plugin que nos puede ayudar a generar consultas de información relevante de cualquier sitio WordPress de forma que pueda implementarse backed y solo presentar los resultados a un usuario final que requiera de información que se haya guardado en la base de datos.

La estrcutura del plugin es el siguiente:

sql-query-plugin/
├── assets/
│   ├── scripts.js
│   └── style.css
├── includes/
│   ├── database.php
│   ├── export.php
│   ├── form-handler.php
│   ├── form.php
│   ├── search.php
│   └── search-form.php
└── sql-query-plugin.php

Archivo Principal del Plugin: sql-query-plugin.php

<?php
/**
 * Plugin Name: SQL Query Plugin
 * Description: Plugin to capture, test, save, and search SQL queries.
 * Version: 1.0
 * Author: Your Name
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit; // Exit if accessed directly
}

define( 'SQP_PLUGIN_DIR', plugin_dir_path( __FILE__ ) );

// Include required files
require_once SQP_PLUGIN_DIR . 'includes/form-handler.php';
require_once SQP_PLUGIN_DIR . 'includes/database.php';
require_once SQP_PLUGIN_DIR . 'includes/search.php';
require_once SQP_PLUGIN_DIR . 'includes/export.php';

// Register activation hook
register_activation_hook( __FILE__, 'sqp_activate_plugin' );

// Enqueue scripts and styles
add_action( 'wp_enqueue_scripts', 'sqp_enqueue_assets' );

function sqp_activate_plugin() {
    sqp_create_database_table();
}

function sqp_enqueue_assets() {
    wp_enqueue_style( 'sqp-styles', plugins_url( 'assets/style.css', __FILE__ ) );
    wp_enqueue_script( 'sqp-scripts', plugins_url( 'assets/scripts.js', __FILE__ ), array( 'jquery' ), null, true );
    wp_localize_script( 'sqp-scripts', 'sqp_ajax_object', array(
        'ajax_url' => admin_url( 'admin-ajax.php' )
    ));
}

// Register shortcodes
add_shortcode( 'sql_query_form', 'sqp_display_form' );
add_shortcode( 'sql_query_search', 'sqp_display_search' );
?>

Archivo de Estilos: assets/style.css

/* Style for the form */
#sql-query-form {
    max-width: 800px;
    margin: 20px auto;
}

#sql-query-form div {
    margin-bottom: 15px;
}

#sql-query-form label {
    display: block;
    margin-bottom: 5px;
}

#sql-query-form textarea,
#sql-query-form input[type="text"] {
    width: 100%;
    padding: 10px;
    border: 1px solid #ccc;
    border-radius: 4px;
    box-sizing: border-box;
}

#sql-query-form button {
    padding: 10px 15px;
    background-color: #0073aa;
    color: #fff;
    border: none;
    border-radius: 4px;
    cursor: pointer;
}

#sql-query-form button:hover {
    background-color: #005177;
}

#sql-query-results {
    margin-top: 20px;
}

/* Style for the results grid */
#sql-query-results table {
    width: 100%;
    border-collapse: collapse;
}

#sql-query-results th,
#sql-query-results td {
    border: 1px solid #ccc;
    padding: 10px;
    text-align: left;
}

#sql-query-results th {
    background-color: #f2f2f2;
}

/* Style for search form and results */
#sql-query-search-form {
    max-width: 800px;
    margin: 20px auto;
}

#sql-query-search-results {
    margin-top: 20px;
}

Archivo de Scripts: assets/scripts.js

jQuery(document).ready(function($) {
    $('#sql-query-form').on('submit', function(event) {
        event.preventDefault();
        var formData = $(this).serialize();

        $.ajax({
            url: sqp_ajax_object.ajax_url,
            type: 'POST',
            data: formData + '&action=sqp_test_query',
            success: function(response) {
                $('#sql-query-results').html(response);
            }
        });
    });

    $('#sql-query-search-form').on('submit', function(event) {
        event.preventDefault();
        var formData = $(this).serialize();

        $.ajax({
            url: sqp_ajax_object.ajax_url,
            type: 'POST',
            data: formData + '&action=sqp_search_queries',
            success: function(response) {
                $('#sql-query-search-results').html(response);
            }
        });
    });
});

Manejo del Formulario: includes/form-handler.php

<?php
add_action( 'wp_ajax_sqp_test_query', 'sqp_test_query' );
add_action( 'wp_ajax_nopriv_sqp_test_query', 'sqp_test_query' );

function sqp_test_query() {
    if ( isset( $_POST['sql_query'] ) ) {
        global $wpdb;
        $sql_query = stripslashes( $_POST['sql_query'] );

        try {
            $results = $wpdb->get_results( $sql_query, ARRAY_A );
            if ( ! empty( $results ) ) {
                echo '<table>';
                echo '<tr>';
                foreach ( array_keys( $results[0] ) as $header ) {
                    echo '<th>' . esc_html( $header ) . '</th>';
                }
                echo '</tr>';
                foreach ( $results as $row ) {
                    echo '<tr>';
                    foreach ( $row as $cell ) {
                        echo '<td>' . esc_html( $cell ) . '</td>';
                    }
                    echo '</tr>';
                }
                echo '</table>';
                echo '<button id="export-to-excel">Export to Excel</button>';
            } else {
                echo '<p>No results found.</p>';
            }
        } catch ( Exception $e ) {
            echo '<p>Error: ' . esc_html( $e->getMessage() ) . '</p>';
        }
    }
    wp_die();
}
?>

Manejo de la Base de Datos: includes/database.php

<?php
function sqp_create_database_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'sqp_queries';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        sql_query text NOT NULL,
        query_name varchar(255) NOT NULL,
        query_description text NOT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}

function sqp_save_query( $sql_query, $query_name, $query_description ) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'sqp_queries';

    $wpdb->insert(
        $table_name,
        array(
            'sql_query' => $sql_query,
            'query_name' => $query_name,
            'query_description' => $query_description
        )
    );
}
?>

Manejo de la Búsqueda: includes/search.php

<?php
add_action( 'wp_ajax_sqp_search_queries', 'sqp_search_queries' );
add_action( 'wp_ajax_nopriv_sqp_search_queries', 'sqp_search_queries' );

function sqp_search_queries() {
    if ( isset( $_POST['search_term'] ) ) {
        global $wpdb;
        $search_term = '%' . $wpdb->esc_like( $_POST['search_term'] ) . '%';
        $table_name = $wpdb->prefix . 'sqp_queries';

        $results = $wpdb->get_results( $wpdb->prepare(
            "SELECT id, query_name, query_description FROM $table_name WHERE query_name LIKE %s OR query_description LIKE %s",
            $search_term, $search_term
        ), ARRAY_A );

        if ( ! empty( $results ) ) {
            echo '<table>';
            echo '<tr><th>Name</th><th>Description</th><th>Action</th></tr>';
            foreach ( $results as $row ) {
                echo '<tr>';
                echo '<td>' . esc_html( $row['query_name'] ) . '</td>';
                echo '<td>' . esc_html( $row['query_description'] ) . '</td>';
                echo '<td><button data-query-id="' . esc_attr( $row['id'] ) . '">Execute</button></td>';
                echo '</tr>';
            }
            echo '</table>';
        } else {
            echo '<p>No results found.</p>';
        }
    }
    wp_die();
}
?>

Exportación a Excel: includes/export.php

<?php
function sqp_export_to_excel( $data ) {
    // Implementación de la exportación a Excel.
}
?>

Shortcode del Formulario: includes/form.php

<?php
function sqp_display_form() {
    ob_start();
    ?>
    <form id="sql-query-form">
        <div>
            <label for="sql_query">SQL Query:</label>
            <textarea id="sql_query" name="sql_query" required></textarea>
        </div>
        <div>
            <label for="query_name">Query Name:</label>
            <input type="text" id="query_name" name="query_name" required>
        </div>
        <div>
            <label for="query_description">Query Description:</label>
            <textarea id="query_description" name="query_description" required></textarea>
        </div>
        <button type="submit">Test Query</button>
    </form>
    <div id="sql-query-results"></div>
    <?php
    return ob_get_clean();
}
?>

Shortcode de Búsqueda: includes/search-form.php

<?php
function sqp_display_search() {
    ob_start();
    ?>
    <form id="sql-query-search-form">
        <div>
            <label for="search_term">Search:</label>
            <input type="text" id="search_term" name="search_term" required>
        </div>
        <button type="submit">Search</button>
    </form>
    <div id="sql-query-search-results"></div>
    <?php
    return ob_get_clean();
}
?>

Resumen

Este conjunto de archivos y código proporciona una guía completa para crear un plugin de WordPress que:

  • Captura consultas SQL, nombres y descripciones a través de un formulario.
  • Permite probar la consulta y mostrar resultados en una rejilla.
  • Guarda las consultas válidas en una tabla de la base de datos.
  • Permite buscar consultas guardadas y ejecutarlas.

Enviar comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *