Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

4 Answers

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");
share|improve this answer
add comment

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;
?>
share|improve this answer
add comment

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

share|improve this answer
1  
I need to execute this code from the front end of the application, not admin. –  Rob Bennet Feb 26 '11 at 6:43
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.