PHP MYSQL Search

[TheOner]

Hi
I have table called urls and it contains fields: id, channel, datetime, nick, link. I have filter form in my page and it filters urls with given filters. My problem is that currently it supports one filter at same time. I wanted that i can use all 4 "filters" same time. So i can like select 2007-04-20 and nick Example. So it shows all nick examples links in that date.

I don't know how to do it good. My only thought of this is i do many many if-senteces.

if(!empty($filter_nick) and !empty($filter_datetime))
{
$query = mysql_query("SELECT * urls WHERE nick = '$filter_nick' AND datetime = '$filter_datetime'");
}
elseif.....

I have 4filters so i shoud do 4^4 = 256 if-senteces haha. So that isnt good way.


The problem is that i cant put all filters to query like this:

mysql_query("SELECT * urls WHERE nick = '$filter_nick' AND datetime = '$filter_datetime' AND channel = '$filted_channel' AND link = '$filter_link'");

if user manage to use only channel and link filters. then there is "where nick = '' AND datetime = ''" and it wont work.

 

[vacunita]

Just whether they are set or not, and construct the query dynamically:

$query="SELECT * urls WHERE ";

if(isset($filter_nick)){
$query.="nick = '$filter_nick' ";
}
if(isset($filter_datetime)){
$query.="AND datetime = '$filter_datetime'";

}

if(isset($filter_channel)){
$query.="AND channel = '$filter_channel'";
}

.
.
.
etc.

$res=mysql_query($query);

this will add the "AND ..." statements to the query if the variables have values.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[towerbase]

Warning! There is a problem with vacunita's code. For example, if $filter_nick is false and $filter_datetime is true then the generated code will look like

SELECT * FROM urls WHERE AND datetime = ...

The simplest way round this is to replace the first four lines of vacunita's code with

$query = "SELECT * FROM urls WHERE 0=0 " 
if(isset($filter_nick)) {
  $query.=" AND nick = '$filter_nick'";
}

Incidentally, the maths in TheOner's posting in incorrect. If you have four filters then the number of if-sentences is 4^2 = 16.

Andrew
Hampshire, UK