What way is best on resources

New Member Joined: Nov 12, 2003 Posts: 2

I was thinking on how to keep queries down on some loops. This has nothing to do with anything except showing you an example to coment on.

Say if you wanted to show the last 50 games played and you have the last played table with game_id in it and all the game info in another table.

So one way to do it is:

Code:




$sql = $db->sql_query("SELECT * FROM nuke_games_last ORDER BY date_played DESC LIMIT 50");





while ( row = $db->sql_fetchrow($sql) )


{


   $sql = $db->sql_query("SELECT * FROM nuke_games WHERE game_id = $row[game_id]");





   echo $game_name . " " . $game_disc . "<br />";


}

That would cause 51 queries, the inital query and then 1 per game. But if you do it like this:

Code:




$sql = $db->sql_query("SELECT * FROM nuke_games_last ORDER BY date_played DESC LIMIT 50");


$last_games = $db->sql_fetchrow($sql);





$sql = $db->sql_query("SELECT * FROM nuke_games ORDER BY game_id");


$game_info = $db->sql_fetchrowset($sql);





for($i  = 0; $i < count($last_games); $i++)


{


    unset($this_name, $this_disc);





    for ($j = 0; $j < count(game_info); $j++)


    {


       if ($last_game[$i]["game_id"] == $game_info[$j]["game_id"])


   {


      $this_name = $game_info[$j]["game_name"];


      $this_disc = $game_info[$j]["game_disc"];


      break;


   }


   


   echo $this_name . " " . $this_disc . "<br />";


    }


}

Basicly all the info for the games is stored in an array and it has to loop through the array for each of the 50 games, this only casues 2 queries, but performance wise what would be beter, especially if the array was say the user table which can have sometimes 1,000's of entries to search through.

Would just like some opinons on it thanks.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Actually, unfortunately, this is not the case. If you look at the code for sql_fetchrowset you will see

Code:

         while($this->rowset[$query_id] = @mysql_fetch_array($query_id))


         {


            $result[] = $this->rowset[$query_id];


         }

As you can see, individual calls are still made in a 'while' loop as this is the only way to retrieve rows from a MySQL table. So, you have saved no queries at all.

Posted: Tue Sep 21, 2004 10:02 am

Thanks for the reply Raven.

If that is the case how come on the query count on the bottom of the page when this was setup on a real table decreased by 50 when I changed it to this.

Just curiouse, would the SQL Abstration Layers Queries not be counted for some reason.

Posted: Tue Sep 21, 2004 11:14 am

My guess is that whatever method you are using to count the queries is not accounting for the fetchrowset subroutine and is only using the fetchrow subroutine.