PHP MySQL inner join with same column name

Question

$q = $db->query("SELECT * 
        FROM people p
        INNER JOIN job j           
        ON p.job_id = j.id
        WHERE p.id = '$id'
        ORDER BY j.id ASC");
    $maps = array();
    while($row = mysqli_fetch_array($q)) {
        $product = array(
            'id' => $row['id'],
            'job_id' => $row['j.id']
        )
    }

table people

id

table job

id

In the above code I am doing an inner join between two tables. Both tables have an a column called id is there a way to differentiate between the two in my while loop?

I have tried the above but $row['j.id'] doesn't work and if I do $row['id'] then it writes both id and job_id with the same value.

you need to use alias while selecting the data and best is select only which is needed instead of *. — Abhik Chakraborty, Jun 30 '14 at 13:40
Put an "as [column name]" on one of the columns. That way you can specify that you want it named differently. — durbnpoisn, Jun 30 '14 at 13:40
You can also technically access both ID values by fetching in numeric mode and referring to them via index... I wouldn't recommend it, though. — Sam Dufel, Jun 30 '14 at 13:43
Possible duplicate of [PHP & MYSQL: How to resolve ambiguous column names in JOIN operation?](https://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation) — PhoneixS, Oct 27 '17 at 08:42

score 16 · Accepted Answer · answered Jun 30 '14 at 13:40

$q    = $db->query("SELECT *, j.id as jid, p.id as pid 
        FROM people p
        INNER JOIN job j           
        ON p.job_id = j.id
        WHERE p.id = '$id'
        ORDER BY j.id ASC");
$maps = array();
while ($row = mysqli_fetch_array($q)) {
    $product = array(
        'id'     => $row['pid'],
        'job_id' => $row['jid']
    );
}

PHP MySQL inner join with same column name

1 Answers1