CODEWITHSUNDEEP
phpmysql
Hunter Davis
Hunter Davis

Reputation: 13

How to correctly use WHERE with JOIN in SQL and PHP

So I'm trying to implement a JOIN in my PHP but I don't know how to include the WHERE clause with JOIN in my query.

I'm trying to do:

SELECT SpecialFacts.ConservationStatus, SpecialFacts.Reproduction, SpecialFacts.Length, Habitat.Type, Habitat.located

FROM SpecialFacts

INNER JOIN Habitat ON SpecialFacts.SpeciesID=Habitat.SpeciesID

WHERE SpeciesID="G. cuvier"

Basically I'm trying to make sure the join happens on the tables where the SpeciesID is G. cuvier but everything I have tried so far doesn't work and the error it is giving me now is "Column SpeciesID in where clause is ambiguous".

Here is my relevant PHP code:

<?php 

include 'connect.php';

$result = mysqli_query($connect,"SELECT SpecialFacts.ConservationStatus, SpecialFacts.Reproduction, SpecialFacts.Length, Habitat.Type, Habitat.located FROM SpecialFacts INNER JOIN Habitat ON SpecialFacts.SpeciesID=Habitat.SpeciesID WHERE SpeciesID='G. cuvier'") or die("fail" . mysqli_error($connect));


$i = 0;
while($row = mysqli_fetch_array($result))
{
    echo $row[$i];
    $i = $i + 1;
}

Upvotes: 0

Views: 1876

Answers (3)

Don&#39;t Panic
Don&#39;t Panic

Reputation: 41810

It's not giving you that error because you're using WHERE incorrectly. It's giving you that error because there are multiple tables in your query that have a column with that name, hence the "ambiguous". You just need to disambiguate it by adding the table name to the identifier.

WHERE SpecialFacts.SpeciesID="G. cuvier"

or

WHERE Habitat.SpeciesID="G. cuvier"

Since you're inner joining the tables on that column, either table should work for the WHERE clause. I would suggest using the smaller table for performance reasons, but honestly I'm not 100% certain if it will matter or not. You can do EXPLAIN on each one to see how they compare.

Upvotes: 1

Hemang
Hemang

Reputation: 1671

Use below query instead

SELECT SpecialFacts.ConservationStatus, SpecialFacts.Reproduction, SpecialFacts.Length, Habitat.Type, Habitat.located
FROM SpecialFacts INNER JOIN Habitat 
ON SpecialFacts.SpeciesID=Habitat.SpeciesID
WHERE SpecialFacts.SpeciesID='G. cuvier'

I have used single quote and used column name with table name.

Upvotes: 0

Lovepreet Singh
Lovepreet Singh

Reputation: 4840

Column SpeciesID exists in both tables, so it doesn't know which need to be compared to value given as G. cuvier. As you are writing every column name as table name with dot(.), the same should be in where condition column SpecialFacts.SpeciesID = "G. cuvier".

So query should be like:

SELECT SpecialFacts.ConservationStatus, SpecialFacts.Reproduction, SpecialFacts.Length, Habitat.Type, Habitat.located

FROM SpecialFacts

INNER JOIN Habitat ON SpecialFacts.SpeciesID=Habitat.SpeciesID

WHERE SpecialFacts.SpeciesID="G. cuvier"

Upvotes: 1

Related Questions