Wavesdream

WordPress custom menu page, fetch data from custom MySQL table and export to CSV

Written by

Sanjay Bhowmick

in

To create a custom menu page in WordPress, retrieve custom table data from MySQL, and display it with the ability to export to CSV/Excel, you can follow these steps:

  1. Create a custom table in your WordPress database to store your data. You can use the $wpdb global variable to interact with custom tables in WordPress. Here’s an example of creating a custom table:
<?php
global $wpdb;
$table_name = $wpdb->prefix . 'custom_data';

$sql = "CREATE TABLE IF NOT EXISTS $table_name (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
) $charset_collate;";

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

Add the following code to your theme’s functions.php file or create a custom plugin file to define the custom menu page:

<?php
// Add menu page
function custom_menu_page()
{
    add_menu_page(
        'Custom Data',
        'Custom Data',
        'manage_options',
        'custom-data',
        'custom_menu_page_callback'
    );
}
add_action('admin_menu', 'custom_menu_page');

// Menu page callback function
function custom_menu_page_callback()
{
    global $wpdb;

    // Retrieve custom table data
    $table_name = $wpdb->prefix . 'custom_data';
    $results = $wpdb->get_results("SELECT * FROM $table_name", ARRAY_A);

    // Display custom table data
    echo '<div class="wrap">';
    echo '<h1>Custom Data</h1>';

    // Export to CSV/Excel button
    echo '<form method="post" action="' . admin_url('admin-post.php') . '">';
    echo '<input type="hidden" name="action" value="export_custom_data">';
    echo '<button type="submit" class="button">Export to CSV/Excel</button>';
    echo '</form>';

    // Display data in a table
    if ($results) {
        echo '<table>';
        echo '<thead><tr><th>Name</th><th>Email</th><th>Phone</th></tr></thead>';
        echo '<tbody>';
        foreach ($results as $row) {
            echo '<tr>';
            echo '<td>' . $row['name'] . '</td>';
            echo '<td>' . $row['email'] . '</td>';
            echo '<td>' . $row['phone'] . '</td>';
            echo '</tr>';
        }
        echo '</tbody>';
        echo '</table>';
    } else {
        echo '<p>No data found.</p>';
    }

    echo '</div>';
}

// Export to CSV/Excel action
function export_custom_data_action()
{
    global $wpdb;

    // Retrieve custom table data
    $table_name = $wpdb->prefix . 'custom_data';
    $results = $wpdb->get_results("SELECT * FROM $table_name", ARRAY_A);

    if ($results) {
        // Set headers for CSV/Excel file
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="custom_data.csv"');
        $output = fopen('php://output', 'w');

        // Write data rows to CSV/Excel file
        foreach ($results as $row) {
            fputcsv($output, $row);
        }

        fclose($output);
        exit;
    }
}
add_action('admin_post_export_custom_data', 'export_custom_data_action');
add_action('admin_post_nopriv_export_custom_data', 'export_custom_data_action');
?>

3. Save the changes to the functions.php file or your custom plugin file.

4. In your WordPress admin dashboard, you will see a new menu page called “Custom Data.” Clicking on this menu item will display the custom table data in a table format. The page will also have a button labeled “Export to CSV/Excel” that allows you to download the data in CSV/Excel format.

Note: Make sure to update the table name, column names, and the export file name according to your custom table structure and requirements.

This code creates a custom table, retrieves the data from the table using $wpdb, and displays it in an HTML table on the custom menu page. It also includes a form that, upon submission, triggers an action to export the data to CSV/Excel. The exported file is downloaded with the name “custom_data.csv” and includes the data rows from the custom table.

More posts