Page 1 of 1
Is there a max number of rows a query can export as CSV?
Posted: Sat Jan 25, 2014 11:21 am
by simonmlewis
Code: Select all
$result = mysql_query ("SELECT DISTINCT subscribed_upload.email FROM subscribed_upload LEFT JOIN unsubscribed ON subscribed_upload.email = unsubscribed.email WHERE unsubscribed.email IS NULL") or die (mysql_error());
while($row = mysql_fetch_array($result))
{
if (filter_var($row[email], FILTER_VALIDATE_EMAIL))
{
$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=MailExport_" . date("Y-m-d") .".csv");
print $csv_output;
exit;
mysql_close($sqlconn);
echo "Extract in progress - one moment please...";
This code is running, looking through over 860,000 rows. Problem is, when it's running on my local machine or the live server, it just hangs.
Is there a maximum amount this sort of script can handle at one time?
Re: Is there a max number of rows a query can export as CSV?
Posted: Sat Jan 25, 2014 3:41 pm
by Christopher
860,000 rows! I would recommend using SELECT INTO OUTFILE to a temp file, then sending the file to the browser, then deleting the temp file. Much faster.
Re: Is there a max number of rows a query can export as CSV?
Posted: Sat Jan 25, 2014 3:46 pm
by simonmlewis
I beg your pardon??? I've never heard of any of that. How ?
Re: Is there a max number of rows a query can export as CSV?
Posted: Sat Jan 25, 2014 4:05 pm
by Celauran
Re: Is there a max number of rows a query can export as CSV?
Posted: Sat Jan 25, 2014 4:34 pm
by simonmlewis
Ta. Will look Monday when back to work mode!
Re: Is there a max number of rows a query can export as CSV?
Posted: Mon Jan 27, 2014 4:59 am
by simonmlewis
Done some checking, and found this:
Code: Select all
SELECT * INTO OUTFILE "/backup/books/allbooks.txt"
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
FROM books;
I need to adapt it to get DISTINCT records, but using JOINs.
Code: Select all
SELECT DISTINCT subscribed_upload.email FROM subscribed_upload LEFT JOIN unsubscribed ON subscribed_upload.email = unsubscribed.email WHERE unsubscribed.email IS NULL
Code: Select all
SELECT DISTINCT subscribed_upload.email INTO OUTFILE "/testoutput.txt"
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
FROM subscribed_upload LEFT JOIN unsubscribed ON subscribed_upload.email = unsubscribed.email WHERE unsubscribed.email IS NULL;
This looks wrong, but am I close?
Re: Is there a max number of rows a query can export as CSV?
Posted: Mon Jan 27, 2014 5:09 am
by simonmlewis
That just hangs too.
Re: Is there a max number of rows a query can export as CSV?
Posted: Mon Jan 27, 2014 5:53 am
by simonmlewis
I think it's partly down to size, and the error log that was building up with it - as when I run it with a LIMIT of just 10, it works, but I get a ton of these:
[text]<b>Notice</b>: Use of undefined constant email - assumed 'email' in <b>C:\xampp\phpMyAdmin\site\csv_mailmarketing.php</b> on line <b>11</b><br />
[/text]
This is the top of the code:
Code: Select all
$result = mysql_query ("SELECT DISTINCT subscribed_upload.email FROM subscribed_upload LEFT JOIN unsubscribed ON subscribed_upload.email = unsubscribed.email WHERE unsubscribed.email IS NULL LIMIT 0,10") or die (mysql_error());
while($row = mysql_fetch_array($result))
{
if (filter_var($row[email], FILTER_VALIDATE_EMAIL))
{
$csv_output .= '"'.$row[email].'"';
$csv_output .= "\015\012";
}
}
That error line is:
Code: Select all
if (filter_var($row[email], FILTER_VALIDATE_EMAIL))
Re: Is there a max number of rows a query can export as CSV?
Posted: Mon Jan 27, 2014 6:34 am
by Celauran
Which should read $row['email']
Re: Is there a max number of rows a query can export as CSV?
Posted: Mon Jan 27, 2014 6:38 am
by Celauran
Have you tried your queries without the INTO OUTFILE bit to make sure the queries themselves return results?
Re: Is there a max number of rows a query can export as CSV?
Posted: Mon Jan 27, 2014 8:28 am
by simonmlewis
We have changed the code now. I think it was down to the sheer time it was taking to process it all.
It's now done with a Limit, so they can choose where to start a finish. So they haev multiple files.
Row 1 - 100000, then 100001 to 200000 and so on. This is working.