Exporting Data in a CSV format - via website is this possible

[Dan_]

Hi All,

I am currently learning about SQL (via some sitepoint books) and I was wondering if the following is possible.

I have a requirement to pull data from an SQL database which needs to be compiled into a .csv file. Currently I manually do this via phpMyAdmin which is a bit messy.

Ideally this would be coded into a page on the website so at a click of a button the file with the relevant data will be exported and saved locally. (Ie save file as..)

From my understanding you can query the database and have the data represented in a webpage (with some php help) but can it be set to export as a csv file?

If you can advise if this is possible or not, I can then focus on learning how to do it and trying it out.

Thanks

[ScallioXTX]

Yes that's possible.
Simple example: you want to export the name and age of all users
Create a file users.php with the following:

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="users.csv"');

$db = mysql_connect('your_host', 'your_username', 'your_password');
mysql_select_db('database_name', $db);

echo 'Name;Age', "\n"; // CSV headers

$res = mysql_query('SELECT name,age FROM users');
while ($row=mysql_fetch_assoc($res))
{
   echo '"', $row['name'], '"; ', $row['age'], "\n";
} 

The name needs to be in double quotes because it's a string and can contain ; that would mess up the whole csv. Quoting the values keeps the data "sane".

Content-Disposition: attachment forces the browser to download the file (show the download dialog to the user) instead of displaying it.

[Dan_]

Hi ScallioXTX,

Firstly thanks for your conformation and WOW thanks for the sample code.

I'll have a play with this and see how it performs.

I really appreciate your assistance and marking the sitepoint forums a valuable resource and learning tool.

I’ll report back my results.

[Dan_]

Hi ScallioXTX,

Well a long time since I last wrote but this project has been on the sidelines for a little while (now back into focus)

I have had a look through your example (which I appreciate, thank you) and whilst a little over my head I’m working my way through it (SQL is very new to me)

As the output table needs to be very specific in format, there will need to be some blank cells so the data corresponds with the correct cell. Can this be generated by the query, or should I creat a "temp" table for this?

Many thanks for your assistance.

[felgall]

As the output table needs to be very specific in format, there will need to be some blank cells so the data corresponds with the correct cell. Can this be generated by the query, or should I creat a "temp" table for this?

Just add extra commas where the blank cells are required.

[Dan_]

Hi felgall,

Many thanks for your assistance, I will implement your solution!

I have started playing with the code provided by ScallioXTX and it seems to be putting some of the data into IE, however it does not prompt you to save the data as a CSV, I must have missed something so I will play with this to see.

One other question I have regarding security, as this piece of script has the database username and password in it, how safe (or secure) is it in it's current form?

[Dan_]

unfortunately I'm unable to edit the above,

However i have fixed up the auto-saving of the file. It was a spacing error.

[felgall]

One other question I have regarding security, as this piece of script has the database username and password in it, how safe (or secure) is it in it's current form?

When you access a database using PHP it is quite normal to put the login data in a php file. THe only other alternative is to get each user to enter it which then requires that you publish it where all your users can see it and thus makes it less secure.