Jump to content

mysql search database with multiple columns


shortysbest

Recommended Posts

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".

Link to comment
Share on other sites

 

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

 

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.