How to create a temporary table in MySQL to output table data as a CSV file?

I've got a script for creating a CSV file from a database table, which works fine except that it outputs all the data in the table, and I need it to output data only for the current logged in user. The app I'm developing is for users to be able to store lists of books they've read, want to read, etc., and I want to be able to allow them to download a list of their books they can import into Excel for example. (Would a CSV file be the best option for this, given that there's also OpenOffice etc. as well as MS Excel?)

I figured out that I needed to create a temporary table to use a select query to select only records belonging to the current logged-in user. I can access the data for the current user using WHERE username='$session->username'.

I thought it would be as easy as creating the temporary table before I try to run the queries that output the data for use in the CSV file, and then drop the table afterwards, but I've tried the CSV creation code again with the temporary table created before the CSV stuff, and again the CSV file produced includes all the records for all users.

There's a configuration file that sets the database connection properties, but also the table to be used to create the file, as the variable $table, and I've set this to be the name of the temporary table I'm trying to crea开发者_C百科te.

This is the code I've got:

<?
$link = mysql_connect($host, $user, $pass) or die("Cannot connect to the database." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$temp = mysql_query("CREATE TEMPORARY TABLE books_temp SELECT * FROM books WHERE username='$session->username'");
$tempresult = mysql_query($temp);

$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("d-m-Y") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
$query  = 'DROP TABLE books_temp';
$result = mysql_query($query);
exit;
?>

It works perfectly outputting all the data, but still outputs all the data when I try to use it with the temporary table. Incidentally, I've tried remove the instruction to drop the temporary table from the end as well, and that doesn't make any difference. Also, checking in PhpMyAdmin, it doesn't seem as though the temporary table's being created. I'm obviously doing something wrong here or missing something, but I've no idea what.

---

Well after a bit of a break from this I've come back to it, and as you guys said, I didn't actually need a temporary table at all.

For the sake of completeness, and incase anyone else encounters the same problem, this was the eventual solution to it.

With the when clause on the SELECT query the script works fine, I eventually discovered, but I still wasn't getting any data through (just the column names). I finally figured out the reason I wasn't getting any data was because although '$session->username' works within my PHP pages to access the name of the current user, the script, which is only linked to the page, has no way to access that session info.

Finally I worked out I needed to add in the extra variable $username and get the value for that from a query string on the URL (not quite sure how I was presuming the session info would get to the script - head wasn't in gear!). It was because the script couldn't access '$session->username' that the script wasn't working.

Rather than linking to the script with:

<a href=\"scripts/mysql2csv.php">Download your list</a>

I used this instead (after declaring the variable on the page):

$accname = $session->username;

and:

<a href=\"scripts/mysql2csv.php?user=$accname\">Download your list</a>

Then all I had to do was add the $username variable into the top of the script and amend the SELECT query with the variable.

$username = mysql_real_escape_string($_GET['user']);

At least it all works now! (I did try some of the suggestions on the other discussion, but couldn't get any to work - either I got only the header rows or a load of errors).

---

I have a poor man's approach to making a CSV file provided the column types are simple. It could be done by means of the CSV Storage Engine.

USE mydb
CREATE TABLE books_csv SELECT * FROM books WHERE 1=2;
ALTER TABLE books_csv ENGINE=CSV;
INSERT INTO books_csv SELECT * FROM books WHERE username='...';

When this step is done, the file /var/lib/mysql/mydb/books_csv.CSV exists.

You can open file how you wish. BTW all fields are enclosed with double quotes.

Give it a Try !!!