Page 1 of 2
How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 8:30 am
by simonmlewis
I have a web site that stores certain info, and need to give the user the ability to click a button marked "Export as CSV".
How do I make that button export, let's say, table name "products", and field names "title, description, price"..... to a CSV file?
Can anyone help as I haven't got a clue.
Re: How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 11:28 am
by markusn00b
CVS files are stupidly easy to work with. The name itself should be a signal of that 'Comma-separated Values'.
So, simply, to create a CVS file from your mysql table: for each row of the table on a single line, print each column for that row separated by a comma.
Have a go
Mark.
Re: How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 11:38 am
by simonmlewis
Pardon?
That doesn't really tell me or give any clue to how to Export as .CSV so for example, it can be opened in Excel.
Re: How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 11:59 am
by Mirge
simonmlewis wrote:Pardon?
That doesn't really tell me or give any clue to how to Export as .CSV so for example, it can be opened in Excel.
Um... he said... comma separated values.
foo,bar,baz <--- comma separated values.
price,title,description,product_id,whatever
price,title,description,product_id,whatever
price,title,description,product_id,whatever
price,title,description,product_id,whatever
price,title,description,product_id,whatever
price,title,description,product_id,whatever
Save that file with a .csv extension, and Excel will open it.
You could also wrap each field with double quotes... or replace comma (,) with a tab (\t)... to create a tab separated values file.
Re: How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 12:13 pm
by simonmlewis
Sorry guys, I have not got a clue what you mean.
You are telling me to save it with a CSV file extension, but not 'how'.
I am flying blind here...........
Re: How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 12:14 pm
by markusn00b
simonmlewis wrote:Pardon?
That doesn't really tell me or give any clue to how to Export as .CSV so for example, it can be opened in Excel.
You are pardoned.
It gives you every clue. You read each row from your database into a file, separating each column with a comma. Hey presto.
Re: How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 12:23 pm
by Mirge
simonmlewis wrote:Sorry guys, I have not got a clue what you mean.
You are telling me to save it with a CSV file extension, but not 'how'.
I am flying blind here...........
Create a file with a .csv filename extension using:
http://us2.php.net/manual/en/function.fopen.php
Write to the file using, separating field values with a comma:
http://us3.php.net/fwrite/
Close the file using:
http://us.php.net/fclose/
IE:
$fp = fopen("foo.csv", "w") or die("Failed opening file.");
fwrite($fp, "foo,bar,baz,burk\n");
fwrite($fp, "foo2,bar2,baz2,burk2\n");
fwrite($fp, "foo3,bar3,baz3,burk3\n");
fclose($fp);
And if that isn't helpful, try
clicking here.
Re: How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 12:26 pm
by Mark Baker
Code: Select all
$sql = "SELECT id as userid, fullname, userstatus
FROM sometable
WHERE userstatus = 1";
$result = mysql_query($sql);
$fp = fopen('file.csv', 'w');
while ($row = mysql_fetch_assoc($result)) {
fputcsv($fp,$row);
}
fclose($fp);
Re: How do you export a MySQL table to CSV?
Posted: Wed Oct 21, 2009 12:33 pm
by markusn00b
Mark Baker wrote:Code: Select all
$sql = "SELECT id as userid, fullname, userstatus
FROM sometable
WHERE userstatus = 1";
$result = mysql_query($sql);
$fp = fopen('file.csv', 'w');
while ($row = mysql_fetch_assoc($result)) {
fputcsv($fp,$row);
}
fclose($fp);
Well, we could have all done that. Though I prefer to not spoon feed.

Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 6:25 am
by simonmlewis
Sorry - how does work with a Form Button link to "Export as CSV"?
I can see an SQL query, but not a clue how to make that actually happen - unless the button is directed to a page that has that query.
Spoonfeeding or not - I am walking blind here. Sometimes you have to show a blind man the way.
Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 11:52 am
by Mirge
simonmlewis wrote:Sorry - how does work with a Form Button link to "Export as CSV"?
I can see an SQL query, but not a clue how to make that actually happen - unless the button is directed to a page that has that query.
Spoonfeeding or not - I am walking blind here. Sometimes you have to show a blind man the way.
You were given the exact code damn near to do what you want. You don't know how to slap a button on a page that sends them to a php script that does the work?
EDIT: Not to be rude, but if you don't know what to do at this point, you just aren't trying at all. If you want code written for you, go to the volunteer work section

.
Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 12:00 pm
by simonmlewis
Thanks for the 'support' mirage.
Why put a post like that on a thread that actually provides no support whatsoever???
Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 12:04 pm
by Mirge
simonmlewis wrote:Thanks for the 'support' mirage.
Why put a post like that on a thread that actually provides no support whatsoever???
You're welcome, semenmlewis (Yes, I can mis-spell your name too). I already posted multiple times with how you can achieve the end result that you want. You just aren't willing to put forth the work to get it done and are expecting somebody to write your code for you. That isn't teaching you anything... that's spoon-feeding you.
Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 12:23 pm
by simonmlewis
Maybe, but I am not being offensive or defensive like you seem to be.
It's more productive to be supportive and helpful (even without spoonfeeding which you seem to think you'd be doing), without adding your particular brand of attitude on the end of it.
Help I need - attitude and rudeness I do not need.
I didn't spell your name wrongly on purpose.
Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 12:35 pm
by Mirge
simonmlewis wrote:Maybe, but I am not being offensive or defensive like you seem to be.
It's more productive to be supportive and helpful (even without spoonfeeding which you seem to think you'd be doing), without adding your particular brand of attitude on the end of it.
Help I need - attitude and rudeness I do not need.
I didn't spell your name wrongly on purpose.
You received help, LOTS of it... try reading it and trying to understand it.. experiment with it, and make it work. You've literally been given working code...