Comparing Checkbox Data in Database with Checkbox Data from $_POST

[artemisbow]

Hi, I'm having problems with comparing values stored in database with values from checkbox... ok let me explain :)

Several users selects several checkbox in a form whose values are 1, 2, 3 and 4, and is submited to a script that stores the $_POST data in an array called locations[]. Following that, I did
[code]$locations = implode("|", $locations)[/code]
which gives me $locations = "1|2|3|4". $locations is then stored in a mysql table in the column user_location.

Next a visitor comes along, and in a seperate form, containing the same series of checkboxs, but selects 1 and 3, which is imploded and stored as $requiredlocations = 1|3.

Now this is where i'm stuck. The above visitor is filling up a search form to search for users who provide services in various locations. I initially thought of
[code]$sql = "SELECT * FROM users where user_location LIKE '%".$requiredlocations."%'[/code]
but that will only work if the visitor only checked a single checkbox. How should I write the code such that the visitor can search for multiple locations?

Will really appreciate any help. Thanks!

[artemisbow]

Please... I need help! Thanks!

[artemisbow]

Anyone?

[artemisbow]

bump^

[.josh]

hi [b]artemisbow[/b]. actually, i'm working on something very similar to this. the way i have gone about it is to setup 3 tables:

providers
======
provider_name
provider_id

services
=====
service_name
service_id

maintable
======
provider_id
service_id


this is a relational database. what happens is in table providers, you have a list of all providers and they each have their own id.

then, you have another table of services. all the available services are listed, and each one has its own id.

then you have this 3rd table (maintable) that looks something like this:

maintable
======
provider_id | service_id
---------------------------
1 | 3
1 | 4
1 | 8
2 | 1
2 | 3
2 | 6
2 | 8
.
.
.


now the idea behind all this is that when the user wants to search for providers based on the services they offer, you can refer to the maintable, and linkie the provider and service names to their id's from the other tables.

now, the query involved in making this happen well probably make you wet your pants. well it made me wet mine, anyways :)

anyways, the way i have my script setup so far is for example:

if you search for a provider by service1,2 and 3 you can have it match any of the 3 or all of the 3. that is, if a provider has any one of the 3 checked services, it will return that provider. Or, you can set it for 'all' and it will only return providers that offer all 3 of the checked services.

now, even though the query matches for the conditions above, it actually lists all of the services provided by each returned provider.

that is, let's say you have a provider1 and he offers service1, service2, service3, and service4.

now let's say you do a search to find all providers that offer service1 and service3. well, regardless of whether you do search for any or search for all, provider1 will be returned, because it offers both service1 and service3. but what will actually be returned is this:

provider1 | service1
provider1 | service2
provider1 | service3
provider1 | service4

in other words, it returns all services provided by provider1. the reason why i have it do this is because of how i want to actually display the results to the user. I want it to display something like this:

[b]provider1[/b] offers the following services: [b][i]service1[/i][/b], service2, [b][i]service3[/i][/b], service4

that's my own personal style i guess. the query probably wouldn't be half as complicated if all i wanted to do was return the provider names and that's it.

if you go to :
[a href=\"http://www.chroniclesofwar.com/providersearch.php\" target=\"_blank\"]http://www.chroniclesofwar.com/providersearch.php[/a]

you can see what i have so far. if you want, i can send you my code so far as well as a create table query for each table i have...

[artemisbow]

Thanks for the reply. I kindda figured it out by letting part of the sql query be generated by PHP depending on how many checkboxes the user ticked; rest of it is same as wat i had described above. :)