Page 1 of 1

mysql LIKE many search fields

Posted: Tue Nov 28, 2006 9:50 am
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?

Posted: Tue Nov 28, 2006 10:20 am
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() );

LIKE or =

Posted: Tue Nov 28, 2006 10:51 am
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

Posted: Tue Nov 28, 2006 11:02 am
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'.