what method would use more resources?

[sde]

i think i know the answer ( method 2 ), but which of these methods would be less taxing on the server:

method 1

PHP Code:

<?
$result=mysql_query("select id from contacts where field='$field'");
while($row=mysql_fetch_row($result))
{  
  $id_array[]=$row[0];
}

foreach($id_array as $each)
{ 
  $result=mysql_query("select * from contacts where id='$each'");
  while(row=mysql_fetch_row($result)
  {
    print $row['f_name'] . " " . $row['l_name'] . "<br>\n\n" . 
$row['addr_1'] . "<br>\n" . $row['addr_2'] . "<br>\n" . 
$row['city'] . " " . $row['state'] . " " . $row['zip'] . "<br><br>\n\n";
  }
}
?>

method2

PHP Code:

<?
$result=mysql_query("select id from contacts where field='$field'");

$i=0;
while($row=mysql_fetch_row($result))
{
  $id[$i]=$row['id'];
  $f_name[$i]=$row['f_name'];
  $l_name[$i]=$row['l_name'];
  $addr_1[$i]=$row['addr_1'];
  $addr_2[$i]=$row['addr_2'];
  $city[$i]=$row['city'];
  $state[$i]=$row['state'];
  $zip[$i]=$row['zip'];
}

foreach($id as $each)
{
  print $f_name . " " . $l_name . "<br>\n\n" . $addr_1 . "<br>\n" . 
$addr_2 . "<br>\n" . $city . " " . $state . " " . $zip . "<br><br>\n\n";
}
?>

i know i could print the results of method 2 in the original loop, however in the project i'm working on , it is in a class function and its purpose is to set object variables.

basically i'm wondering of it is better to put everything in an array even if the data within the array is going to be a lot.. or just run a bunch of sql queries.

which method is optimal?

[joe_bruin]

which server are you trying to tax less, the database server, or the web servers(s)? or are they the same machine?

it is usually a better idea to minimize the number sql queries. these may have a very high latency if your database is under high load. in a traditional web setup, you have several front facing web machines all accessing one database. the database very often becomes the bottleneck, whereas the webservers usually have ram and cpu time to spare. a query that returns all the data at once only requires one-time processing by the db engine, whereas a thousand small queries that return the data individually require the db to do the same amount of work for each one. it is rarely the case where doing the multiple queries is a good idea.

if the amount of data you're selecting is really large, though (tens of thousands of rows or more), you may want to look at more efficient ways of handling this query, or partitioning the result set. otherwise your webserver will be spending alot of needless time copying memory around, and may even exceed the script memory limit (or timeout).

oh, your method 2 sql statement should probably be "select *"

[sde]

thanks joe,

yes, both server and db are on the same machine. i did go with the 1 query method.

my examples were purely for the question and you're right, it should be select * =)

[pngwyn]

Method #2 is certainly *much* quicker and less taxing.

I will assume that $i is suppose to be incrementing. You may find the comments in the code below helpful.

PHP Code:

<?
// I assumed "Select *"
// Additionally, it would be quicker to ask for just the fields you 
// need in the order you need them in.

$result=mysql_query("SELECT * FROM contacts WHERE field='$field'")

$i=0;

while($row=mysql_fetch_array($result)) {
// It isn't necessary to define each variable.  Simply write the
// result set to your multidimensional array.

// Additionally I assume you are using mysql_fetch_array rather 
// than fetch_row by the way you are referring to the vars.
  $multi[$i][$row];
  $i++; 
}

// then a quicker loop would be:
for($ii = 0; $ii >= $i; $ii++) {
   // Additionally you could nest a loop here.
   print("$multi[$ii][fieldName]");
   print("$multi[$ii][0]");
}

// to get a good look at your result set use the print_r command
print_r($multi);
?>