Export wordpress table to excel

I have created PHP scripts before to export a database table to .xls format like this:

$select = "SELECT * FROM tracking";
$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( )  );
$fields = mysql_num_fields ( $export );
$file = 'export';
for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $export , $i ) . "\t";
}
while( $row = mysql_fetch_row( $export ) )
{
$line = '';
foreach( $row as $value )
{
    if ( ( !isset( $value ) ) || ( $value == "" ) )
    {
        $value = "\t";
    }
        else
    {
        $value = str_replace( '"' , '""' , $value );
        $value = '"' . $value . '"' . "\t";
    }
        $line .= $value;
 }
 $data .= trim( $line ) . "\n";
 }
 $data = str_replace( "\r" , "" , $data );
 if ( $data == "" )
 {
 $data = "\n(0) Records Found!\n";
 }

$filename = $file."_".date("M-d-Y");

header("Content-type: application/octet-stream");
header( "Content-disposition: filename=".$filename.".xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

What I’d like to know is how to “convert” this to wordpress format using the $wpdb class? I can get the initial query down to actually select the data from the table I want, but I get lost in some of the semantics of the wpdb class. I also wanted to know where I would put this code in wordpress, so if say a user clicks on a link, it would run this query and download the .xls file?

Any help is greatly appreciated! This site is great!

Solutions Collecting From Web of "Export wordpress table to excel"

Why not use the SELECT INTO OUTFILE syntax:

$wpdb->query("SELECT * INTO OUTFILE '/path/to/file' 
 FIELDS TERMINATED BY '\t' 
 LINES TERMINATED BY '\n' 
 FROM tracking");

create this as a ‘export_data.php’ file.
Then call this php from the link

<?php
$host = 'localhost';
$user = 'mysqlUser';
$pass = 'myUserPass';
$db = 'myDatabase';
$table = 'products_info';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field']."; ";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j]."; ";
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

If you have phpmyadmin, then just go to export and select Excel instead of SQL.

I am definitely late to help out with my answer .. I hope 🙂 , but I am aiming to anyone else who needs an updated WordPress function to export custom table to a nice and clean row display CSV file

    function ns_contact_form_csv_pull() {

       global $wpdb;

       $table = 'ns_contact_form';// table name
       $file = 'ns_contact_form_csv'; // csv file name
       $results = $wpdb->get_results("SELECT * FROM $wpdb->prefix$table",ARRAY_A );

       if(count($results) > 0){
          foreach($results as $result){
          $result = array_values($result);
          $result = implode(", ", $result);
          $csv_output .= $result."\n";
        }
      }

      $filename = $file."_".date("Y-m-d_H-i",time());
      header("Content-type: application/vnd.ms-excel");
      header("Content-disposition: csv" . date("Y-m-d") . ".csv");
      header( "Content-disposition: filename=".$filename.".csv");
      print $csv_output;
      exit;

    }
   add_action('wp_ajax_csv_pull','ns_contact_form_csv_pull');

Download the CSV ( need to be logged in ) just add this URL to your browser http://example.com/wp-admin/admin-ajax.php?action=csv_pull … change domain with yours 🙂

You can call it anywhere in WordPress by using:

$ajax_url = admin_url('admin-ajax.php?action=csv_pull');

Something like this:

$results = $wpdb->get_results('SELECT * FROM tracking;');

See SELECT generic results in Codex for details.

Not sure on how to best implement link to this, page with custom named template might do the trick.