mysql - PHP error when trying to select row from multiple tables

Tuesday, October 30, 2018

I am trying to use a SELECT * statement in PHP but from two tables. The query I make is the following:

$query2="SELECT * FROM london, manchester WHERE user = '$row[user]'";

$result = mysqli_query($con,$query2);

If I try it with just one table, say london, the query works fine. However, when trying to query two tables I get the following error:

mysqli_fetch_array() expects parameter 1 to be mysqli_result,

Here is how I call the result...

while($row = mysqli_fetch_array($result)) {
results....
}

Upon looking into the exact reason the error message occurs i am being told that "Column 'user' in where clause is ambiguous".

Answer

For something simple like that statement, try using a MySQL UNION.

Select * from london where user = '$row[user]' 

UNION Select * from manchester 
where user = '$row[user]'

Or with PDO...

Select * from london where user = :user UNION Select * from manchester where user = :user

...and then bind :user with your variable.

Blog