Re: Create Search engine with multiple form fields

["Bastien Koert" <phpster@xxxxxxxxx>]

On Sun, Jun 29, 2008 at 11:07 AM, Nasreen Laghari <nasreen_laghari@xxxxxxxxx>
wrote:

> Hi,
>
> I have an HTML form which has more then one fields. User can precise its
> search using one or more input fields E.g GigName,Date, Genre,Venue.
> The data is coming from 3 tables.  Below Query brings one tables record
> with each and every row of other two tables. Could any one please help me
> sort out the query.
>
> select * FROM gig g, venue v, genre ge  WHERE g.gigName LIKE
> '%".$gig_name."%' OR g.gig_date LIKE '%".$sdate."%' OR ge.name LIKE
> '%".$genre."%' OR g.ticket_price LIKE '%".$ticket_price1."%' OR
> g.ticket_price LIKE '%".$ticket_price2."%' OR v.venueName LIKE
> '%".$vname."%'"
>
> If I replace OR with AND I dont get any result.
>
>
> Regards
>
> Nasreen
>
>
>


I would avoid the ORs and build the statement dynamically making for a
cleaner sql

<php

$sql = "select * FROM gig g, venue v, genre ge  WHERE 1 ";

if(!empty($_POST['gig_name']))
{
  $sql .= " AND g.gigName LIKE
'%".mysql_real_escape_string(strip_tags($_POST['gig_name']))."%' "
}

if(!empty($_POST['sdate']))
{
  $sql .= " AND g.gig_date LIKE
'%".mysql_real_escape_string(strip_tags($_POST['sdate']))."%' "
}

if(!empty($_POST['genre']))
{
  $sql .= " AND ge.name LIKE
'%".mysql_real_escape_string(strip_tags($_POST['genre']))."%' "
}

if((!empty($_POST['ticket_price1']))&&(!empty($_POST['ticket_price2'])))
{
  //let's assume its a OR relationship (price is t1 or t2)
  $sql .= " AND (g.ticket_price LIKE
'%".mysql_real_escape_string(strip_tags($_POST['ticket_price1']))."%' "
  $sql .= " OR g.ticket_price LIKE
'%".mysql_real_escape_string(strip_tags($_POST['ticket_price2']))."%') "

}else{
  // let's assume here its t1 or t2 that is filled in, but not both
  if(!empty($_POST['ticket_price1']))
  {
    $sql .= " AND g.ticket_price LIKE
'%".mysql_real_escape_string(strip_tags($_POST['ticket_price1']))."%' "
  }

  if(!empty($_POST['ticket_price2']))
  {
    $sql .= " AND g.ticket_price LIKE
'%".mysql_real_escape_string(strip_tags($_POST['ticket_price2']))."%' "
  }

}

if(!empty($_POST['vname']))
{
  $sql .= " AND v.venueName LIKE
'%".mysql_real_escape_string(strip_tags($_POST['vname']))."%' "
}

mysql_result($sql,$conn);

?>



-- 

Bastien

Cat, the other other white meat