[SOLVED] 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.

[Tyche]

 

You can take advantage of the "WHERE 1" statement equating to true

 

Use something like this ...

$where_nick="1";
$where_datetime="1";
$where_channel="1";
$where_link="1";

if (!empty($filter_nick))$where_nick = " nick = '$filter_nick' "; 
if (!empty($filter_datetime))$where_datetime = " datetime = '$filter_datetime' "; 
if (!empty($filter_channel))$where_channel = " channel = '$filter_channel' "; 
if (!empty($filter_link))$where_link = " link = '$filter_link' ";
$query = mysql_query("SELECT * FROM urls WHERE $where_nick AND $where_datetime AND $where_channel AND $where_link");

 

 

 

[TheOner]

Hmmm. Sounds simple if that works. Have to try! Thanks.

[TheOner]

That worked good! Big thanks!

[bubblegum.anarchy]

This is somewhat how I handle a dynanic where clause:

 

mysql_query("
   SELECT * FROM tbl_name
   WHERE TRUE ".( !empty($fld_name_1) ? "
      AND fld_name_1 = '".$fld_name_1."'" : null ).( !empty($fld_name_2) ? "
      AND fld_name_2 = '".$fld_name_2."'" : null ).( !empty($fld_name_3) ? "
      AND fld_name_3 = '".$fld_name_3."'" : null );

 

I use to format the lines like this:

 

mysql_query("
   SELECT * FROM tbl_name
   WHERE TRUE "
      .( !empty($fld_name_1) ? " AND fld_name_1 = '".$fld_name_1."'" : null )
      .( !empty($fld_name_2) ? " AND fld_name_2 = '".$fld_name_2."'" : null )
      .( !empty($fld_name_3) ? " AND fld_name_3 = '".$fld_name_3."'" : null );