Optimising Code for Database Queries

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
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Optimising Code for Database Queries

Post 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?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

not really...
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

I guess what I'm really asking is: There's nothing really wrong with the way I currently do things, right?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

nope.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
Post Reply