Page 2 of 2
Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 12:44 pm
by simonmlewis
Ok Markusn00b....
Looking at this:
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);
I see the top SELECT but unsure why you are selecting something as ID, why not just select id, email ? Why assign something as ID ?
$fp - is this a way to create a temporary file called file.csv, to output the result to? Should I be expecting to see a "Save As" type box come up? Also, what is the 'w' for?
I can see the "while" area coming into play, as it's producing the results to the various in the $fp line above it.
I have done this:
Code: Select all
$sql = "SELECT id as userid, email FROM users";
$result = mysql_query($sql);
$fp = fopen('file.csv', 'w');
while ($row = mysql_fetch_object($result))
{
fputcsv($fp,$row);
}
fclose($fp);
But all I get is:
Warning: fputcsv() expects parameter 2 to be array, object given in C:\xampp\phpmyadmin\site\includes\a_emailexport.inc on line 18
Warning: fputcsv() expects parameter 2 to be array, object given in C:\xampp\phpmyadmin\site\includes\a_emailexport.inc on line 18
Line 18 is:
I have written it to basically open the page and that runs the query straight away.
This is what I mean when I say I am flying blind. Even on reading various pages on the web about doing this apparently easy script, I am not getting terribly far from those who are trying helping.
I'm sure I am missing something rather daft here. I'm sure it's a case of extracting the data in a query, and putting it into rows on a temporary CSV, then outputting it to a file.
Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 12:47 pm
by Mirge
simonmlewis wrote:Ok Markusn00b....
Looking at this:
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);
I see the top SELECT but unsure why you are selecting something as ID, why not just select id, email ? Why assign something as ID ?
$fp - is this a way to create a temporary file called file.csv, to output the result to? Should I be expecting to see a "Save As" type box come up? Also, what is the 'w' for?
I can see the "while" area coming into play, as it's producing the results to the various in the $fp line above it.
I have done this:
Code: Select all
$sql = "SELECT id as userid, email FROM users";
$result = mysql_query($sql);
$fp = fopen('file.csv', 'w');
while ($row = mysql_fetch_object($result))
{
fputcsv($fp,$row);
}
fclose($fp);
But all I get is:
Warning: fputcsv() expects parameter 2 to be array, object given in C:\xampp\phpmyadmin\site\includes\a_emailexport.inc on line 18
Warning: fputcsv() expects parameter 2 to be array, object given in C:\xampp\phpmyadmin\site\includes\a_emailexport.inc on line 18
Line 18 is:
I have written it to basically open the page and that runs the query straight away.
This is what I mean when I say I am flying blind. Even on reading various pages on the web about doing this apparently easy script, I am not getting terribly far from those who are trying helping.
I'm sure I am missing something rather daft here. I'm sure it's a case of extracting the data in a query, and putting it into rows on a temporary CSV, then outputting it to a file.
while ($row = mysql_fetch_object($result)) <--- "mysql_fetch_
object"
...
Warning: fputcsv() expects parameter 2 to be
array, object given in C:\xampp\phpmyadmin\site\includes\a_emailexport.inc on line 18
Fetch an array instead of object.
Worry about getting it to write a correct CSV file first, then you can move onto generating a "Save As" dialog box.
Re: How do you export a MySQL table to CSV?
Posted: Sat Oct 24, 2009 1:06 pm
by simonmlewis
I am trying this as well that I have found elsewhere, which sort of makes sense. Put the "email" into the $row[] array, and then use the CSV output code in the header to do it.
Trouble is, I get a ton of errors.
Code: Select all
$result = mysql_query ("SELECT email FROM users");
while ($row = mysql_fetch_array($result))
{
$row[] = 'email';
$data .= join(',', $row)."\n"; // Join all values without any trailing commas and add a new line
// Output the headers to download the file
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=log.csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $data;
}
I was also reading a Tutorial on how to do it, to try and get it in my head, but they missed out the part where you select the data, so I just couldn't see how it all tied in.
Re: How do you export a MySQL table to CSV?
Posted: Wed Nov 04, 2009 6:03 am
by simonmlewis
And the answer was:
Code: Select all
$csv_output = '"email"';
$csv_output .= "\015\012";
$result = mysql_query("SELECT * FROM table");
while($row = mysql_fetch_array($result)) {
$csv_output .= '"'.$row[email].'"';
$csv_output .= "\015\012";
}
//You cannot have the breaks in the same feed as the content.
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv; filename=VSemails_" . date("Ymd") .
".csv");
print $csv_output;
exit;
The various posts in this thread, posted links all over the place and many ways to do it. Each giving a description, but none actually working for me.
This method was found on the web, including a tutorial telling me that the file that runs this has to be in the root of the server to avoid any white spaces (ie isn't an include file, but a PHP file of its own).
I don't know about other developers here, but I use this forum only when I hit a brick wall and have absolutely not clue why something is going wrong, or indeed how to do something that other pages on the web cannot help me with.
If someone here were to give me code like this, and explain what each step meant, that would have been the right answer to give. Rather than email-tennis and frustration caused by me and those appearing to try and help - just give someone the solution (if you know it!), and explain what it all means (so you are spoon feeding, but in a tutorial method).
I don't think I have even been to an IT Training course when the tutor says: "So, you are going to be doing this today. Here's a book, read it and learn it your bloody self". They tell you how to do something, explaining the steps, and then you go away and work through it stage by stage.
Just a few paragraphs there for the wise. If we need help, we need help in the form of your experience and what you know - rather than web page links showing various ways to do things.
Rant over.
Re: How do you export a MySQL table to CSV?
Posted: Wed Nov 04, 2009 6:54 am
by Mark Baker
So you fully understand exactly how this code works, including what purpose "\015\012" serves? The site you got it from explained every line for you?
The answer was given to you the best part of a month ago, using more efficient code than provided here.
RESOLVED Re: How do you export a MySQL table to CSV?
Posted: Wed Nov 04, 2009 8:17 am
by simonmlewis
Sorry, but if the answer was given to me with explanations, I would not have been trying for so long to make it work. Though I have not been working on it for a month!
My discovery it explained most of the code, yes. Not all.
I am not about to get into more email tennis about what "good help" is, as clearly some developer views are to show you something, and see if you can find your way. Some are to give you a few web site links with apparent answers in various staggered stages, but not their own experieince or knowledge. While some, in my view 'better' helpers, who have used code before, state: this does this, and this is how it works. None of the 'personal' code showed that.
I have come across many developers that help in a really good way. And some that hinder a little more than help When you are at the end of your tether, you don't need yet more hinderance: you need help, with answers and explanations. Not as riddles either.
I do not see any answer given to me that explains how the connection to the database works with it, how you place the 'rows' into an array, nor how to place them into the CSV rows, and then how it outputs that to an actual file.
Matter now closed. I simply wrote on here to end this conversation, and thank those that tried.
Re: How do you export a MySQL table to CSV?
Posted: Wed Nov 04, 2009 10:10 am
by Mirge
You were given code, VERBATIM... line for line... of how to do it. It's not our fault that we, as VOLUNTEERS, are not writing you a personalized tutorial just to satisfy your unsatisfiable need for spoon-feeding.
