Export wordpress table to excel

Question

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!

Answer 1

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");

Answer 2

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;
?>

Answer 3

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');

Answer 4

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

Answer 5

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.