php mysql loop query timing out

hey guys, im currently working on a script to query a database for multiple rows and get data from them. the values that im searching for in the db are stored in a comma seperated string, which i then use explode() to seperate into an array. so here is how it starts

i recieve a string in a url as follows

items=item1,item2,item2,item3,item4,item5,6tem7,item8 ... there can be dozens of items though.

i then use explode, to put those in an array.

i then loop through the array and do a query for each item and get the data that i need and output, then the loop starts again, and so on... the database has about 33,000 rows and about 90 columns. i know this is a terrible way to query, as its timing out looking for more then 10 or 12 items. im sure its because all the queries, but im having programmers block, so i can think of a work around, which im sure there is an obvious one. Thanks guys.

Matthew

 

i don't think the timeout is due to what you have mentioned... 33k rows isn't a lot... and the explode function hardly takes any effort...

how many rows does the query return normally? and are you selecting only the columns you require in your query? don't do a "select *"...

also, can try "where x like y" instead of "where x = y" ?

i'm shooting in the dark... if you can provide more details... e.g. you actual query, i may be able to provide more suggestions...

 

Does this help at all? its a cutdown version of the code...
disenSize=length of array of items to search

...................................................................

for($z=0; $z<$disenSize; $z++){
if(isset($disenArray[$z])){
if($cbg=="#FFFFFF"){
$cbg="#EEEEEE";
}
elseif($cbg=="#EEEEEE"){$cbg="#FFFFFF"; }


connectDB("****","****","****");

$query2="SELECT * FROM items WHERE name='$disenArray[$z]'";
$result2=mysql_query($query2);
$num2=mysql_numrows($result2); if($num2!=0){$num2=1;}
mysql_close();

for($n=0; $n<$num2; $n++){

...grabs 75 values from row.
...check if those 75 values empty

...32 lines of code to change some of the results


...141 lines of code to check results and build a string out of them. includes about 70 if statements.



...14 lines of echo code.

}
}
$z++;
}

 

anyone have any idea on how to make this run better?

 

Post the results of this query.

"show create table items" (Change the items table name if your's is different.)

My guess is you are table walking.

 

Can you post CREATE TABLE statement for tables you're using and tell us exactly what you need to do?

 

hey, thanks for the reply. im at work so ill post it when i get home this afternoon. thanks again.

 

While there are many folks here that can help, if ya wanna PM me later to say you posted, go for it. I'll try my best to help even if I fail.

 

1. You make a new db connect on every iteration. Why are you doing this?
   Take the db connection/disconnection code out of loop block.
2. It seems your script takes too much time to execute.
   Try to change the max script execution time by using a function set_time_limit at the beginning.

 

Good catch! That does have to change.

I knew there were smarter people then me here.

 

hey, i tried moving the open and close database outside the loop but it didnt speed it up. i added the set_time_out, it does allow the script to finish, it takes about 1 minute to display 12 results its sooo slow.

ps

whats table walking mean?

 

Table walking means rather then doing and indexed lookup and getting right to records, the query reads each and every record in the table for each query.

This will occur when you use queries that do lookups and where's on non indexed rows. That's why we asked for the display of the table structure.

 

ic, how do you use the 'show create table items' thing in php?

also, i just noticed that moving the connect outside the loop did speed it up a bit, actually about 25% faster.

 

$result = mysql_query("show create table items");

$tableinfo =  mysql_fetch_array($result);

echo '<pre>';
print_r($tableinfo[1]);

PHP: