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:
- 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.