Advanced Search w/ Multi-Select Array - Why Can't I Do This?!

12sharks

I'm working on a site that deals with a MySQL database of stores across the country. I'm implementing an 'advanced search' section, and I can't for the life of me figure out how to handle queries that are derived of variables coming from multi-select lists. It seems like it would be an obvious thing, just going off the sheer ubiquity of similar search pages out there, but I just can't seem to come up with the logic behind it.

The two main problems I have are:

I. There are going to be several lists, all or any of which could have their own selections. There are two basic 'categories' of these lists:
a. A list where any of the selections will produce results, such as a 'State' field where a user could select 'NY' and 'TX' and the search would come up with stores in either state.
b. A list where only stores with all of the selections would produce results, such as 'Formats Carried', in which a user could select 'CDs' and 'Books' and only stores that carry both those formats would show up.

It seems like the use of OR vs. AND in the query would do the job, but what if a user selects 49 of the 50 states? Would the query read (in layspeak) NY OR TX OR.. (47 more times)? How can I add the flexibility to handle zero, one, or 50 selections?

II. Which brings me to my next question. I thought, well, perhaps a 'foreach' would handle the query, as in (simplified a little for clarity):

foreach ($_POST['states'] as $key => $value) {

$pullStores[] = mysql_query("SELECT stName, storeID FROM ksdrsStores WHERE stName LIKE '%$storename%' && stState LIKE '%$state%' && stFormats LIKE '%$formats%' ORDER BY stName ASC", $connection);

}

The 'states' POST is from a multi-select field in the form called $states[], as I thought if I made it a variable, then I could run it as a foreach later. It sort of worked, but then I'd have to run another foreach for the $pullStores[] array, which is fine, but it separates each query by, in this case, state. If I were to do this with the 'formats' POST array, not only would each format selected in the search have its own query result, but several stores would come up several times. So now, my thinking is perhaps there's a way to collect all of the results into yet another array, and that would somehow remove the duplicates and condense the output into a single list, but so far that hasn't really worked (at all).

And that doesn't even come close to addressing the problems from question #1! There must be a simpler way to go about this, but I haven't found a tutorial that even mentions anything along these lines. This forum is the closest I've come to finding anything relevant, so congratulations, everybody. Hopefully, this question hasn't already been answered and I just didn't see it.

Huge thanks to anybody who has a wise word,

--Samuel

devinemke

to use multiple selects in your query use the IN() syntax. here is a simple example:

if (!isset($_POST['submit']))
{
	$states = array(
	'AA' => 'Armed Forces Americas',
	'AE' => 'Armed Forces',
	'AK' => 'Alaska',
	'AL' => 'Alabama',
	'AP' => 'Armed Forces Pacific',
	'AS' => 'American Samoa',
	'AZ' => 'Arizona',
	'AR' => 'Arkansas',
	'BC' => 'British Columbia',
	'CA' => 'California',
	'CO' => 'Colorado',
	'CT' => 'Connecticut',
	'DE' => 'Delaware',
	'DC' => 'District of Columbia',
	'FM' => 'Federated States of Micronesia',
	'FL' => 'Florida',
	'GA' => 'Georgia',
	'GU' => 'Guam',
	'HI' => 'Hawaii',
	'ID' => 'Idaho',
	'IL' => 'Illinois',
	'IN' => 'Indiana',
	'IA' => 'Iowa',
	'KS' => 'Kansas',
	'KY' => 'Kentucky',
	'LA' => 'Louisiana',
	'ME' => 'Maine',
	'MH' => 'Marshall Islands',
	'MD' => 'Maryland',
	'MA' => 'Massachusetts',
	'MI' => 'Michigan',
	'MN' => 'Minnesota',
	'MS' => 'Mississippi',
	'MO' => 'Missouri',
	'MT' => 'Montana',
	'NE' => 'Nebraska',
	'NV' => 'Nevada',
	'NH' => 'New Hampshire',
	'NJ' => 'New Jersey',
	'NM' => 'New Mexico',
	'NY' => 'New York',
	'NC' => 'North Carolina',
	'ND' => 'North Dakota',
	'MP' => 'Northern Mariana Islands',
	'OH' => 'Ohio',
	'OK' => 'Oklahoma',
	'OR' => 'Oregon',
	'PW' => 'Palau',
	'PA' => 'Pennsylvania',
	'PR' => 'Puerto Rico',
	'RI' => 'Rhode Island',
	'SC' => 'South Carolina',
	'SD' => 'South Dakota',
	'TN' => 'Tennessee',
	'TX' => 'Texas',
	'UT' => 'Utah',
	'VT' => 'Vermont',
	'VI' => 'Virgin Islands',
	'VA' => 'Virginia',
	'WA' => 'Washington',
	'WV' => 'West Virginia',
	'WI' => 'Wisconsin',
	'WY' => 'Wyoming'
);

echo '
<form action="" method="POST">
select multiple states:<br>
<select name="state[]" size="10" multiple>
';

foreach ($states as $key => $value) {echo '<option value="' . $key . '">' . $value . '</option>';}

echo '
</select><br>
<input type="submit" name="submit" value="submit">
</form>
';
}
else
{
	if (isset($_POST['state']))
	{
		foreach ($_POST['state'] as $value) {$states[] = "'" . $value . "'";}
		$sql = 'SELECT * FROM table WHERE state IN(' . implode(',', $states) . ')';
		echo $sql;
	}
	else
	{
		echo 'you did not select any states';
	}
}

