Searching DB with mutiple fields

[xcandiottix]

$q=$_GET["firstName"];
$e=$_GET["lastName"];

$sql="SELECT * FROM user_table WHERE db_Fname = '".$q."' or db_Lname ='".$e."'";

$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
  {
  echo "<td>" . $row['db_Fname'] . "</td>";
  echo "<td>" . $row['db_Lname'] . "</td>";
}

 

I am creating a search function. This isn't quite working right though. If a user inputs firstName then first name and last name is echoed if found. If a user inputs lastName and it is found, first and last name is echoed. My table has 2 users. Keith Candiotti and Heather Martin. If I input firstName Keith my result is:

Keith Candiotti

If I enter firstname Keith lastName Martin my result is Heather Martin.

I want the code to be progressive if that makes any sense.. So if firstName is Keith it finds all Keiths... if a last name is entered then it checks all Keiths for that last name instead of the entire table.

 

Hope this makes sense to someone =p

[Jessica]

You'll need to do some checking to build your SQL statement. If someone enters both a first and last, you need to use AND. If someone only enters first OR last, only query for the one they entered (no OR)

[xcandiottix]

Okay, ill try that next.

 

On a google search I found something like this but I have never used it:

 

$sql="SELECT db_Fname FROM user_table MATCH ('db_Fname') AGAINST ('$q')";

 

It doesn't work tho.

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /peoplesearchcriteria.php on line 21

[akshay]

Hi.

 

You should try a new query:

 

//either use if(isset($_GET['firstname'])), etc. here, or use this simple technique:

$q=$_GET["firstName"];
$e=$_GET["lastName"];

if(!empty($q) && empty($e)) //thinking $q is not empty if user posts something
$sql="SELECT * FROM user_table WHERE db_Fname = '".$q."' ";
else
$sql="SELECT *FROM user_table WHERE db_Lname='$e'";


$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
  {
  echo "<td>" . $row['db_Fname'] . "</td>";
  echo "<td>" . $row['db_Lname'] . "</td>";
}

 

Hope it helps

[xcandiottix]

Hi.

 

You should try a new query:

 

if(!empty($q) && empty($e)) //thinking $q is not empty if user posts something

$sql="SELECT * FROM user_table WHERE db_Fname = '".$q."' ";

else

$sql="SELECT *FROM user_table WHERE db_Lname='$e'";

 

Thank you for the replies so far, they are much appreciated. In regards to this one, I think this would not find a first and last name match. If someone sets a first name it will find the first name ... but what if there was Keith Candiotti and Keith Doe. I think this code would still spit both names out on the results page. Basically the searching should go Find all matching first names -> if last name queried , find this last name from first name results OR find all matching last names -> if first name queried, find this first name from list of last name results.