Re: [PHP] More info on timeout problem, with code
Jon Westcot wrote: Hi Chris: ... That is, I need to close the single quote, place a comma after the field, and then add in another opening quote. One other thing: I suspect I need to use addslashes() to the elements in $data -- is there a way to do this with one statement as you've done above? something like: INSERT INTO foo (fields) VALUES ('.join(',', array_map('mysql_real_escape_string', $data).'); Also as someone else suggested if this is a csv file you can use LOAD DATA INFILE to directly import it instead of having to create a bunch of insert queries. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html Only do this if you are 100% sure of the data (ie it it sanitized in some other step). Trying to use LOAD DATA INFILE was my initial plan, but that simply didn't work. Turns out that it's because of being on a shared server. I've talked with The Powers That Be about this and they're going to move to a dedicated virtual server; that should give me much more flexibility and control over what I need to update in the various settings and files and things to get past all of the heartburn I've had over the last weeks. As odd as this sounds, should I put in some type of delay? Could the system be thinking it's getting flooded by all of the inserts? Doubt it. Surprisingly enough, when I've used something like usleep(15000), I seem to be able to process more records than when I don't have it at all. ::shrug:: Just clutching at straws. Thanks again for your comments. I appreciate them all! Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] More info on timeout problem, with code
On 11/5/07, Jon Westcot [EMAIL PROTECTED] wrote: Hi all: As requested, here's the code: ?php if(isset($_POST['process'])){ $old_session_gc_maxlifetime = ; $old_max_execution_time = ; $old_max_input_time = ; $old_session_gc_maxlifetime = ini_set(session.gc_maxlifetime,1800); $old_max_execution_time = ini_set(max_execution_time,1800); // 30 minutes $old_max_input_time = ini_set(max_input_time,1800); // 30 minutes -- doesn't work echo pSession.gc_maxlifetime: . ini_get(session.gc_maxlifetime) . /p\n; // shows 1800 echo pMax execution time: . ini_get(max_execution_time) . /p\n; // shows 1800 echo pMax input time: . ini_get(max_input_time) . /p\n; // shows -1 ignore_user_abort(TRUE); set_time_limit(0); $query = mysql_query(TRUNCATE evall;); ^ From The Manual... ** *mysql_query* A SQL query The query string should ___not___ end with a semicolon. echo pResults of Truncate: $query/p\n; $myfile_replace = uploads/evall.csv; $handle = @fopen($myfile_replace, 'rb'); $save_handle = @fopen(tmp/sql_calls.txt, wb); $process_count = 0; if(!$handle) { echo pThe file ($myfile_replace) could not be opened.br //p\n; flush(); } else { echo pThe file ($myfile_replace) opened correctly.br //p\n; flush(); $headings = fgetcsv($handle, 1, ,); // Just ignore the first row returned. $row = 0; while (($data = fgetcsv($handle, 1, ,)) !== FALSE) { $row++; $num = count($data); $insert_query = INSERT INTO evall VALUES(; for ($c=0; $c $num; $c++) { if($c 0) { $insert_query .= ,; } $insert_query .= '' . $data[$c] . ''; } $insert_query .= );; if(fwrite($save_handle, $row . ; . strlen($insert_query) . : . \n) === FALSE) { echo pThe query could not be written./p\n; } else { $process_count++; } if($row % 1000 == 0) { echo $row records processed so farbr /\n; flush(); } } echo pFile import completed. $row records read; $process_count records added./p\n; flush(); fclose($save_handle); fclose($handle); } ini_set(session.gc_maxlifetime,$old_session_gc_maxlifetime); ini_set(max_execution_time,$old_max_execution_time); ini_set(max_input_time,$old_max_input_time); } ? form name=form enctype=multipart/form-data action=?php echo $_SERVER['PHP_SELF']; ? method=POST pVersion 1.9 -- The file uploading process presupposes that the user has uploaded the EVAll.CSV file to the quot;uploadsquot; folder. If this has been done and you're ready to process it, click the lt;Processgt; button./p input type=submit name=process value=Process /br/br/ /form hth, David
Re: [PHP] More info on timeout problem, with code
Hi David, et al.: Thanks for the comment. I removed the trailing semi-colon in the two areas where it was being sent to mysql_query() and tried the code again. I'm still getting the same basic problem -- it silently aborts somewhere around 22,000 to 26,000 records being processed out of just under 30,000. When I don't build the $insert_query string, I am able to read through the CSV file completely. As odd as this sounds, should I put in some type of delay? Could the system be thinking it's getting flooded by all of the inserts? Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] More info on timeout problem, with code
Thanks for the comment. I removed the trailing semi-colon in the two areas where it was being sent to mysql_query() and tried the code again. I'm still getting the same basic problem -- it silently aborts somewhere around 22,000 to 26,000 records being processed out of just under 30,000. When I don't build the $insert_query string, I am able to read through the CSV file completely. As odd as this sounds, should I put in some type of delay? Could the system be thinking it's getting flooded by all of the inserts? I didn't know you were on a shared server. I think your first problem is getting your 50+ MB file transfered/uploaded. Maybe it can be FTP'd from authenticated users, then a cronjob can run to check the directory for unprocessed uploaded files, then parse it to create the TRUSTED SQL statement and maybe even use LOAD DATA INFILE. _ Peek-a-boo FREE Tricks Treats for You! http://www.reallivemoms.com?ocid=TXT_TAGHMloc=us -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] More info on timeout problem, with code
Jon, I can provide you with access to a Linux web box to test your code and database stuff if you'd like. No charge or anything, it's just that I strongly believe the problems are caused by the limits you face with your current web host. If you want to give it a shot, let me know and I'll set it up for you. -- Daniel P. Brown [office] (570-) 587-7080 Ext. 272 [mobile] (570-) 766-8107 If at first you don't succeed, stick to what you know best so that you can make enough money to pay someone else to do it for you. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] More info on timeout problem, with code
Jon Westcot wrote: Hi David, et al.: Thanks for the comment. I removed the trailing semi-colon in the two areas where it was being sent to mysql_query() and tried the code again. I'm still getting the same basic problem -- it silently aborts somewhere around 22,000 to 26,000 records being processed out of just under 30,000. When I don't build the $insert_query string, I am able to read through the CSV file completely. What indexes are on this table? When you do an insert, each one has to update the index as well as the data, so maybe that's where all the time is being spent in the database (doubt it's the problem but try dropping all of the indexes on the table). Are you commenting out this whole section? $insert_query = INSERT INTO evall VALUES(; for ($c=0; $c $num; $c++) { if($c 0) { $insert_query .= ,; } $insert_query .= '' . $data[$c] . ''; } $insert_query .= );; Try $insert_query = INSERT INTO evall values (' . implode('\'', $data) . '); so you're not doing a for loop when you don't need to. Also as someone else suggested if this is a csv file you can use LOAD DATA INFILE to directly import it instead of having to create a bunch of insert queries. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html Only do this if you are 100% sure of the data (ie it it sanitized in some other step). As odd as this sounds, should I put in some type of delay? Could the system be thinking it's getting flooded by all of the inserts? Doubt it. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] More info on timeout problem, with code
Hi Chris: What indexes are on this table? On the import table, there is only one index. And I probably don't even need an index on it since it will be processed sequentially into other tables after it's been imported. When you do an insert, each one has to update the index as well as the data, so maybe that's where all the time is being spent in the database (doubt it's the problem but try dropping all of the indexes on the table). I will try this. Are you commenting out this whole section? $insert_query = INSERT INTO evall VALUES(; for ($c=0; $c $num; $c++) { if($c 0) { $insert_query .= ,; } $insert_query .= '' . $data[$c] . ''; } $insert_query .= );; Only for the point of testing; normally, that code would need to be included to generate the INSERT query. Try $insert_query = INSERT INTO evall values (' . implode('\'', $data) . '); so you're not doing a for loop when you don't need to. Thanks for the suggestion. But, won't the glue part actually need to be something like: '\',\'' That is, I need to close the single quote, place a comma after the field, and then add in another opening quote. One other thing: I suspect I need to use addslashes() to the elements in $data -- is there a way to do this with one statement as you've done above? Also as someone else suggested if this is a csv file you can use LOAD DATA INFILE to directly import it instead of having to create a bunch of insert queries. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html Only do this if you are 100% sure of the data (ie it it sanitized in some other step). Trying to use LOAD DATA INFILE was my initial plan, but that simply didn't work. Turns out that it's because of being on a shared server. I've talked with The Powers That Be about this and they're going to move to a dedicated virtual server; that should give me much more flexibility and control over what I need to update in the various settings and files and things to get past all of the heartburn I've had over the last weeks. As odd as this sounds, should I put in some type of delay? Could the system be thinking it's getting flooded by all of the inserts? Doubt it. Surprisingly enough, when I've used something like usleep(15000), I seem to be able to process more records than when I don't have it at all. ::shrug:: Just clutching at straws. Thanks again for your comments. I appreciate them all! Jon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php