php+mysql statement timing out  
Index ‹ php-general
Author Message
Shabam
PostPosted: 2004-3-2 0:54:36 Top
php-general, php+mysql statement timing out Ok I'm trying to run a php script written by someone else, not me, and it's
getting stuck in a particular step. Actually it isn't getting stuck per se,
but the browser is, because it's taking forever to return the results back
to the browser.

Here's the line that's responsible for this:

$users = $db->query_return_array("SELECT * FROM user");

It's getting stuck because in my database I have over 60,000 records. Now,
I'm just wanting to get over this step (it's an upgrade script), not looking
for fancy proper methods of php coding.

What alternative ways are there for me to prevent the browser from timing
out? I'm guessing some way of looping through the records, and updating the
client with simple update characters to prevent it from timing out.

Thanks in advance. :)




 
John Smith
PostPosted: 2004-3-2 1:05:00 Top
php-general >> php+mysql statement timing out You could ad limit=10000 to the statement
echo a space
and run the query again and again

But are you sure this is the problem???
How long will it take the SQL server to get the data
The problem is probably somewhere else.

The greatest query I have is only 10.000 records but with severel inner and
left joins and the data gets sorted, this doesn't take more than a second on
my server.
And 6 seconds isn't by far long enough for a browser to time-out


"Shabam" <email***@***.com> schreef in bericht
news:email***@***.com...
> Ok I'm trying to run a php script written by someone else, not me, and
it's
> getting stuck in a particular step. Actually it isn't getting stuck per
se,
> but the browser is, because it's taking forever to return the results back
> to the browser.
>
> Here's the line that's responsible for this:
>
> $users = $db->query_return_array("SELECT * FROM user");
>
> It's getting stuck because in my database I have over 60,000 records.
Now,
> I'm just wanting to get over this step (it's an upgrade script), not
looking
> for fancy proper methods of php coding.
>
> What alternative ways are there for me to prevent the browser from timing
> out? I'm guessing some way of looping through the records, and updating
the
> client with simple update characters to prevent it from timing out.
>
> Thanks in advance. :)
>
>
>
>


 
Shabam
PostPosted: 2004-3-2 1:24:00 Top
php-general >> php+mysql statement timing out "John Smith" <email***@***.com> wrote in message
news:c1vqgn$cpi$email***@***.com...
> You could ad limit=10000 to the statement
> echo a space
> and run the query again and again
>
> But are you sure this is the problem???
> How long will it take the SQL server to get the data
> The problem is probably somewhere else.
>
> The greatest query I have is only 10.000 records but with severel inner
and
> left joins and the data gets sorted, this doesn't take more than a second
on
> my server.
> And 6 seconds isn't by far long enough for a browser to time-out

I'm not an expert in all of this, but I'm sure it's getting stuck here:

$users = $db->query_return_array("SELECT * FROM user");
$totusers = $db->num_rows();

As for your proposed solution, how would I loop it 10,000 at a time, echo a
space, then requery to get the next 10,000, and then add it to the $users
array? It's vital the end array is the same, as latter code will need it.
Thanks.


 
 
John Smith
PostPosted: 2004-3-2 2:25:00 Top
php-general >> php+mysql statement timing out
"Shabam" <email***@***.com> schreef in bericht
news:email***@***.com...
> "John Smith" <email***@***.com> wrote in message
> news:c1vqgn$cpi$email***@***.com...
> > You could ad limit=10000 to the statement
> > echo a space
> > and run the query again and again
> >
> > But are you sure this is the problem???
> > How long will it take the SQL server to get the data
> > The problem is probably somewhere else.
> >
> > The greatest query I have is only 10.000 records but with severel inner
> and
> > left joins and the data gets sorted, this doesn't take more than a
second
> on
> > my server.
> > And 6 seconds isn't by far long enough for a browser to time-out
>
> I'm not an expert in all of this, but I'm sure it's getting stuck here:
>
> $users = $db->query_return_array("SELECT * FROM user");
> $totusers = $db->num_rows();
>
> As for your proposed solution, how would I loop it 10,000 at a time, echo
a
> space, then requery to get the next 10,000, and then add it to the $users
> array? It's vital the end array is the same, as latter code will need it.
> Thanks.
>
>
This might do the trick depending on the keys the array returns,
but I think this is some Unique Key from de DB so there should be no
problems with double keys.
I haven't tried it but I think it would work.

//Replace "$users = $db->query_return_array("SELECT * FROM user");" with
this code
echo "<!--";
$limit=100;
$users=array();
$temp_users = $db->query_return_array("SELECT * FROM user limit ".$limit);
while($count($temp_user)<$limit)
{
$users=array_merge($temp_users,$users)
$temp_users = $db->query_return_array("SELECT * FROM user limit ".$limit);
echo " ";
}
$users=array_merge($temp_users,$users)
echo "-->";



 
 
