Skip to main content
X
xxxx YYYY
Participant
November 21, 2011
Answered

Select Value Equal to Array

  • November 21, 2011
  • 1 reply
  • 918 views

I'm using Dreamweaver CS5 with PHP and a MySQL database.  I'm trying to read from an array (states[]) in the database that list all selected states a contractor might work in, i.e. WA,ID,OR... my insert record is savaing my array with the state abriviations with a coma in between with no space, which is fine with me.  My code for the insert record is:

if (isset($_POST['states'])) {

                    $_POST['states'] = implode(',', $_POST['states']);

          } else {

                    $_POST['states'] = null;

          }

To test what my result needs to be, I have tried to hard code the Select Value Equal To box in the Dynamic List/Menu to WA which selects WA (Washington) fine. The Allow Multiple Selections checkbox is selected for this list/menu.  When I try to hard code more than one state like WA,ID, nothing is selected.  I have also tried to select WA ID and WAID.  What is the proper format of the Select Value Equal To in order to select multiple items. 

Once the hardcoding is figured out I was going to try to figure out how to loop through in PHP and have it dynamically select from the record and echo (or otherwise print) in the proper format so multiple states from the array would be selected.  However, if someone out there has a sample, that would be great. 

Thanks in advance for your help!

Darren

This topic has been closed for replies.
Correct answer Ben M

First, and maybe I'm reading this incorrectly, but shouldn't the "implode" in your example be "explode" because you have the array but you want to get the values out of it?

Now I realize my next suggestion isn't what you want to hear but I used to think like you and store things in an array, loop an array, and then try to check the loop if certain values exist.  However, as time has taught me it's not necessarily the best way to store values that you will need for this type of instance.  Most of the time in instances like this, your user will be searching for contractors in a specific state.  So what I would recommend is creating another table with 2 columns:

contractor_id (a way to identify the contractor, this will be a primary key, that will link to a key for the "contractor_id" in your original table)

contractor_state (this will link an id to a state)

One loop would be to query the contractor state, while the contractor ID is met and you could run a join to pull out the contractor states while the ID condition is met.  The other side is to pull out all the contractor IDs where the state criteria is met.  You would need to run a JOIN on the query to combine the original table with this new table if you wanted to pull out the contractor name along with the state just to name an example.

A good article that you may want to read is the first answer from this StackOverflow question and the associated link in the response talking about normalization and about serializing data:

http://stackoverflow.com/questions/1790481/php-mysql-storing-array-in-database

1 reply

B
Community Expert
Ben MCommunity ExpertCorrect answer
Community Expert
November 21, 2011

First, and maybe I'm reading this incorrectly, but shouldn't the "implode" in your example be "explode" because you have the array but you want to get the values out of it?

Now I realize my next suggestion isn't what you want to hear but I used to think like you and store things in an array, loop an array, and then try to check the loop if certain values exist.  However, as time has taught me it's not necessarily the best way to store values that you will need for this type of instance.  Most of the time in instances like this, your user will be searching for contractors in a specific state.  So what I would recommend is creating another table with 2 columns:

contractor_id (a way to identify the contractor, this will be a primary key, that will link to a key for the "contractor_id" in your original table)

contractor_state (this will link an id to a state)

One loop would be to query the contractor state, while the contractor ID is met and you could run a join to pull out the contractor states while the ID condition is met.  The other side is to pull out all the contractor IDs where the state criteria is met.  You would need to run a JOIN on the query to combine the original table with this new table if you wanted to pull out the contractor name along with the state just to name an example.

A good article that you may want to read is the first answer from this StackOverflow question and the associated link in the response talking about normalization and about serializing data:

http://stackoverflow.com/questions/1790481/php-mysql-storing-array-in-database

X
xxxx YYYYAuthor
Participant
November 21, 2011

SnakEyez02,

Thank you for the response.

Initially I was going to do it as you mentioned and I currently have the tables set up this way by I'm now designing the pages and was having second thoughts.  As I was considering that I potentially am going to have hundreds of thousands of contractors in my database and several different areas will need to track data like this where there a multiple selections a contractor (or others in other categories, like vendors) will have to perform, I thought that it would be less expensive (processing wise) to store it as an array in the contractor's record.

So if I'm going have many groups of people and each group, like contractors, will have several areas where they would insert, change, and delete selected information, in this case the states where they do business, then you would recommend a table for only that purpose.  If the table is indexed it should be able to look up the information by contractorID without much expense, right?

On update if the record reads the contractorID and populates the selected states and the contracotor changes from WA, ID, and OR and now is working in WA, AK, and AL, can you point me in the right direction how it would keep WA and delete the ID and OR and create the AK and AL? 

Thanks again for your help. this concept will solve several different design issues and I'd like to get it right the first time before I put it into practice.


Darren

B
bregent
Participating Frequently
November 21, 2011

>I thought that it would be less expensive (processing wise)

>to store it as an array in the contractor's record.

It would actually be more expensive as there is a lot of overhead involved. It also violates basic rules of data normalization and makes it just plain hard to do simple queries. There are very few instances when it makes sense to store multiple values in a single column.

>On update if the record reads the contractorID and populates the

>selected states and the contracotor changes from WA, ID, and OR

>and now is working in WA, AK, and AL, can you point me in the right

>direction how it would keep WA and delete the ID and OR and create the AK and AL?

A real simple option would be to first delete all values for the contractor and then insert the selected values. You would not need to perform an update statement, or worry about which records to delete.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices