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
MySQL documentation has examples.

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.