Complex SQL query... names of returned variables

Question

Excuse me for what I'm sure is an elementary question for most of you, but I have an issue with table columns from separate tables having the same name as one another, and trying to select from both tables in the same query.

Okay, so this is my code:

$q_value = $mdb2->quote($_POST['query']);
$field = $_POST['field'];
$sql = "SELECT m.*, l.name FROM memberlist m, mail_lists l
        WHERE m.$field=$q_value
        AND l.id = m.list
        ORDER BY m.id";
$l_list = $mdb2->queryAll($sql, '', 'MDB2_FETCHMODE_ASSOC');

The table memberlist has the following columns: id, email, list, sex, name

and the table mail_lists has the following columns: id, name

After running the query, I later loop through the results with a foreach like so:

foreach ($l_list as $l){ //blahblah }

The problem is that the column 'name' in mail_lists refers to the names of the list, while the column 'name' in memberlist refers to the name of the member.

When I later access $l->name (within the foreach), will I get m.name, or l.name? Furthermore, how do I get access to the other?

Or will I just have to do two separate queries?

Answer 1

Why can you not simply use:

SELECT m.*, l.name as l_name FROM ...

and then distinguish between name and l_name?

---

And this is a matter of style, so others may disagree but I never use * in queries, tending to prefer explicit column specifications. That's not actually a problem in your case other than if you wanted to do:

SELECT m.*, l.* FROM ...

and still distinguish between the two names. With explicit column specifications, you can add an as clause to each column to give then unique names.