在SELECT中加入搞乱我的'id'字段 - SQL / PHP

Question

This is my query:

$query = mysqli_query($conn, "SELECT *  
                                FROM zoekwoning 
                                    JOIN users ON zoekwoning.user_id = users.id 
                                WHERE users.email='$session_email'");

Basically the query gives me the values of all added rows from the database the current logged in user added. If I test this query in PHPMYADMIN then it gives me the correct result, being first column the id and 2nd column the user_id.

Result from PHPMYADMIN

Then I put in a while loop

while ($record = mysqli_fetch_assoc($query)){
    echo "<form action=# method=post>";
    echo "<table>";
    echo "<tr>";
    echo '<td><input type="label" name="id" value"' . $record['id'] . '"></td>';
    echo "</tr>";
    echo "</table";
    echo "</form>";
}

Gives me this result, if I check the array:

print_r($record);

First 2 values should be first and second column from table 'zoekwoning'. But it shows the value from the 2nd column 2 times.

Answer 1

While fetching the data with mysqli_fetch_assoc() the id from the first table is overwritten by the id of the second table. So you should not use SELECT * if multiple tables with same column names are involved. Use table prefixes and column aliases like:

SELECT zoekwoning.id as zoekwoning_id, ... , users.id as user_id ...

Then you can access the values with $record['zoekwoning_id'] and $record['user_id'].