12sharks

Thanks for the response! This helps out a lot, but I still have a question:

As far as I can read into the logic of your code, the results of the search would be imploded into a string, right? So, after the variables are replaced with results, it would read something like:

WHERE 'NY' IN 'AR, NY, TX'

if Arkansas, New York and Texas were selected in the search. This makes sense, and I'm sure works for the single-entry fields like 'State'. On the other hand, there are fields like the aforementioned 'Format', which contain imploded strings of their own. For example, if a store carries CDs, LPs and Tapes, then the store's 'formats carried' field would have an imploded string that would read 'CDs, LPs, Tapes'.

The problem I see coming up with the application of the 'IN' syntax for this query is as follows (in same post-variable format as previously):

WHERE 'CDs, LPs, Tapes' IN 'CDs, Tapes'

In this case, a hypothetical user searched for all stores that carried both CDs and Tapes, but the hypothetical store that carried both of those, plus LPs, wouldn't come up because the string didn't read exactly as the store's 'formats' field.

It seems like it would produce the store in the search result if the user searched for stores that carry CDs and LPs, though, because they would read the same way the data was stored in MySQL. Is storing a store's 'formats carried' info as an imploded string not the way to go, or perhaps there's a workaround?

Also, your code has the:

if (isset($_POST['state']))

line in it. I understand why it's in there, but there are about six fields that would also necessitate an 'IN' query (i.e. the 'formats' field, a 'genres' field, 'store features', etc.), and it doesn't seem like it would make sense to run six different queries. Is there a way of going about validating the POST info while still being able to do just one consolidated query?

I realize I'm asking a lot, but I'm about two months into learning PHP and MySQL and this is the first real problem I've run into that seems to have no documentation whatsoever available. Thanks again for the information you've already posted, it's been very helpful.

--Samuel

devinemke

i think you need to rethink you data structure here. you have a table containing stores. each store can only be in one single state so that part of the query is pretty straight forward: if you want all stores in New York, New Jersey and Connecticut then simply do:

SELECT * FROM stores WHERE state IN('NY','NJ','CT')

you also want to store what product types the stores carry. your question suggests that this is a many-to-many data relationship, ie there a muliple stores and there are multiple product types and each store can carry any number of product types. for this you need a separate table for the product types and a 3rd table that ties them together using the primary key fields from the other tables. an example data structure:

stores
store_id (INT) PK
name (VARCHAR)
address (VARCHAR)
city (VARCHAR)
state (VARCHAR)
zip (VARCHAR)
phone (VARCHAR)

products
product_id (INT) PK
name (VARCHAR)

store_product_link
link_id (INT) PK
store_id (INT)
product_id (INT)

so if Joe's Music Depot in Newark NJ carries CD's and tapes then there would be (at least) 2 records in the link table using the store_id of Joe's and the product_id's of CD's and tapes. make sense?

12sharks

Totally. I think that makes a ton of sense. I guess I just considered 'one-field-plus-pk' groups not worthy of being their own table, but I now see the light.

Just to make sure I'm getting you exactly, there would be only as many records in the 'product' table as there are product types, but there would be many entries in 'store_product_link', right? As in, the only entries in the 'products' tab would read like:

1 - CDs
2 - LPs
3 - Tapes
etc.

but the entries for the 'link' table would be:

1 - store1 - product1
2 - store1 - product4
3 - store2 - product2
4 - store6 - product1
5 - store6 - product2
6 - store6 - product4

as opposed to:

1 - store1 - product1, product4
2 - store2 - product2
3 - store6 - product1, product2, product4

because there would need to be some sort of one-to-many relationship, even though that one-to-many connects two many-to-many relationships. Am I right?

This is pretty exciting (in a sad sort of way). I've been beating my head against a wall for two full days now. Thank you! Thank you!

Now, if I could ask one more question of you, sage: I'm guessing I'm going to need to do some sort of join to collect the results. Is this on the right track? If so, I think I can take it from here!

--Samuel

devinemke

12sharks wrote:
...but the entries for the 'link' table would be:

1 - store1 - product1
2 - store1 - product4
3 - store2 - product2
4 - store6 - product1
5 - store6 - product2
6 - store6 - product4

as opposed to:

1 - store1 - product1, product4
2 - store2 - product2
3 - store6 - product1, product2, product4

correct

12sharks wrote:
...I'm guessing I'm going to need to do some sort of join to collect the results. Is this on the right track?

correct, again