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
