Prev: PHP & Active Directory?
Next: Warning messages on web page.
From: Ashley Sheridan on 21 Jun 2010 14:31
Hi All,

This is just a bit of a 'throw it out to the masses' sort of question to
see what people might recommend.

At the moment, if I am retrieving a single record from the DB, my code
looks like this:

$query = "SELECT * FROM table WHERE id=1";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
return $row;
}

Now, aside from the actual SQL involved, is this efficient? Would it
gain me anything if I used mysql_result() statements instead, or would
that only be efficient if there were a small number of fields I was
retrieving data for? Should I use something else entirely?

I've not got to the testing this myself, as I figured something like
this might be common knowledge for all of you who are better than me at
optimisation.

Is it even an issue if I'm only retrieving a single record in this
manner (I always make my id field in a table the primary key, so it
means I won't ever be retrieving more than one record)

Thanks in advance for any input you guys have!

Thanks,
Ash
http://www.ashleysheridan.co.uk


From: "larry on 21 Jun 2010 14:36
Performance-wise, SELECT * is slower than specifying fields
(marginally). If you just want a single field then mysql_result() will
be faster, but if you want multiple fields mysql_fetch_* is your best bet.

As far as the PHP goes, if you know there will be only a single record
I'd suggest using:

if ($row = mysql_fetch_array($result)) {
// Do stuff
}

As then you get an automatic "not found" else condition you can use.
That should be the same performance as the while, but get you the extra
information.

That said, you really shouldn't be using ext/mysql anymore. Use either
ext/mysqli or PDO. Better APIs, more secure, faster, and actually
maintained.

--Larry Garfield

On 6/21/10 1:31 PM, Ashley Sheridan wrote:
> Hi All,
>
> This is just a bit of a 'throw it out to the masses' sort of question to
> see what people might recommend.
>
> At the moment, if I am retrieving a single record from the DB, my code
> looks like this:
>
> $query = "SELECT * FROM table WHERE id=1";
> $result = mysql_query($query);
> while($row = mysql_fetch_array($result))
> {
> return $row;
> }
>
> Now, aside from the actual SQL involved, is this efficient? Would it
> gain me anything if I used mysql_result() statements instead, or would
> that only be efficient if there were a small number of fields I was
> retrieving data for? Should I use something else entirely?
>
> I've not got to the testing this myself, as I figured something like
> this might be common knowledge for all of you who are better than me at
> optimisation.
>
> Is it even an issue if I'm only retrieving a single record in this
> manner (I always make my id field in a table the primary key, so it
> means I won't ever be retrieving more than one record)
>
> Thanks in advance for any input you guys have!
>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
>
 | 
Pages: 1
Prev: PHP & Active Directory?
Next: Warning messages on web page.