SQL Search not including WHERE

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sunnydayz
Forum Newbie
Posts: 6
Joined: Mon Feb 13, 2006 9:16 pm

SQL Search not including WHERE

Post by sunnydayz »

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

Code: Select all

if ($_GET['horse_sex'])
	$sex = implode(', ', $_GET['horse_sex']);
$sexes = array('Gelding', 'Mare', 'Stallion');
The not-working one: horse_breed

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;
}
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?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

There is obviously something wrong with your "if" statement.

Not gonna be able to help you with that without more information unfortunatelty
sunnydayz
Forum Newbie
Posts: 6
Joined: Mon Feb 13, 2006 9:16 pm

Post by sunnydayz »

What kind of information do you need?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Need to know where all the data is coming from for this part...

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 != "")
...that is equating to false with your other query, hence the lack of a WHERE clause

You need to figure out which particular part is causing the problem
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

When setting 'WHERE' and 'AND' based on conditionals, you really need to pay close attention to your logic. Whatever fires the 'AND' should also have fired a 'WHERE' somewhere, because without the WHERE the AND will throw a mysql error. Also, your conditional logic should also make sure that whatever fires the sql search criteria also fires the 'WHERE' and 'AND' parameters, or you'll end up with what you are getting.

The way you current code reads, if every member of that huge list of vars is not empty, it appends the query with " WHERE". But nothing else is dependant on that conditional. So if there other conditionals fail, your query will be "SELECT something WHERE". Same goes for the AND. You might want to look at the conditional statements and see where things can be grouped so that depending on the outcome of the conditional you will always have a clean query.
Last edited by RobertGonzalez on Wed May 10, 2006 11:52 am, edited 1 time in total.
sunnydayz
Forum Newbie
Posts: 6
Joined: Mon Feb 13, 2006 9:16 pm

Post by sunnydayz »

My current suspicion is that I may have too many items in my breeds array. I got my state_province and horse_sex arrays to work and they do not span more than one line. The breeds array spans multiple lines, even though it has the same line number. So would too many items in the array be a possibility? Or maybe not organizing the array correctly within lines? horse_breeds is the only one that doesn't work in the if() before "WHERE"
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Regardless of what us men say, size doesn't matter. At least not in this case. What matters is each piece of that conditional. If any conditional in that list of conditonals evaluates to true then a ' where' should be appended to the query. That being said, what you want to do is set the logic up so that if a then b else c.

Maybe you can do something like this to see what is happening...

Code: Select all

<?php
if ($horse_name != "" ||
	$state_province != "" || 
	$city != "" || 
	in_array($breed, $breeds) || 
	in_array($sex, $sexes) || 
	$age_min != "" ||
	$age_max != "" || 
	$height_min != "" || 
	$height_max != "" || 
	$color != "" || 
	$price != "" || 
	$registration !="" || 
	$horse_disciplines != "" || 
	$horse_attributes != "" || 
	$horse_temperment_min != "" || 
	$horse_temperment_max != "" || 
	$bloodline != "" || 
	$bloodline_where != "" || 
	$ad_type != "") {
	// This conditional is saying that if any one of the above conditions is met, add " where" to the query
	// So, if $price = 'foo', add the where
	$sql .=" WHERE";
}

if (strlen($breed) > 2 && 
	$breed !="1") {
	// This one us saying that if the length of the string $breed is longer than 2 AND 
	// the value of the variable $breed is not the number 1...
	if ($state1 || 
		$state2 || 
		$state3) {
		// If $state1, $state2 or $state3 is true, append " and" to the query
		$sql .= " AND";
	}

	if (count(array_diff($_GET['horse_breed'], $breeds)) == 0) {
		// If the passed data and the coded data are the same append the query with ...
		$sql .= " horse_breed IN ('" . implode("','", $_GET['horse_breed']) . "')";
	}

	$state4 = true;
}

if (strlen($sex) > 2 && 
	$sex !="1") {
	// This one us saying that if the length of the string $sex is longer than 2 AND 
	// the value of the variable $sex is not the number 1...
	if ($state1 || 
		$state2 || 
		$state3 || 
		$state4) {
		// If $state1, $state2, $state3 or $state4 is true, append " and" to the query
		$sql .= " AND";
		/*****************************************************************************
		 * THIS WILL CAUSE PROBLEM IF AND HAS ALREADY BEEN APPENDED TO THE QUERY
		 ****************************************************************************/
	}

	// check that all elements in $_GET['horse_sex'] are in $sexes
	if (count(array_diff($_GET['horse_sex'], $sexes)) == 0) {
		// If the passed data and the coded data are the same append the query with ...
		$sql .= " horse_sex IN ('" . implode("','", $_GET['horse_sex']) . "')";
	}

	$state5 = true;
}
	
?>
sunnydayz
Forum Newbie
Posts: 6
Joined: Mon Feb 13, 2006 9:16 pm

Post by sunnydayz »

Alright, I got it figured out. Thank you.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So why not share it with the community so we can all benefit from it? Also, can you change your original Topic title to whatever it is now with [SOLVED] in front of it?
Post Reply