Page 1 of 1

How to retrieve all, searching MySQL

Posted: Tue Jan 17, 2006 11:16 am
by gotlisch
Hi,

I'm implementing a search function on my web page. The user will be able to enter a search string, but also to specify some search criteria from a dropdown menu. My question is the following: If the user doesn't specify any search criteria (leaves it empty) then I want the search to disregard it.

As the code stands, if the user left category empty MySQL would look for a product with the category="", how can I change that to category=ALL. I don't want to do it with PHP!!

Code: Select all

$query = "SELECT url FROM product WHERE name LIKE '$new_search' AND category='$category'";

Cheers


Michael

Posted: Tue Jan 17, 2006 11:22 am
by Jenk

Code: Select all

$query = "SELECT `url` FROM `product` WHERE `name` LIKE '%{$new_search}%' AND `category`= '$category'";

Posted: Tue Jan 17, 2006 12:50 pm
by pickle
Something like this should work:

Code: Select all

SELECT
   url
FROM
   product
WHERE
   name LIKE '$new_search AND
   (CHAR_LENGTH('$category') > 0 AND category = '$category') OR
   (CHAR_LENGTH('$category') = 0 AND category = 'ALL')
If you've got a huge database though, that could increase quite a bit of overhead. Why don't you want to do it in PHP - it's real easy:

Code: Select all

$category = (strlen($category) == 0) ? 'ALL' : $category;

Posted: Tue Jan 17, 2006 1:45 pm
by gotlisch
The reason why I didn't wanna use PHP is because I was hoping there something like an "ALL" or "ANY" command built-in in MySQL. What I mean is:

SELECT * FROM product WHERE category='ANY'

This way I could just give the reselected value in my dropdown list this "ANY" and I would have to make such a fuss of it.

Something like the SELECT *

Do you understand what I mean?

Thanx for your relies!

Posted: Tue Jan 17, 2006 1:54 pm
by raghavan20
you cannot really make if condition in sql statement. can you tell us the reason why you want to that in sql statement?
this is an extension of pickle model supposed to be the easiest way to do it.

Code: Select all

SELECT 
   url 
FROM 
   product 
WHERE 
   name LIKE '%$new_search%' 
AND 
   category like '<? echo (strlen($category) == 0) ? 'ALL' : $category;  ?>'