How do you export a MySQL table to CSV?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
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?

Post 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.
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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you export a MySQL table to CSV?

Post 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.
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?

Post 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...........
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
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?

Post 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.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you export a MySQL table to CSV?

Post 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.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: How do you export a MySQL table to CSV?

Post 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);
 
User avatar
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?

Post 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. ;)
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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you export a MySQL table to CSV?

Post 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 :P.
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?

Post by simonmlewis »

Thanks for the 'support' mirage.
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.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you export a MySQL table to CSV?

Post 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.
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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you export a MySQL table to CSV?

Post 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...
Post Reply