mysql_fetch_assoc vs iterative mysql_fetch_row & mysql_fetch_column

Question

I am working on an active record class that is used to fetch records from a database which can contain joins.

Now, in my tests, I have had some data that has common column names that contain different values, but when I perform a mysql_fetch_assoc function, it will only return one column of the common name, and can loose the remaining data. So, I am planning on replacing the mysql_fetch_assoc with two loops, the first to get the column names (using mysql_fetch_column), and the second to get the data (by having two loops, one nested in the other, that will fetch all the data, then put it together into an array that is returned) which would contain the column name and its parent table reference (or if the JOIN has the AS syntax).

But I am getting concerned that this might not be efficient, and might cost quite a lot of processing time, so could someone clarify the efficiency of both methods, or if there is a function like mysql_fetch_assoc that would prepend the table reference of the column?

Additional

Although this is using the mysql library for PHP, I am considering extending the library in the future to by PDO driven, but I would be using a similar method to above (by using getColumnMeta)

having multiple columns in query with the same name is always going to give you problems. The short answer is don't do it. SQL has a perfectly good aliasing feature that allows you to rename columns in the output recordset to exactly this kind of clash. eg `SELECT field AS alias`. Hacking at the problem in the way you've described may solve the problem, but will be slow and doesn't get around the fact that the query should have been written better in the first place. — Spudley, Apr 30 '13 at 13:56

score 2 · Accepted Answer · answered Apr 30 '13 at 13:52

2

First off: Standard "mysql_ is being removed, use mysqli_ instead" warning.

Second: hard to say without seeing your current code, but as far as raw sql goes, you can rename columns rather simply. So if you have table users and table contacts, you can do this:

select a.id, a.firstname as myfirstname, a.lastname as mylastname, 
    b.id, b.firstname, b.lastname 
from users a, contacts b 
where a.id=b.userid;

answered Apr 30 '13 at 13:52

Wolfman Joe

799
1
8
23

1

Re use of the `mysql_xx` functions: He already stated in the question that he's planning to switch to PDO in the future, so he's clearly aware of this issue. Nevertheless, I've given you +1 for providing the correct answer, rather than the one he actually asked for. – Spudley Apr 30 '13 at 13:57

score 2 · Answer 2 · answered Apr 30 '13 at 13:53

A common solution to the real problem (not XY one you asked on) is giving aliases to "common" field names:

SELECT goods.id as gid, categories id as cid ...

and so on

However, it scarcely would be a problem if you use your second approach with mysql_fetch_field() (which you actually mean) and mysql_fetch_row()

mysql_fetch_assoc vs iterative mysql_fetch_row & mysql_fetch_column

2 Answers2