Optimization of a script help

[cooldude832]

I have a search engine I threw together yesterday to query my mysql table and then show $limit results per page of matching results.

since this script is a few hundred lines and unrefined (and my work that I wish to remain private, not public) I will explain its flow.  It seems to be having some load issues and I wanted an opinion on making it faster other than buying a faster server

Here is the script order:

 

1) Take all the user's query and format them into a string called $critera; $query = "SELECT * FROM ".$table." ".$critera." ".$orderby;

 

2) $numresults=mysql_query ($query); //Return of the Number of Matching Results

    $row= mysql_fetch_array ($numresults); //Lets use a fetch array for this

 

3) From the results it forms a dual dimensional array the outer keys are the page numbers so page one is results 1 to result limit page to is from limit+1 to 2limit etc etc.  The inner part of those arrays is the matching result's ID numbers

 

4) Then it does this:

  foreach($page[$currentpage] as $value)//Stops at end of this limit

 

5) In that foreach loop it runs a mysql query:

  $query = mysql_query("SELECT * FROM $table WHERE ID = '$value'") or die(mysql_error());

$result =  mysql_fetch_array($query);

 

6) in the loop it gives out formatted results with echos if else and while functions to interpret the results.

 

For some reason everything loads fine until a point before the ads are created.  Any tips?

 

 

[trq]

Well, looping through results and running more queries within each loop is very inificient.

 

You might want to find yourself a decent tutorial on record paging with php and go from there.

[cooldude832]

its not running any more queries than needed unless you know of a way I can retrive all the data in a single query.  hmm actually now that I think about it I run an initial query for the IDs, maybe I can reuse that query's results to get the data I already have.  Any suggestions how to turn :

 

$query = "SELECT * FROM ".$table." ".$critera." ".$orderby; 


$numresults=mysql_query ($query);		//Return ofthe Number of Matching Results
$row= mysql_fetch_array ($numresults);	//Lets use a fetch array for this 
//Puts all the Keys Returned into a Nice Array
do
{
          $adid_array[] = $row['ID'];
    }
  while( $row= mysql_fetch_array($numresults));

into a third level to my two level array containing all my search results?

,but only for $page[$currentpage]  I might be able to do it if i first establish it and then re mysql_fetch_array($numresults) and use if($row['ID']  = $page[$currentpage][$i]) populate $page[$currentpage][$i][$fields];