Is there a max number of rows a query can export as CSV?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Is there a max number of rows a query can export as CSV?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Is there a max number of rows a query can export as CSV?

Post 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.
(#10850)
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Is there a max number of rows a query can export as CSV?

Post by simonmlewis »

I beg your pardon??? I've never heard of any of that. How ?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Is there a max number of rows a query can export as CSV?

Post by Celauran »

MySQL documentation has examples.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Is there a max number of rows a query can export as CSV?

Post by simonmlewis »

Ta. Will look Monday when back to work mode!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Is there a max number of rows a query can export as CSV?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Is there a max number of rows a query can export as CSV?

Post by simonmlewis »

That just hangs too.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Is there a max number of rows a query can export as CSV?

Post 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)) 
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Is there a max number of rows a query can export as CSV?

Post by Celauran »

Which should read $row['email']
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Is there a max number of rows a query can export as CSV?

Post by Celauran »

Have you tried your queries without the INTO OUTFILE bit to make sure the queries themselves return results?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Is there a max number of rows a query can export as CSV?

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