How do you export a MySQL table to CSV?
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
How do you export a MySQL table to CSV?
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.
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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
- markusn00b
- Forum Contributor
- Posts: 298
- Joined: Sat Oct 20, 2007 2:16 pm
- Location: York, England
Re: How do you export a MySQL table to CSV?
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.
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.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you export a MySQL table to CSV?
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.
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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do you export a MySQL table to CSV?
Um... he said... comma separated values.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.
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.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you export a MySQL table to CSV?
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...........
You are telling me to save it with a CSV file extension, but not 'how'.
I am flying blind here...........
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
- markusn00b
- Forum Contributor
- Posts: 298
- Joined: Sat Oct 20, 2007 2:16 pm
- Location: York, England
Re: How do you export a MySQL table to CSV?
You are pardoned.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.
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?
Create a file with a .csv filename extension using: http://us2.php.net/manual/en/function.fopen.phpsimonmlewis 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...........
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.
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: How do you export a MySQL table to CSV?
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);
- markusn00b
- Forum Contributor
- Posts: 298
- Joined: Sat Oct 20, 2007 2:16 pm
- Location: York, England
Re: How do you export a MySQL table to CSV?
Well, we could have all done that. Though I prefer to not spoon feed.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);
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you export a MySQL table to CSV?
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.
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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do you export a MySQL table to CSV?
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?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.
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
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you export a MySQL table to CSV?
Thanks for the 'support' mirage.
Why put a post like that on a thread that actually provides no support whatsoever???
Why put a post like that on a thread that actually provides no support whatsoever???
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do you export a MySQL table to CSV?
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.simonmlewis wrote:Thanks for the 'support' mirage.
Why put a post like that on a thread that actually provides no support whatsoever???
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you export a MySQL table to CSV?
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.
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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do you export a MySQL table to CSV?
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...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.