SELECT query help please

[realjumper]

Hi,

 

In my db I have id, first_name, last_name fields.

 

From the php page I want to search the db and return the results that the user enters into a form field named 'name'. This much works fine......

 

$query = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '$name' OR last_name LIKE '$name' ORDER BY first_name";
$result = mysql_query($query);

 

The above query works as expected if the user is searching by first_name or last_name. What I am having a problem with is how to construct the query if the user enters a first name and a last name into the form field, rather than a first name or a last name. I have tried playing with the IN condition but I think I'm looking at this wrong. Any help would be great. Thanks

[trq]

If they are putting both names in the one textbox separated by a space....

 

<?php

  if (isset($_POST['name'])) {
    $namefield = mysql_real_escape_string($_POST['name']);
    $names = explode(' ', $namefield);
    if (count($names) > 1) {
      $sql = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '{$names[0]}' OR last_name LIKE '{$names[1]}' ORDER BY first_name";
    } else {
      $sql = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '{$names[0]}' OR last_name LIKE '{$names[0]}' ORDER BY first_name";
    }
    if ($result = mysql_query($sql)) {
      if (mysql_num_rows($result)) {
        $row = mysql_fetch_assoc($result);
        // display data.
      }
    }
  }

?>

[MadTechie]

What about

 

<?php
if( isset($_GET['last_name']) && isset($_GET['first_name']) )
{
$OP = "AND"
}else{
$OP = "OR"
}
$query = "SELECT id, first_name, last_name FROM users WHERE first_name LIKE '$name' $OP last_name LIKE '$name' ORDER BY first_name";

$result = mysql_query($query);

?>

 

 

EDIT: thorpe just kicked my *****

[realjumper]

Thank you both....I will work through this and see what I can learn and fit inot my code :-)

[realjumper]

I have just fitted the code kindly supplied by Thorp into my application and I can see how this works now. I'll have to modify my code which loops through the results of the search, but this is going to work out fine. Thank you (both) very much :-)

[trq]

Your welcome, I'll mark the thread resolved.

[realjumper]

Sorry, just one more thing.....when I echo out $num_rows, it show the number of records returned unless the number returned is zero. Obviously I need to let the user know if his search found no result, but if I echo $num_rows when I know for certain that there is no record, all I get is white space, but I expected '0'. Have I missed something obvious?

[Glyde]

0, as an integer, when output by PHP, is interpreted basically as nothing.  The simple way of fixing this is by forcing a string data type:

$num_rows = (string) mysql_num_rows($result);

This should resolve your problem.

[realjumper]

Thanks....but still have the same problem. Any records returned are recorded by $num_rows, except when there are no records returned....I still get 'nothing!!'

[rcorlew]

Try this, I use it all the time.

 

<?php
if($num_rows > 0) {

//Put your stuff here

} 

if($num_rows == 0) {
echo "We are sorry there were no results found";
}
?>