Mysql query with multiple ID columns and php

Question

Hi guys.

I have 2 joined tables, each one has a primary key column named id.

SELECT t1.*, t2.* from t1 join t2 on t1.fk_id=t2.id

When I run the query above, both id fields are selected (t1.id and t2.id). My question is, how can I select the correct ID while I am looping through the result set? If I select $result->id, I will get the t2.id. Is there any way that I can get the t1.id also without explicitly selecting it in the query (i.e. t1.id as t1_id?) Also, please, let us know about some of your practices when it comes to naming the primary key columns.

Thanks!

Answer 1

You'll often see the primary key for table XXX named xxx_id. This keeps the name of the same "information identifier" the same everywhere: for example in another table YYY, you'll have YYY.xxx_id with a foreign key constraint to XXX.xxx_id. This makes joins easier (you don't have to specify the "on" constraint at all in many databases) and it solves the problem you're running into as well.

I'm not saying you should prefix every column name to create a faux-namespace, but in the case of "id" it is actually useful and descriptive. It is, after all, not just any kind of ID, it's a user ID, site ID, game ID, contact ID, what have you.

Answer 2

SELECT t1.id as id1, t2.id as id2, t1.*, t2.* from t1 join t2 on t1.fk_id=t2.id

Answer 3

You are probably using mysqli_result::fetch_assoc to return each row of your result set as an associative array. MySQL will let you have two columns with the same name in a query, but these do not map to an associative array the way you want them to—even though the associative array is doing exactly as it should.

Assume two tables, book and author, linked by the junction table book_author. In MySQL, you can run the following query, which returns two id columns:

SELECT b.*, a.*
FROM book AS b
JOIN book_author AS ba ON ba.book_id = b.id
JOIN author AS a ON a.id = ba.author_id
LIMIT 2;

+----+-----------------+----+--------------+
| id | title           | id | name         |
+----+-----------------+----+--------------+
|  1 | Design Patterns |  1 | Erich Gamma  |
|  1 | Design Patterns |  2 | Richard Helm |
+----+-----------------+----+--------------+

If you try to map one of these rows to an associative array, you end up with a single id element in your array:

$row = $result->fetch_assoc();
print_r($row);

Array
(
    [id] => 1
    [title] => Design Patterns
    [name] => Erich Gamma
)

The last id column in the row will overwrite any that precede it. Here’s the second row from the result set:

Array
(
    [id] => 2
    [title] => Design Patterns
    [name] => Richard Helm
)

This is just the same as modifying the value of an element in an associative array;

$row = array();
$row['id'] = 1;
print_r($row);
Array
(
    [id] => 1
)

$row['id'] = 2;
print_r($row);
Array
(
    [id] => 2
)

If you give each column a unique name in your query, either by doing so in the table itself, or giving it an alias in the query, the problem is avoided:

SELECT b.id AS book_id, b.title,
    a.id AS author_id, a.name
FROM book AS b
JOIN book_author AS ba ON ba.book_id = b.id
JOIN author AS a ON a.id = ba.author_id
LIMIT 2;

+---------+-----------------+-----------+--------------+
| book_id | title           | author_id | name         |
+---------+-----------------+-----------+--------------+
|       1 | Design Patterns |         1 | Erich Gamma  |
|       1 | Design Patterns |         2 | Richard Helm |
+---------+-----------------+-----------+--------------+

$row = $result->fetch_assoc();
print_r($row);
Array
(
    [book_id] => 1
    [title] => Design Patterns
    [author_id] => 1
    [name] => Erich Gamma
)

Alternatively, you could (and almost certainly should) use prepared statements instead. Although this can get round the problem of duplicate column names, using unique column names in your queries still makes things much easier to read and debug:

$sql = 'SELECT b.*, a.* ' .
   'FROM book AS b ' .
   'JOIN book_author AS ba ' .
   'ON ba.book_id = b.id ' .
   'JOIN author AS a ' .
   'ON a.id = ba.author_id';

$stmt = $mysqli->prepare($sql);
$stmt->execute();
$stmt->bind_result($book_id, $book_title, $author_id, $author_name);
while ($stmt->fetch()) {
    printf("%s, %s, %s, %s\n",
           $book_id,
           $book_title,
           $author_id,
           $author_name);
}