Which is more efficient?

[Omzy]

$query="SELECT * FROM customers ORDER BY id DESC LIMIT 5";
$results = mysql_query($query);

while($row=mysql_fetch_assoc($results))
{
  $id=$row['id'];
  $name=$row['name'];
  $description=$row['description'];

//echo the data
}

 

OR

 

$query="SELECT id, name, description FROM customers ORDER BY id DESC LIMIT 5";
$results = mysql_query($query);

while(list($id,$name,$description)=mysql_fetch_row($results))
{
//echo the data
}

 

 

Also with regards to method 1, is it advisable to specify the field names in the query rather than selecting all fields?

[Andy-H]

I think the first one would be faster if you specified the required field names rather than selecting all of the fields in each row.

 

Although the difference would be insignificant I think.

I take it you already know that mysql_fetch_array adds the data to both a numerically indexed array and an associative array and is therefor slower without the 2nd parameter passed.

[ignace]

Also with regards to method 1, is it advisable to specify the field names in the query rather than selecting all fields?

 

IMO you should always specify the field names if you are not going to use all columns returned. Maybe it is even better to always specify all the field names even if you are using all columns. This helps your teammates and yourself to understand what is in the result set and limits the result set if you'd ever add columns.

[ignace]

I take it you already know that mysql_fetch_array adds the data to both a numerically indexed array and an associative array and is therefor slower without the 2nd parameter passed.

 

If he didn't knew it he would know now but he ain't using mysql_fetch_array

[Andy-H]

I take it you already know that mysql_fetch_array adds the data to both a numerically indexed array and an associative array and is therefor slower without the 2nd parameter passed.

 

If he didn't knew it he would know now but he ain't using mysql_fetch_array

 

Hence "I take it you already know" However if he didn't he will now, just thought I'd add that he was doing the right thing with that in case it wasn't his normal practice.

[Omzy]

Lol yeah I just came across that last night, I had previously been doing it with mysql_fetch_array($results, MYSQL_ASSOC) but I've now changed all my code to mysql_fetch_assoc.

 

Anyway I assumed the second method would be faster as all the variables are being assigned within the while statement?

[akitchin]

Lol yeah I just came across that last night, I had previously been doing it with mysql_fetch_array($results, MYSQL_ASSOC) but I've now changed all my code to mysql_fetch_assoc.

 

Anyway I assumed the second method would be faster as all the variables are being assigned within the while statement?

 

as far as i know, it wouldn't really matter - PHP still has to extract the variables from the array returned by mysql_fetch_assoc() or mysql_fetch_row(). the only difference is the assignment of an intermediate variable. you could always run a benchmark.

 

one question i've always had is why bother extracting the variables in the first place? why not just use them directly from the array returned by the fetching function?

[Andy-H]

Not sure about that one tbh, it uses an extra function but less code lines. Obviously echoing them out will be more efficient as you are using less variables and therefor less server RAM.

 

If you find yourself doing that kind of thing alot I think you might find extract useful with mysql_fetch_assoc().

 

It basically pulls the array values into variables with the relevant key name to that value.

Example:

$query="SELECT id, name, description FROM customers ORDER BY id DESC LIMIT 5";
$results = mysql_query($query);

   while ($row = mysql_fetch_assoc($results))
   {
      extract($row, EXTR_PREFIX_ALL, 'c');
      echo '<pre>' . "\n\r";
        echo "ID:\t\t" . $c_id . "\n\r";
        echo "Name:\t\t" . $c_name . "\n\r";
        echo "Description:\t" . $c_description . "\n\r";
      echo '</pre>';
   }

[ignace]

one question i've always had is why bother extracting the variables in the first place?

 

I sometimes use a combination of list and mysql_fetch_array with the MYSQL_NUM as a second parameter for convenience reasons.