sql syntax error

[stew]

hey there peeps...

i have the following SQL query that is obviously wrong. it forms part of search script for a database and i really have no idea where to begin..

SELECT * FROM gigs WHERE { date >= '1998-01-01' AND date < '2003-01-01' } OR { place LIKE '%astoria%' } OR { city LIKE '%london%' } OR { type = 'live' } 

the form has a start date and an end date, it also has a select which is the 'type' and two text inputs, 'place' and 'city'. not all fields are nessecary indeed none are nessecary...

its a big ask, but could someone help me out here?

[ir4z0r]

what exactly is your problem? 😕

$date1 = '1998-01-01';
$date2 = '2003-01-01';
$place = 'astoria';
$city = 'london';
$type = 'type';

$query = "SELECT * FROM gigs WHERE ((date >= '$date1') AND (date < '$date2' )) OR (place LIKE '%" . $place . "%') OR (city LIKE '%" . $city . "%') OR (type = '$type')";

[stew]

basically i have those fields that i want to search... (place, city, type and date) if just one of those fields is filled in (say $city = london) i'd like the SQl to return the rows that have the city to be equal to london. but here's my problem i also the need the same sql statement to return the rows if city = london and place = astoria. at the moment if one of the search fields are left blank then the search gets rows where place = astoria and city = null, but i just want to it search for rows with place = astoria and to ingore the empty fields... am i being clear?

im not too sure how to use LIKE and where the '%'s' need to go and also where the brackets should go... if i need them at all...

hope this isn't asking too much...
stew

[ir4z0r]

UNTESTED

$query = "SELECT * FROM gigs";

if ($date1 || $date2 || $place || $city || $type)
{
  $query .= " WHERE";
  if ($date1) $query .= " (date >= '$date1') AND";
  if ($date2) $query .= " (date < '$date2') AND";
  if ($place) $query .= " (place LIKE '%" . $place . "%') AND";
  if ($city) $query .= " (city LIKE '%" . $city . "%') AND";
  if ($type) $query .= " (type = '$type') AND";
  $query = substr($query,0,strlen($query)-4);
}

if a var is set, its corresponding condition is appended
finally, it cuts the last " AND"

abclondonxyz is matched by %london%
abclondon is matched by %london
londonxyz is matched by london%

[stew]

thank you very much for you help and sorry if you get another email to your inbox cloggin it up , just for this post!

but thanks alot...