WHERE field contains....a challenge!

[pablo1988]

Hi,

 

My code below only allows me to search exact terms and therefore does not allow for spelling mistakes of names etc. Please can somebody advise how I can alter the code below to allow a user to search part of the search criteria and still retrieve results e.g. a search for "business" would return "business analysis", "business architecture" and so on. Any help would be greatly appreciated.

 

$sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '$fname' OR Last_Name LIKE '$lname' OR Skill_Name LIKE '$skill'";

 

Thanks a lot!

 

Paul

[cpd]

SELECT * FROM `table` WHERE `column` LIKE '%{$value}%';

 

% is a wildcard. In this instance it means search for a term with anything either side of it. If you were to put '{$value}%' it would only allow for additional characters after the term and vice-versa.

[Muddy_Funster]

CPD! You didn't just use a SELECT * 

[pablo1988]

Thanks CPD, I have applied % to both sides of skill only, however when I search by name and leave skill blank it brings back all records. I assume because I have used % on both sides it takes anything??

 

$sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '{$fname}' OR Last_Name LIKE '{$lname}' OR Skill_Name LIKE '%{$skill}%'";

 

See above code.

 

Please can you advise what I should do to fix this?

 

Thanks.

[pablo1988]

I assume it needs to have some code to say if blank ignore, rather than bring back all perhaps?

[Muddy_Funster]

you can either use it conditionaly through php, or use an SQL CASE statement

[pablo1988]

Muddy_Funster I am very new to php and don't know how I would make use of what you have written. Could you show me please?

[Muddy_Funster]

a little something like this:

 if (!empty($skill)) {
$skillQry = " OR Skill_Name LIKE '%$skill%'";
}
else{
$skillQry = '';
}

$sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '{$fname}' OR Last_Name LIKE '{$lname}' $skillQry";

[pablo1988]

Muddy_Funster would you do me a massive favor and add the code into the below please? I don't want to mess it up. It would be hugely appreciated!

 

<?php

 

if(isset($_POST['submit'])){

 

 

$fname = $_POST['fname'];

$lname = $_POST['lname'];

$skill = $_POST['skill'];

   

//connect  to the database

$db=mysql_connect  ("127.0.0.1", "root",  "") or die ('I cannot connect to the database  because: ' . mysql_error());

   

//select  the database to use

$mydb=mysql_select_db("resource matrix");

   

 

//query  the database table

$sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '{$fname}' OR Last_Name LIKE '{$lname}' OR Skill_Name LIKE '%{$skill}%'";

 

[Muddy_Funster]

Not much to mess up really, but here ya go :

<?php

if(isset($_POST['submit'])){


$fname = mysql_real_escape_string($_POST['fname']);   // these changes
$lname = mysql_real_escape_string($_POST['lname']);   // are important
$skill = mysql_real_escape_string($_POST['skill']);   // please find out why!
   
//connect  to the database
$db=mysql_connect  ("127.0.0.1", "root",  "") or die ('I cannot connect to the database  because: ' . mysql_error());
   
//select  the database to use
$mydb=mysql_select_db("resource matrix");

//build condition for WHERE:
if (!empty($skill)) {
$skillQry = " OR Skill_Name LIKE '%$skill%'";
}
else{
$skillQry = '';
}   

//query  the database table
$sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '{$fname}' OR Last_Name LIKE '{$lname}' $skillQry";