Created
September 10, 2024 17:53
-
-
Save ara303/5523fa8fa7bdd44e52e4943f962f484c to your computer and use it in GitHub Desktop.
WordPress database table import/export (as CSV)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * Plugin Name: Table Import/Export | |
| * Description: For internal use only. To access, WP-Admin > Tools > Table Import/Export. | |
| */ | |
| function table_import_export_menu(){ | |
| add_submenu_page( | |
| 'tools.php', | |
| 'Table Import/Export', | |
| 'Table Import/Export', | |
| 'manage_options', | |
| 'table-import-export', | |
| 'table_import_export_page', | |
| 20 | |
| ); | |
| } | |
| add_action( 'admin_menu', 'table_import_export_menu' ); | |
| function get_tables(){ | |
| global $wpdb; | |
| $tables = $wpdb->get_results( "SHOW TABLES", ARRAY_N ); | |
| $table_list = array(); | |
| foreach( $tables as $table ) { | |
| $table_list[] = $table[0]; | |
| } | |
| return $table_list; | |
| } | |
| function export_table( $table_name ){ | |
| global $wpdb; | |
| $results = $wpdb->get_results( "SELECT * FROM $table_name" ); | |
| $temp_file = fopen( 'php://output', 'w' ); | |
| $headings = array_keys( get_object_vars( $results[0] ) ); | |
| fputcsv($temp_file, $headings); | |
| foreach( $results as $row ){ | |
| fputcsv( $temp_file, (array)$row ); | |
| } | |
| fclose( $temp_file ); | |
| } | |
| function import_csv( $table_name, $file ){ | |
| global $wpdb; | |
| $skipped_rows = array(); | |
| $temp_file = fopen( $file, 'r' ); | |
| $headers = fgetcsv( $temp_file ); | |
| while( ( $row = fgetcsv( $temp_file ) ) !== FALSE ){ | |
| $data = array(); | |
| foreach( $headers as $key => $header ){ | |
| $data[$header] = $row[$key]; | |
| } | |
| $id = $data['id']; | |
| if( $wpdb->get_row( "SELECT * FROM $table_name WHERE id = '$id'" ) ){ | |
| $wpdb->update( $table_name, $data, array( 'id' => $id ) ); | |
| } else { | |
| $skipped_rows[] = $row; | |
| } | |
| } | |
| fclose( $temp_file ); | |
| return $skipped_rows; | |
| } | |
| // Admin page callback function | |
| function table_import_export_page(){ | |
| global $wpdb; | |
| if( isset($_POST['export_table'] ) ){ | |
| $table_name = $_POST['export_table']; | |
| header( 'Content-Type: text/csv' ); | |
| header( 'Content-Disposition: attachment; filename="'. $table_name. '.csv"' ); | |
| ob_end_clean(); // so we don't get WP_head() and other stuff | |
| export_table( $table_name ); | |
| exit; // prevents anything more being added after the data | |
| } elseif( isset( $_FILES['import_file'] ) && $_FILES['import_file']['error'] == 0 ){ | |
| $table_name = $_POST['import_table']; | |
| $skipped_rows = import_csv( $table_name, $_FILES['import_file']['tmp_name'] ); | |
| ?> | |
| <div class="updated"> | |
| <p>Import successful! The following rows were skipped because a matching ID for them does not exist within the database and this function does not support the addition of new data.</p> | |
| <ul> | |
| <?php foreach( $skipped_rows as $row ): ?> | |
| <li><?php echo implode( ', ', $row );?></li> | |
| <?php endforeach; ?> | |
| </ul> | |
| </div> | |
| <?php | |
| } | |
| ?> | |
| <div class="wrap"> | |
| <h1>Table Import/Export</h1> | |
| <h2>Export Table</h2> | |
| <form method="post"> | |
| <select id="export_table" name="export_table"> | |
| <?php foreach( get_tables() as $table ): ?> | |
| <option value="<?php echo $table; ?>"><?php echo $table; ?></option> | |
| <?php endforeach; ?> | |
| </select> | |
| <input type="submit" value="Export" /> | |
| </form> | |
| <h2>Import CSV File</h2> | |
| <form method="post" enctype="multipart/form-data"> | |
| <select id="import_table" name="import_table"> | |
| <?php foreach( get_tables() as $table ): ?> | |
| <option value="<?php echo $table; ?>"><?php echo $table; ?></option> | |
| <?php endforeach; ?> | |
| </select> | |
| <input type="file" name="import_file" /> | |
| <input type="submit" value="Import" /> | |
| </form> | |
| <script> | |
| // attempt to ensure the table names align for them by making it so when one's updated the other changes too | |
| document.getElementById('export_table').addEventListener('change', function() { | |
| document.getElementById('import_table').value = this.value; | |
| }); | |
| document.getElementById('import_table').addEventListener('change', function() { | |
| document.getElementById('export_table').value = this.value; | |
| }); | |
| </script> | |
| <p style="padding: 10px; border: 2px dashed red;">Note: When importing ensure that you have selected the correct table from the dropdown. At time of export the file name was the table's name.</p> | |
| </div> | |
| <?php | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment