CSV File too large

[dc_jt]

Ive created a script so that gets all users from my database by two dates and loops through them then downloads this as a csv. This is how its done (the main part anyway)@

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=users.csv");
header("Pragma: no-cache");
header("Expires: 0");

$aUsers = $oTblUsers->getUsers($startdate, $enddate);

$date=date('Y')."-".date('m')."-".date('d');

header('Pragma: ');
header('Cache-control: ');
header("Content-type: application/force-download");
header("Content-disposition: attachment; filename=".$date."competition_entries.csv");

$top = 'Title, First Name, Surname, Address 1, Address 2, City, Email Address, Date of Birth, Telephone Number, Mobile Number, Postcode, Date Added';
echo ($top);
echo("\n");

if($aUsers)
   foreach ($aUsers as $aItem){

	echo ('"'.$aItem[title].'",');	
	echo ('"'.$aItem[name].'",');	
	echo ('"'.$aItem[surname].'",');
	echo ('"'.$aItem[address1].'",');
	echo ('"'.$aItem[address2].'",');
	echo ('"'.$aItem[city].'",');
	echo ('"'.$aItem[email].'",');
	echo ('"'.$aItem[date_of_birth].'",');
	echo ('"'.$aItem[telephone].'",');
	echo ('"'.$aItem[mobile].'",');
	echo ('"'.$aItem[postcode].'",');
	echo ('"'.$sDateAdded.'",');
	echo("\n");


}

 

Now this has been working however I noticed today that if I put 5/10/2008 in until the 1/1/2009 then it doesnt work and in Chrome I get a message saying page not found. In Firefox it doesnt do anything at all. If I do it from the 6/10/2008 to the 1/1/2009 then it works, however there is over 16,000 records and is almost 3mb in size.

 

Is this why it wont work because the filesize or amount of records is too much? What can I do?

 

Thanks

 

 

[nadeemshafi9]

you need to create indexes on your query, find out how to create indexes on your query and then your sql statment will be lightning fast, i had teh same issue until i made indexes, i was using date to refine aswell lol, i dont knwo how to do it manualy in phpmyadmin, but if your using navicat then its prety simple

 

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

 

 

 

navicat

Indexes are organized versions of specific columns in your tables. MySQL uses indexes to facilitate quick retrieval of records. With indexes, MySQL can jump directly to the records you want. Without any indexes, MySQL has to read the entire data file to find the correct record(s).

 

Table indexes are managed on the Indexes tab of the Table Designer. Just simply click/double-click an index field for editing. A right-click displays the popup menu or using the toolbar below, allowing you to create new, edit and delete the selected index field.

 

 

----

any column which is defined as UNIQUE or PRIMARY KEY is automatically indexed. You can optionally define any other column as being indexed in order to improve SELECT performance, presumably on columns which are likely to be used in WHERE clauses. (Hopefully some SQL guru here will chime in and either confirm or deny the above.

----

 

phpmyadmin

 

i think if you go to table structure then indexis

 

[dc_jt]

Thanks for your help. I have never really used indexes to be honest but I have just tried adding an index to the job_id field and the date_added. This is because the query I am running selects all users where job_id equals the id passed in and the date_added is between the two dates passed in.

 

I simply just clicked on "index" next to the two fields, is that all that is needed because it still doesnt seem to work?

 

 

 

[gffg4574fghsDSGDGKJYM]

2 thing can kill your script either execution time or it eat too much memory.

 

What nadeemshafi9 say may work assuming your script is killed because it take too much time and it take too much time because you don't have add the proper indexing to your row.

 

You have to make your script run faster, use less memory and/or configure your php.ini to increase the max execution time and memory.

 

If your script get killed because the timeout in browser you can try this :

 

- Desactivate any mod_gzip, mod_deflate for this file and use a flush(); after each CSV echo line;

http://www.php.net/manual/en/function.flush.php

The browser will start receiving data as soon as you flush them it won't timeout, it may not work if your script take too much memory or take too much time to execute. If it work and you receive a incomplete file you can increase the max execution time and memory in your php.ini.

 

- If your MySQL server is on the same machine as your PHP you can use

SELECT ... INTO OUTFILE '/tmp/result.txt' ... and then readfile(), be sure to use a unique filename if you want this to be thread safe.

http://www.php.net/manual/en/function.readfile.php

http://dev.mysql.com/doc/refman/5.1/en/select.html

MySQL SELECT INTO OUTFILE can be used to output a CSV file and will probably be a lot faster than any php script making the same job. (And probably use less memory too).

 

- Tweaking your sql query, datatype and add index will make the sql work faster if the problem is coming from that.

 

There probably a lot more you can do to solve this but none that i can think right now.

 

Hope it help.

[nadeemshafi9]

we have 6 million records and it was taking 1 min to do SELECT and then we done indexes reduced it to 2 seconds, i aggree try to use unset and things like that inside your loops and try not to create redundancey etc, the script time can be made longer in your php ini but thats not the point by then people will think it has crashed. You may need to do it via ajax with aspinny thiunk why dont you adopt a framework.

[soak]

If you wish to post the full query that's being called against the database we can advise on what you need to index on.

 

EDIT: Meant to say: You can't necessarily just add an index to a couple of columns (although it may help somewhat), you need to get the index correct for the query.

[nadeemshafi9]

If you wish to post the full query that's being called against the database we can advise on what you need to index on.

 

EDIT: Meant to say: You can't necessarily just add an index to a couple of columns (although it may help somewhat), you need to get the index correct for the query.

 

im new to indexing myself

[tripwater]

One thing I use is the 'explain' in front of the select query so in mysql I had a query like this

 

explain Select Contact.ContactID, Businesses.BusinessName from Contact, Businesses where ContactID.ContactID = Businesses.ContactID

 

This will give you info on how many rows, tables, indexes and help you isolate huge query problems. Google sql explain. We use indexes all teh time and link many tables together and just recently started using the 'expain' and caught many potential issues.

 

Hope that helps some.

[nadeemshafi9]

One thing I use is the 'explain' in front of the select query so in mysql I had a query like this

 

explain Select Contact.ContactID, Businesses.BusinessName from Contact, Businesses where ContactID.ContactID = Businesses.ContactID

 

This will give you info on how many rows, tables, indexes and help you isolate huge query problems. Google sql explain. We use indexes all teh time and link many tables together and just recently started using the 'expain' and caught many potential issues.

 

Hope that helps some.

 

wow thanks for that i will defo start implameting it now into a debug typpe mode of teh app