SQL Search not including WHERE
Posted: Wed May 10, 2006 9:50 am
I've been working on an advanced search for a while now. I have a couple multiple select fields in the form. I have one of them working so that if I select multiple options, it returns the correct results. So I figured since I got that working with no errors, I'd apply it to the other multiple select fields. So I applied it to my horse_breeds field, and it doesn't work.
If I select one option, such as "Quarter Horse" it will return the correct results. When I select more than one option, it forgets to put the "WHERE" in there. Even though, when I do the exact same thing for the horse gender field, it works... I don't get it and it's starting to make me mad. It should be the exact same thing.
The working one: horse_sex
The not-working one: horse_breed
It gives me this error
"SELECT * FROM text_horse horse_breed IN ('Quarter Horse','Thoroughbred')You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN ('Quarter Horse','Thoroughbred')' at line 1"
From what I can see, they are processed the exact same way, yet one works, and one doesn't. So what's up with that?
If I select one option, such as "Quarter Horse" it will return the correct results. When I select more than one option, it forgets to put the "WHERE" in there. Even though, when I do the exact same thing for the horse gender field, it works... I don't get it and it's starting to make me mad. It should be the exact same thing.
The working one: horse_sex
Code: Select all
if ($_GET['horse_sex'])
$sex = implode(', ', $_GET['horse_sex']);
$sexes = array('Gelding', 'Mare', 'Stallion');Code: Select all
if ($_GET['horse_breed'])
$breed = implode(', ', $_GET['horse_breed']);
$breeds = array('Arabian', 'Quarter Horse', 'Thoroughbred');Code: Select all
if ($horse_name != "" OR $state_province != "" OR $city != "" OR in_array($breed, $breeds) OR in_array($sex, $sexes) OR $age_min != "" OR $age_max != "" OR $height_min != "" OR $height_max != "" OR $color != "" OR $price != "" OR $registration !="" OR $horse_disciplines != "" OR $horse_attributes != "" OR $horse_temperment_min != "" OR $horse_temperment_max != "" OR $bloodline != "" OR $bloodline_where != "" OR $ad_type != "")
$sql .=" WHERE";
if(strlen($breed) > 2 AND $breed !="1"){
if($state1 OR $state2 OR $state3) $sql .= " AND";
if (count(array_diff($_GET['horse_breed'], $breeds)) == 0) {
$sql .= " horse_breed IN ('" . implode("','", $_GET['horse_breed']) . "')";
}
$state4 = true;
}
if(strlen($sex) > 2 AND $sex !="1"){
if($state1 OR $state2 OR $state3 OR $state4) $sql .= " AND";
// check that all elements in $_GET['horse_sex'] are in $sexes
if (count(array_diff($_GET['horse_sex'], $sexes)) == 0) {
$sql .= " horse_sex IN ('" . implode("','", $_GET['horse_sex']) . "')";
}
$state5 = true;
}"SELECT * FROM text_horse horse_breed IN ('Quarter Horse','Thoroughbred')You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN ('Quarter Horse','Thoroughbred')' at line 1"
From what I can see, they are processed the exact same way, yet one works, and one doesn't. So what's up with that?