Export wordpress DB table to excel

  • rwbennet

    (@rwbennet)


    14 years ago

    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? I would ultimately like to make this a plugin.

    Any help is greatly appreciated! This site is great!

Viewing 2 replies - 1 through 2 (of 2 total)

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

    Perfect, thank you!

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Export wordpress DB table to excel’ is closed to new replies.