Shabam
PostPosted: 2004-3-2 7:16:00 Top
php-general >> php+mysql statement timing out > //Replace "$users = $db->query_return_array("SELECT * FROM user");" with
> this code
> echo "<!--";
> $limit=100;
> $users=array();
> $temp_users = $db->query_return_array("SELECT * FROM user limit ".$limit);
> while($count($temp_user)<$limit)
> {
> $users=array_merge($temp_users,$users)
> $temp_users = $db->query_return_array("SELECT * FROM user limit
".$limit);
> echo " ";
> }
> $users=array_merge($temp_users,$users)
> echo "-->";

It doesn't work, even though I cleaned up some of the code errors. The line
"while($count($temp_user)<$limit)" should be
"while(count($temp_users)<$limit)", and even then it returns false on the
very first try because the count = 100 right off the bat.

Help?


 
 
Doug Hutcheson
PostPosted: 2004-3-2 13:05:00 Top
php-general >> php+mysql statement timing out "Shabam" <email***@***.com> wrote in message
news:email***@***.com...
> > //Replace "$users = $db->query_return_array("SELECT * FROM user");" with
> > this code
> > echo "<!--";
> > $limit=100;
> > $users=array();
> > $temp_users = $db->query_return_array("SELECT * FROM user limit
".$limit);
> > while($count($temp_user)<$limit)
> > {
> > $users=array_merge($temp_users,$users)
> > $temp_users = $db->query_return_array("SELECT * FROM user limit
> ".$limit);
> > echo " ";
> > }
> > $users=array_merge($temp_users,$users)
> > echo "-->";
>
> It doesn't work, even though I cleaned up some of the code errors. The
line
> "while($count($temp_user)<$limit)" should be
> "while(count($temp_users)<$limit)", and even then it returns false on the
> very first try because the count = 100 right off the bat.
>
> Help?
>
>

I think you are trying to retrieve $limit records at a time from
your database and merge these into an array. Is that right?

If so, you really want to loop while the number of returned records
is equal to $limit. If the select fails, or returns fewer records,
you have either consumed all records or have hit an error.

I would recode it something like this (warning - aircode):
==========================================
echo "<!--";
$limit=100;
$users=array();
/*
* Set up a loop exit condition
*/
$not_done = true;

/*
* Loop until the condition is met
*/
while($not_done)
{

/*
* Retrieve a chunk of records and collapse gracefully on error
*/
$temp_users = $db->query_return_array("SELECT * FROM user limit
".$limit) or die ("Error retrieving records: ".dberrorblah..);

/*
* Perform the merge
*/
$users=array_merge($temp_users,$users)

/*
* Decide whether we have reached the end of the record set
*/
$not_done = $count($temp_user) == $limit ? true : false;

/*
* Extend our output string by one space
*/
echo " ";
}

echo "-->";
==========================================

Hope it helps.
Kind regards,
Doug


 
 
John Smith
PostPosted: 2004-3-2 15:23:00 Top
php-general >> php+mysql statement timing out
"Doug Hutcheson" <email***@***.com> schreef
in bericht news:RCU0c.148$email***@***.com...
> "Shabam" <email***@***.com> wrote in message
> news:email***@***.com...
> > > //Replace "$users = $db->query_return_array("SELECT * FROM user");"
with
> > > this code
> > > echo "<!--";
> > > $limit=100;
> > > $users=array();
> > > $temp_users = $db->query_return_array("SELECT * FROM user limit
> ".$limit);
> > > while($count($temp_user)<$limit)
> > > {
> > > $users=array_merge($temp_users,$users)
> > > $temp_users = $db->query_return_array("SELECT * FROM user limit
> > ".$limit);
> > > echo " ";
> > > }
> > > $users=array_merge($temp_users,$users)
> > > echo "-->";
> >
> > It doesn't work, even though I cleaned up some of the code errors. The
> line
> > "while($count($temp_user)<$limit)" should be
> > "while(count($temp_users)<$limit)", and even then it returns false on
the
> > very first try because the count = 100 right off the bat.
> >
> > Help?
> >
> >
>
> I think you are trying to retrieve $limit records at a time from
> your database and merge these into an array. Is that right?
>
> If so, you really want to loop while the number of returned records
> is equal to $limit. If the select fails, or returns fewer records,
> you have either consumed all records or have hit an error.
>
> I would recode it something like this (warning - aircode):
> ==========================================
> echo "<!--";
> $limit=100;
> $users=array();
> /*
> * Set up a loop exit condition
> */
> $not_done = true;
>
> /*
> * Loop until the condition is met
> */
> while($not_done)
> {
>
> /*
> * Retrieve a chunk of records and collapse gracefully on error
> */
> $temp_users = $db->query_return_array("SELECT * FROM user limit
> ".$limit) or die ("Error retrieving records: ".dberrorblah..);
>
> /*
> * Perform the merge
> */
> $users=array_merge($temp_users,$users)
>
> /*
> * Decide whether we have reached the end of the record set
> */
> $not_done = $count($temp_user) == $limit ? true : false;
>
> /*
> * Extend our output string by one space
> */
> echo " ";
> }
>
> echo "-->";
> ==========================================
>
> Hope it helps.
> Kind regards,
> Doug
>
> --
> Remove the blots from my address to reply
>
>
My mistake, I always take the first $limit and not the second $limit with
the second time,
I've changed 3 lines!!!

> > > //Replace "$users = $db->query_return_array("SELECT * FROM user");"
with
> > > this code
> > > echo "<!--";
> > > $limit=100;
$count=0;
> > > $users=array();
> > > $temp_users = $db->query_return_array("SELECT * FROM user limit
> ".$limit);
> > > while($count($temp_user)<$limit)
> > > {
> > > $users=array_merge($temp_users,$users)
> > >$temp_users = $db->query_return_array("SELECT * FROM user limit $count,
".$count+$limit);
$count+=$limit;
> > > echo " ";
> > > }
> > > $users=array_merge($temp_users,$users)
> > > echo "-->";
> >


 
 
 
Index ‹ php-general