Export CSV from custom table through endpoint
-
In the plugin I am developing (basically it’s a custom form displayed n front-end which stores data in a custom mysql table) I have a back-end page where I can visualize all data entries.
A javascript code enables to check rows with an input checkbox. If checked, an ajax call send records ‘ids’ to an endpoint. Here I have a callback function that contains the php code to save data as csv. Everything works, in the sense that checked records ‘ids’ are correctly sent to the endpoint. The callback function grabs the ids and start a $wpdb query. Headers are prepared before echoing anything else and variables (first label row and data) are correctly stored in arrays. However the csv is not created. Here’s my code
Page with entries displayed
<?php //the full table with rows showing mysql records ?> <input type="button" id="export_records" class="button-primary" value="Export Selected"> <script> // export as csv selected records $('#export_records').on('click', function(e) { var row_data = []; $(".chk_id:checked").each(function() { row_data.push($(this).data('form-id')); }); if(row_data.length <=0) { alert("Please select records."); } else { WRN_PROFILE_EXPORT = "Are you sure you want to export "+(row_data.length>1?"these":"this")+" row?"; var checked = confirm(WRN_PROFILE_EXPORT); if(checked == true) { var selected_values = JSON.stringify(row_data); $.ajax({ type: "POST", url: "<?php echo get_rest_url(null, 'sight-form-tethys/v1/handle_export_entries'); ?>", contentType: "application/json", dataType: 'json', data: selected_values, success: function(response) { var form_ids = response.form_ids; WRN_PROFILE_EXPORTED = "You have exported "+form_ids.length+" rows."; confirm("You have exported "+form_ids.length+" rows."); $("#select_count").html(form_ids.length+" Exported"); }, error: function (xhr, status, error) { console.error(xhr.responseText); } }); } } }); </script>
The response of the ajax call is a JSON with the checked id (ex. [16,17])
The endpoint and relative callback function is as follows
<?php public function __construct( $plugin_name, $version ) { add_action( 'rest_api_init',array( $this,'sigh_form_handle_export_entries' )); } public function sigh_form_handle_export_entries(){ // route url: domain.com/wp-json/$namespace/$route $namespace = 'sight-form-tethys/v1'; $route = 'handle_export_entries'; register_rest_route($namespace, $route, array( 'methods' => 'POST', 'callback' => [$this, 'handle_export_entries_function'] )); } public function handle_export_entries_function(WP_REST_Request $data){ $result = $data -> get_params(); $result = implode(',', $result); if ( !empty($result) ) { // Set headers for CSV file download header('Pragma: public'); header('Expires: 0'); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Cache-Control: private', false); header('Content-Type: application/csv; charset=utf-8'); header('Content-Disposition: attachment; filename="csv_export.csv"' ); header('Content-Transfer-Encoding: binary'); // Create a file pointer connected to the output stream $output = fopen('php://output', 'a+'); // Retrieve data from your specific table global $wpdb; $table_name = $wpdb->prefix . 'sight_form_data'; // Replace 'your_table_name' with the name of your table $request =$wpdb->prepare("SELECT * FROM
$table_name
WHEREid_form
IN ($result)"); $entries = $wpdb->get_results($request, ARRAY_A); // Write headers to the CSV file $header_row = [ 'First name', 'Last name', 'Country', 'Email', 'Phone', 'Date sighting', 'Time sighting', 'Species', 'Confidence', 'Latitude', 'Longitude', 'N animals', 'Offsprings', 'Sea state', 'Want to be contacted', 'Privacy consent', 'Video url', 'File URL' ]; fputcsv($output, $header_row); // Write data rows to the CSV file foreach ($entries as $entry) { // Prepare an array with values to write to CSV $csv_data = array( $entry['first_name'], $entry['last_name'], $entry['country'], $entry['email'], $entry['phone'], $entry['date_sighting'], $entry['time_sighting'], $entry['species'], $entry['confidence'], $entry['latitude'], $entry['longitude'], $entry['n_animals'], $entry['offsprings'], $entry['sea_state'], $entry['contact'], $entry['consent_privacy'], $entry['video_url'], $entry['fileUrl'] // Assuming this is the field with file URLs ); // fputcsv($output, $csv_data); } // Close the file pointer fclose($output); $exported_ids = explode(',', $result); return new WP_REST_Response(array('form_ids' => $exported_ids), 200); } else { return new WP_REST_Response(array('message' => 'Something went wrong. Try again'), 400); } } ?>I checked in various forums, but it seems that the way I built the csv is correct: $csv_data and $header are correct. $output contains ‘Resource id #15’. By inspecting the request and response of the ajax call I have:
request: the JSON data with records ids
response: a long string similar to this
IkZpcnN0IG5hbWUiLCJMYXN0IG5hbWUiLENvdW50cnksRW1haWwsUGhvbmUsIkRhdGUgc2lnaHRpbmciLCJUaW1lIHNpZ2h0aW5nIixTcGVjaWVzLENvbmZpZGVuY2UsTGF0aXR1ZGUsTG9uZ2l0dWRlLCJOIGFuaW1hbHMiLE9mZnNwcmluZ3MsIlNlYSBzdGF0ZSIsIldhbnQgdG8gYmUgY29udGFjdGVkIiwiUHJpdmFjeSBjb25zZW50IiwiVmlkZW8gdXJsIiwiRmlsZSBVUkwiCkVsZW5hLFBvbGl0aSwsLCwwLDAsLCwwLDAsLCwseWVzLCJubyBhZ3JlZSIsLApyZWVydGVlcSxyZXdlcmYsLCwsMCwwLCwsMCwwLCwsLHllcywibm8gYWdyZWUiLCwKeyJmb3JtX2lkcyI6WyIxNiIsIjE3Il19
Can someone help please? Thanks very much
- The topic ‘Export CSV from custom table through endpoint’ is closed to new replies.