tags:

views:

41

answers:

3

I currently have an array I created like this:

$regions=array(
    1=>'North West',
    2=>'North East',
    3=>'South West',
    4=>'South East',
    5=>'West Midlands',
    6=>'East Midlands',
    7=>'London',
    8=>'Yorkshire',
    9=>'East England',
    10=>'Scotland',
    11=>'Wales',
    12=>'N. Ireland',
);

I will use this array to generate dropdown list options (value=>text) in my form. The selected option value will be stored in the database in an integer field.

This might just be a matter of personal preference, but I was just wondering whether this is the correct approach? Or should the array key be the same as its value? Could there be any potential problems further down the line (adding/deleting/modifying the array) when using this approach?

+3  A: 

I tend to stored the key value in this instance. However I would normally store the regions in a lookup table where the id and values are stored so any future modifications can be managed relatively easily using foreign keys etc.

simnom
The main reason I did not put it in a table was because I could not figure out an easy way of ordering the records in a custom order. For example if you delete a record, any new record automatically takes the deleted record's "position" in the table.
GSTAR
If you had a position field in the table you could run a couple of queries over the table when an item is deleted to shuffle the positions:update regions set position = position - 1 where position > " . (int) $pos;Would bring all the items above the one you have deleted back into numeric order. This way the id's in the table are still unique and can relate direct to other sources.
simnom
@GSTAR don't be silly. that's the only option. To add an ordering field to the table is easy. Why do you want new field in place of deleted one? What if you deleted 2 - what place it should take?
Col. Shrapnel
@simnom - so let's say I add a position field in my table and I create a new record which I want to be at position 2 in the table, but there is already a record with position 2. How would I do the query to update all the rows correctly?
GSTAR
@GSTAR - I suppose it depends a little on how you are handling the entry of the items. If you were just adding the entry through Navicat, phpmyadmin or similar then you could just update the existing entries first with something like "update regions set position=position where position >= 2" then insert your new region into position 2. If you are using a content management system to handle the insertion then I would suggest adding the new entry at the end and then create some sort of up/down movement mechanism using the same processes.
simnom
+2  A: 

This is generally correct, but typically the dropdown list (and thus the array) is generated from the database (at least at some point, even if cached later). This is so that someone doesn't change the code and the change the meaning of the previously stored values.

RedFilter
A: 

One of the nice things about MySQL is that it directly supports enum field types. IMHO its infinitely preferable to use significant data over surrogate values.

There is some debate over this in database circles - a quick google turned up about 82000 matches for "relational database enum type". Go read some of them.

But those who disagree with it as a practice unversally recommend maintaining the surrogate/value lookups within the database. NOT in the code.

C.

symcbean