mysql search database with multiple columns

[shortysbest]

I have a search set up to search a table for the text entered in a textbox, I have two columns in the table, one with the first name of people, and the second with their last names, I am wondering how I can search both, so for instance:

 

I type in the search field: Roger Smith

 

in the database it would look like:

 

First_name-----|-----Last_name

-------------------|-------------------

Roger------------|-------Smith

 

my current query is:

 

$query = mysql_query("SELECT * FROM users WHERE fname LIKE '%$find%' OR lname LIKE '%$find%'");

 

But if I type both parts of the name it doesn't return anything. works fine if I just search for "Roger" OR "Smith".

[nicholasolsen]

 

You could try to use the explode function.

 

Example that would do the trick:

$find = $_POST/GET['name_from_search'];      // EDIT THIS

$find_string = explode(' ',$find);

$first_name = $find_string[0];
$last_name = $find_string[1];

$query = mysql_query("SELECT * FROM users WHERE fname LIKE '$first_name%' AND lname LIKE '$last_name%'  OR fname LIKE '$last_name%' AND lname LIKE '$first_name%' ");

 

 

What you did wrong was not using the AND syntax in your query. Instead you used OR.

If you use the query I wrote above the search will search the two columns for all combinations.

 

The reason I did the fname = $last_name is because some search "Smith Roger" instead of "Roger Smith". The query i wrote will give the same result in both instances.

 

 

Hope it helps

 

[nicholasolsen]

 

The simple answer to your q:

 

Your query was:

$query = mysql_query("SELECT * FROM users WHERE fname LIKE '%$find%' OR lname LIKE '%$find%'");

Instead you could use this:

$query = mysql_query("SELECT * FROM users WHERE fname LIKE '%$find%' AND lname LIKE '%$find%'");

 

This searches the table for First Name = Roger AND Last Name = Smith.