search script ..query error

[netmastan]

Hi, please have look the search code ..what I am doing wrong..
I get

You have an error in your SQL syntax near 'where listname like '%my search word %' ' at line 1

$search = '';
$the_array = explode(' ', $q);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$search .= " '%$t%' ";
$i = 1;
}else{
$search .= " OR listname LIKE '%$t%' ";
}
}

$query = "select listid,cid,listname,description,city,state,country,postcode,website from yp_list where listname like $search  ";

if(!empty($_POST[cid])) {
$query .= "and cid like '%$cid%' ";
}
if(!empty($_POST[state])) {
$query .= "and state like '%$state%' ";
}
if(!empty($_POST[city])) {
$query .= "and city like '%$city%' ";
}
if(!empty($_POST[postcode])) {
$query .= "and postcode like '%$postcode%' ";
}
if(!empty($_POST[country])) {
$query .= "and country like '%$country%' ";
}

#$query .= " order by  listname ";
$result = mysql_query($query,$dbi);
$num_record = mysql_num_rows($result);

I am trying add advanc search function in my yellow pages. Like user can search choosing category,or all category,by country,by city etc..

[cgraz]

print $query right before $result and copy and paste what it prints. It will help in finding where the error is occuring without sorting through your code blindly.

Cgraz

[netmastan]

thank you for the good tips. I fixed the query. Here is my final code after modifcation.

$search = '';
$the_array = explode(' ', $q);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$search .= " '%$t%' ";
$i = 1;
}else{
$search .= " OR listname LIKE '%$t%' ";
}
}

$query = "select listid,cid,listname,city,state,country,postcode,website ";
$query.="FROM  yp_list where listname like $search ";


if(!empty($cid) && $cid !='ANY') {
$query .= "AND cid like '%$cid%' ";
}
if(!empty($state)) {
$query .= "AND state like '%$state%' ";
}
if(!empty($city)) {
$query .= "AND city like '%$city%' ";
}
if(!empty($postcode)) {
$query .= "AND postcode like '%$postcode%' ";
}
if(!empty($country) && $country !='ANY') {
$query .= "AND country like '%$country%' ";
}

echo"$query<br><br>";

$result = mysql_query($query,$dbi) or die ("Error".mysql_error());
$num_record = mysql_num_rows($result);

if i search in category "Acccountant" Which id is 74, City"Auckland" and country 'New zealand' whose id is 153.

I get this query

select listid,cid,listname,city,state,country,postcode,website FROM yp_list where listname like '%ANZ%' OR listname LIKE '%Bank%' AND cid like '%74%' AND city like '%Auckland%' AND country like '%153%' 

I should get only one result.

I have two records as follow

ANZ Bank,Auckland,New Zealand in category' Accountant'
ANZ Bank,Sydney,Australia in category' Accountant'

so when i search for ANZ Bank in city(Auckaland) and Country New Zealand.. i should get only one records.But I am getting two records.

Any suggestion.. basicly I want in search function .. i can choose one category or all category,one location or all or one country or all etc..

One example..
http://www.yellowmaker.com/cgi-bin/yellowmaker/se/advanced.pl

Thanks

[cgraz]

just briefly skimmed over your post; infact i'm half-asleep, but you want to try using parenthesis in your query so that it ends up looking like this:

select * FROM yp_list where (listname like '%ANZ%' OR listname LIKE '%Bank%') AND cid like '%74%' AND city like '%Auckland%' AND country like '%153%'

Cgraz

[netmastan]

Thank you thank you... it's working now now .

i changed the following line in the code

$query.="FROM  yp_list where listname like $search ";

to

$query.="FROM  yp_list where listname like ($search) ";

Thanks again for your help.

Regards