Page 1 of 1

Optimising Code for Database Queries

Posted: Thu Aug 11, 2005 9:35 am
by Grim...
Currently, when I'm querying a DB with PHP I get my results like this:

Code: Select all

<?php
$result=mysql_query("SELECT * FROM table WHERE thisfield = '$thatvariable'");
while ( $a_row = mysql_fetch_object( $result ) )
{
    //do whatever
}
?>
However, if I know that only one result will come back, is there a more efficient way of doing things?

Posted: Thu Aug 11, 2005 9:42 am
by nielsene
Well its not more efficient from a program execution standpoint, but I'ld probably do something like

Code: Select all

list($a, $b, $c) = mysql_fetch_row(mysql_query($query));
If I was positive it would only return one row and I'm willing to lose the
possibility of error checking between the two calls.

Most of the time, I'ld probably do it more like

Code: Select all

$res=mysql_query($query) or handle_error();
list($a, $b, $c) = mysql_fetch_row($res);
If I knew it was sure to return a single row -- ie simple query on a known to exist primary/unique key.

Posted: Thu Aug 11, 2005 9:42 am
by feyd
not really...

Posted: Thu Aug 11, 2005 9:43 am
by Grim...
I guess what I'm really asking is: There's nothing really wrong with the way I currently do things, right?

Posted: Thu Aug 11, 2005 9:46 am
by feyd
nope.

Posted: Thu Aug 11, 2005 9:54 am
by nielsene
Yeah, there's nothing wrong with it, but I don't think its as "clear" from a "what the code means" perspective to code a loop when you expect a single row. In fact, I'd often code it like (slight psuedo-code/OOP)

Code: Select all

$res = $db->query($query);
switch($res->numrows()) {
  case 0 : handle_not_found(); break;
  case 1: handle_normal(); break;
  default: handle_multiple_found_error(); break;
}
//or
$res = $db->query($query);
if ($res->numrows()!=1) handle_error();
else {
// normal processing
}
If only a single row should be returned, you should make that explicit. If zero or many rows are returned then something is wrong and you should deal with it, not complacently loop.