By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,472 Members | 1,199 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,472 IT Pros & Developers. It's quick & easy.

am I doing unnecessary operations by mysql_fetch_array?

P: n/a

table `user`
---------------------------------------
| id | user_name | gender |
---------------------------------------

I can gurantee that ids in table user are unique (so each number
correspond at most one row in the table). I have two questions
1) can I somehow do away with mysql_fetch_array($result);?
2) If i use $sql_query = "SELECT user_name from `user` WHERE (id =
'$id')"; to replace line (1), do I get some better performance
theoretically? if so, can I simply return $row; ---because
'user_name' is the only field being selected.

$sql_query = "SELECT * from `user` WHERE (id = '$id')"; // (1)
$result = mysql_query($sql_query);
$row = mysql_fetch_array($result);
return $row['user_name'];

Thanks,

Aug 2 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 02.08.2007 07:51 newbie wrote:
table `user`
---------------------------------------
| id | user_name | gender |
---------------------------------------

I can gurantee that ids in table user are unique (so each number
correspond at most one row in the table). I have two questions
1) can I somehow do away with mysql_fetch_array($result);?
Yes, if you need one single field, you can use mysql_fetch_field. The
performance benefit would be negligible though.
2) If i use $sql_query = "SELECT user_name from `user` WHERE (id =
'$id')"; to replace line (1), do I get some better performance
Yes, of course. Some say '*' is harmful, always list the fields you want.
theoretically? if so, can I simply return $row; ---because
'user_name' is the only field being selected.

$sql_query = "SELECT * from `user` WHERE (id = '$id')"; // (1)
$result = mysql_query($sql_query);
$row = mysql_fetch_array($result);
return $row['user_name'];

Thanks,


--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi
Aug 2 '07 #2

P: n/a
..oO(newbie)
>table `user`
---------------------------------------
| id | user_name | gender |
---------------------------------------

I can gurantee that ids in table user are unique (so each number
correspond at most one row in the table). I have two questions
1) can I somehow do away with mysql_fetch_array($result);?
2) If i use $sql_query = "SELECT user_name from `user` WHERE (id =
'$id')";
Don't quote $id, it's a numeric value.
>to replace line (1), do I get some better performance
theoretically?
You need both, a mysql_query() call to query the database and a
mysql_fetch_*() call to get the results. But you could use the second
paramater of mysql_fetch_array() to specify whether it should return the
result as a numeric or associative array. By default it does both.
>if so, can I simply return $row; ---because
'user_name' is the only field being selected.

$sql_query = "SELECT * from `user` WHERE (id = '$id')"; // (1)
You shouldn't use "SELECT *" in production code, list all columns
explicitly.

Micha
Aug 2 '07 #3

P: n/a
C.
On 2 Aug, 10:04, Michael Fesser <neti...@gmx.dewrote:
if so, can I simply return $row; ---because
'user_name' is the only field being selected.
$sql_query = "SELECT * from `user` WHERE (id = '$id')"; // (1)

You shouldn't use "SELECT *" in production code, list all columns
explicitly.

Micha
You shouldn't use "SELECT *" in production code IF YOU CAN list all
columns
explicitly.

....reason being that you're loading more data into the mysql buffer
than you need, then it gets copied into the PHP variable space.
(1), do I get some better performance theoretically?
Unless someone shoves a LOB column type and populates it, you're
unlikely to see much of a performance difference though.

C.

Aug 2 '07 #4

P: n/a
On 3 Aug, 09:37, David Gillen <Bel...@RedBrick.DCU.IEwrote:
C. said:On 2 Aug, 10:04, Michael Fesser <neti...@gmx.dewrote:
You shouldn't use "SELECT *" in production code, list all columns
explicitly.
Micha
You shouldn't use "SELECT *" in production code IF YOU CAN list all
columns
explicitly.
...reason being that you're loading more data into the mysql buffer
than you need, then it gets copied into the PHP variable space.

If I want all column in a table though surely SELECT * is better.
In fact, performance on SELECT * is better than "SELECT field1, field2, field3,
field4, field5, field6, field7, field8" because mysql doesn't have to go
looking for the specific fields you've requested but just returns you
everything and since you know you want everything there is no problem.

D.
--
Fermat was right.
As well as what Micha said, there is another potential drawback.

Suppose someone added a TEXT BLOB field containing megabytes of data
to the table.
Any queries that used * would nowbe bringing back all that data each
time.

Aug 3 '07 #5

P: n/a
David Gillen wrote:
Michael Fesser said:
>Explicitly listing all columns in the SELECT statement works
always.
So does SELECT * if you code for it.

$sql = "SELECT * FROM table1";
$query = mysql_query($sql);
while($row = mysql_fetch_assoc($query))
{
echo "Always field 1: " . $row['field1'];
}

So now, where is the problem?

D.
As I said earlier, the problem is when a TEXT BLOB or any other large field
is added to the database in future and then all * queries are pulling back
loads of data that is not required.

Argue all you like, but it is bad practice. I have come across lots of bad
programming which was fine for the original intention, but caused many
problems when the applications changed in later years. Had good practice
been followed there would have been no problem.

So you go and ignore all the good advice you want to and program as badly as
you wish. It's a fair bet that I'll never have to maintain your crap.
Aug 4 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.