Is this server intensive?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Covenant
Forum Newbie
Posts: 15
Joined: Sun Oct 02, 2005 3:30 pm

Is this server intensive?

Post by Covenant »

Is it bad on the server (slows things down) to have a mysql_query or while() statement within another while() statement?

Here's an example:

Code: Select all

$boards=mysql_query("SELECT * from cats");

 while($row=mysql_fetch_array($boards))
 {
 echo ("$row[title]");
 $boards2=mysql_query("SELECT * from boards where catid='$row[id]'");
 while($row2=mysql_fetch_array($boards2)
      {
 echo ("$row2[title]");
      }
 }
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

It is a bit server intensive, a join is quicker but do you think you can easily get information in the query result from the join displayed in the way you want...I think then you would be delegating work to the Web server instead of database server...
I think for just listing, it is better to use single select statements inside loops instead of joins. If anybody has got a better way to handle this result easily then I would be eager to hear from it you...

Code: Select all

select a.`title`, a.`id`, b.`title` from
cats a
left join `boards` b on b.`catid` = a.`id`
order by a.`title`
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

Post by duk »

i think what he is doing is faster then the join query... the join query will affect more the database server... and what he have with the select query and whiles, will use both server and database capacity... and will work faster... i didn't test....
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the OP's is very likely not faster. This is due to running n 1 queries where n is the number of records that would match.

There are two possible routes to make it faster, one is raghavan20's. The other is running two queries and making the connections yourself after the fact, or using a mild amount of preprocessing.

Code: Select all

$cats = mysql_query('SELECT * FROM `cats` ORDER BY `order`') or die(mysql_error());
$titles = array();
while($row = mysql_fetch_assoc($cats)) {
  $titles[$row['catid']] = $row['title'];
}
$boards = mysql_query('SELECT * FROM `boards` WHERE `catid` IN(\'' . implode('\',\'',$list) . '\') ORDER BY `catid`, `order`');
$subtitles = array();
$last = null;
while($row = mysql_fetch_assoc($boards)) {
  if($last !== $row['catid']) {
    $subtitles[$row['catid']] = array();
  }
  $subtitles[$row['catid']][$row['boardid']] = $row['title'];
  $last = $row['catid'];
}

foreach($titles as $catid => $title) {
  echo '<hr><b>'.$title.'</b><br />';
  if(isset($subtitles[$catid])) {
    foreach($subtitles[$catid] as $boardid => $title) {
      echo $title.'<br />';
    }
  }
}
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I had done a lot of the loading query results into arrays, and referencing them later on in the script to avoid using queries inside of loops.

However, on a few scripts I am loading 4 or 5 separate arrays which I think is using up more memory than it should. I think I should look into using joins.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

@scrotaye ~

or only do the queries when you need them, and instead of loading them into arrays, loop over the result as you need it and use mysql_free_result() as soon as possible in the script
Post Reply