[SOLVED] Making advanced search, need help matching criteria

[CrazeD]

I'm making a bar review site for a client, and he wants an advanced bar search feature. He wants to search by name, zipcode, city, music, music genre, and bar type. He wants only the results that match all the criteria to be returned. IE: If name, zipcode, and music are selected, only the results that return true for all three values will be returned.

 

I'm having trouble matching the results from the search criteria. My code is very long and repetitive (you'll see why in a minute) so rather than posting it all, I'll paraphrase it. I don't have an error in my code, but rather in my logic. Right now, I have each option running its own SQL and then making an array with the bar ID's it finds. Then, I do an if statement to see if the array containing the ID's returned by the name array is empty. If not, I then do if statements to see if the rest of the options are empty and then do if statements to see if the contents of the other option's arrays are in the name array. If the name array is empty, I go to the next criteria and repeat this until all the options are covered. This is probably extremely confusing so I'll post the first if so you get the idea.

 

if (!empty ($nameBarsID)) {
		if (!empty ($zipcodeBarsID) ||
			!empty ($cityBarsID) ||
			!empty ($typeBarsID) ||
			!empty ($musicBarsID) ||
			(!empty ($musicBarsID) && !empty ($genreBarsID))) {
			if (!empty ($zipcodeBarsID)) {
				foreach ($zipcodeBarsID as $val)
				{
					if (in_array ($val,$nameBarsID)) {
						$barsID[] = $val;
					}
				}
			}

			if (!empty ($cityBarsID)) {
				foreach ($cityBarsID as $val)
				{
					if (in_array ($val,$nameBarsID)) {
						$barsID[] = $val;
					}
				}
			}

			if (!empty ($typeBarsID)) {
				foreach ($typeBarsID as $val)
				{
					if (in_array ($val,$nameBarsID)) {
						$barsID[] = $val;
					}
				}
			}

			if (!empty ($musicBarsID)) {
				foreach ($musicBarsID as $val)
				{
					if (in_array ($val,$nameBarsID)) {
						$barsID[] = $val;
					}
				}
			}

			if (!empty ($musicBarsID) && !empty ($genreBarsID)) {
				foreach ($genreBarsID as $val)
				{
					if (in_array ($val,$nameBarsID)) {
						$barsID[] = $val;
					}
				}
			}
		} else {
			foreach ($nameBarsID as $val)
			{
				$barsID[] = $val;
			}
		}
	} elseif (!empty ($zipcodeBarsID)) {
                        // and so on until all options are covered

 

Now at first I thought it worked alright, but then I realized that I'm only comparing each array to the beginning array, and not with each other. So, if I search by name, zipcode, and music, I'm only returning rows that have matching zipcode, and matching music, but not matching zipcode AND matching music (in the same row).

 

So, without making my code 5000 lines long, how can I accomplish this in an easier way?

 

Sorry if this is really confusing, I explained it as best I could.

 

Thanks.

[Floydian]

hm. that's a hard road to travel there

 

I personally construct a query using the different search options and then just do one database query at the end.

 

So, how about something like this:

 

<?php

// initialize a query string
$query = '';

$options = array('name', 'zipcode', 'city', 'music', 'music_genre', 'bar_type');

// The loop lets us check all the posted data
foreach ($options as $option) {

// if a search option is used, a request var would be available
if (isset($_REQUEST[$option])) {
	$value = $_REQUEST[$option];

	// we may want to handle name different from zip code or city
	switch ($option) {
		case 'name':
			$query .= ' and name = ' . mysql_real_escape_string($value);
			break;
		case 'zipcode':
			$query .= sprintf(' and zipcode = %d', $value);
			break; 
	}
}

}

// since our where clause ($query) starts with "and",
// we'll cut that out of the string
// we'll also add in the "where" part
if (strlen($query) > 0) {
$query = 'where' . substr($query, 4);
}

mysql_query('select blah from foo ' . $query);

 

 

I should note that the $options array values would have to correspond to the html form element names. so your text inputs or whatever it is you're using would need names like "name" or "zipcode", for this script to work.

[CrazeD]

Aha! I knew there was a much easier way!

 

Thanks a ton.

[Floydian]

You're welcome