mysql LIKE many search fields

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

mysql LIKE many search fields

Post by boo_lolly »

what if you had many search fields to search a SQL table. for instance, if you had a SQL table with many columns (that you would search through) like first name, last name, birth date, how would you search for all three queries and return only results that matched the user's input data?

for instance, if they wanted to search for all the people in my_search_table who had the first name 'Glen' AND the last name 'Lambert' AND who have a birthdate of 02/05/82. how can this be done in the query? something like

Code: Select all

$result = mysql_query("SELECT first_name, last_name, birth_date
             FROM my_search_table
             WHERE first_name LIKE '%". fname ."%' AND last_name LIKE '%". $lname ."%' AND
                  birth_month LIKE '%". $birth_month ."%' AND
                  birth_day LIKE '%". $birth_day ."%' AND
                 birth_year LIKE '%". $birth_year ."%'")
             or die(mysql_error());
how would you have that effect?
User avatar
zeek
Forum Commoner
Posts: 48
Joined: Mon Feb 27, 2006 7:41 pm

Post by zeek »

I think you want '=' instead of 'LIKE', and get rid of the '%'s. As far as the date, why do you have a seperate column for each date element? Just make birth_date a 'DATE' column type and format it like YYYY-MM-DD, then build a variable named $birth_date and make sure it's formatted YYYY-MM-DD.

Your query will then look something like...

Code: Select all

$query = "SELECT first_name, last_name, birth_date
                     FROM my_search_table
                     WHERE 
                      first_name = '". $fname ."' 
                     AND 
                      last_name = '". $lname ."' 
                     AND
                      birth_date = '". $birth_date ."'";
             

    $result = mysql_query($query) or die( mysql_error() );
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

LIKE or =

Post by Jaxolotl »

You shoud consider if you want a LIKE or EQUAL search

Code: Select all

LIKE '%". $something ."%'
will look for "$something" everywhere inside the string (end, middle,start)

Code: Select all

='". $something ."'
will look a string that is EQUAL to $something, NOT MORE NOR LESS

example

Code: Select all

LIKE '%ant%'
will find "ant","pant","anthology","antique","pantometer"

but

Code: Select all

='ant'
will return ONLY "ant"

if you want to search
for instance, if they wanted to search for all the people in my_search_table who had the first name 'Glen' AND the last name 'Lambert' AND who have a birthdate of 02/05/82. how can this be done in the query? something like
a LIKE search will find also:
Eglenia | Spilamberto | 02/05/82
Eve Glen | Mc Lambert | 02/05/82

and so on

PS. a LIKE search is case insensitive
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post by boo_lolly »

zeek, there are different variables for the birth_date to allow an admin user to make changes to each entry using an HTML drop-down menu. and to also search for a birth date, also using a drop-down menu. the birth-date can be searched using the '=' symbol, but the rest MUST be a LIKE query (leaving some room for mistakes in the search request).

keep in mind, also, that if the admin does not enter any information into one or two or three of these search fields, that the sql query will disregard them. so if they search for all the people with the first name 'glen' who were born in february, it will bring up exactly that. the other search values will all be 'NULL'.
Post Reply