How to retrieve all, searching MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gotlisch
Forum Newbie
Posts: 23
Joined: Wed Jan 11, 2006 8:37 am

How to retrieve all, searching MySQL

Post 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
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

$query = "SELECT `url` FROM `product` WHERE `name` LIKE '%{$new_search}%' AND `category`= '$category'";
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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;
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
gotlisch
Forum Newbie
Posts: 23
Joined: Wed Jan 11, 2006 8:37 am

Post 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!
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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;  ?>'
Post Reply