SQL Search Functionality

[wchamber22]

Hi gents,

 

This will be an easy one for you all! I have a sql select statement and everything functions fine as is, BUT I would like to give users more freedom.

 

Using this sql statement the results only return if the user types the Botanical Name or Common Name of a particular plant exactly as it is spelled in the database:

 

$sql = mysql_query("SELECT PlantID, CommonName FROM plants WHERE BotanicalName='$BotanicalName' OR CommonName='$CommonName' OR Use1='$Use' OR Use2='$Use' OR Use3='$Use' OR Use4='$Use' OR Use5='$Use' ORDER BY CommonName");

 

What if the user doesn't know the species of a plant and only it's genus or the user only knows Maple tree and not which particular one they are looking for:

 

For example:

Plant in Database = Brandywine Red Maple

User Search would have to be exactly that "Brandywine Red Maple" for a result to return

Ideally I would like the user to be able to type in "Maple" and ALL Maples return

 

Let me know if you need more info

[Zurev]

Use the SQL LIKE command!

 

SELECT PlantID, CommonName FROM plants WHERE BotanicalName LIKE '%$BotanicalName%' OR CommonName LIKE '%$CommonName%'

[wchamber22]

Hi Zurev,

 

I tried the LIKE statement as you described and still no success, again here is my SQL to this point.

 

$sql = mysql_query("SELECT PlantID, CommonName FROM plants WHERE BotanicalName LIKE '%$BotanicalName%' OR CommonName LIKE '%$CommonName%' OR Use1='$Use' OR Use2='$Use' OR Use3='$Use' OR Use4='$Use' OR Use5='$Use' ORDER BY CommonName");

 

This sql statement now returns ALL the fields in the database when searching for anything.

 

Thanks, again.