Selecting more than one option within a list

[EmmaGale]

I would like to set up my database so that the user can select more than one option within a list. At the moment I have the list as a drop-down box updating one field.

I thought about using tick boxes - but am not sure how the database should be constructed behind it.

Would be grateful to know what the standard practice for this kind of thing is. Both from the database point of view and what type of form field to interact with it.

with kind regards
emma

[brian.mark]

You'd have lots of options in the database side. If it isn't a long value you're saving, you could put it all in one text field. If there are a limited number of options, you could have a field for each option (blank or a value determining on/off). If there are a lot of options available, or the list could grow down the road, I usually create a unique table just for those responses.

I'll give an example.

Case: Someone could decide that there are 10 states that they would consider a job from. I don't want to create a table with 51 fields for states in their profile, and it's possible someone could select all of them, so this would be best left as a unique table.

The unique identifier is going to be two fields in this case. I'd set a primary index of profile_id (an id number for the person's profile) and state_name. I'd probably also set up a secondary index of just the profile_id for when I join those tables later.

When someone selects (usually checkboxes, but it could also be a multi-select box with instructions on using the CTRL key for multiple selections) multiples, I'd simply insert a record for each state they select along with the id for the profile they're creating.

The multi-select box is fairly simple to code if that's what you'd prefer. Checkboxes are very simple, so I'll ignore those and just give you a multi-select sample.

<SELECT MULTIPLE SIZE=5>
 <OPTION VALUE="AL">Alabama
 <OPTION VALUE="AK">Alaska
 <OPTION VALUE="AZ">Arizona
 <OPTION VALUE="CA">California
 <OPTION VALUE="CO">Colorado
 <OPTION VALUE="DE">Deleware
</SELECT>

The SIZE=5 says that 5 should be visable. It will automatically add a vertical scroll-bar as needed, so you can keep it showing a few options. If you have pre-existing data (editing), just add SELECTED within one or more of the option tags to have it pre-selected.

Any additional questions, just ask.

Brian.

[EmmaGale]

Thank you for your help with this. Ive uploaded the page im doing to my personal webspace so you can then see what im trying to do. The page is very basic - i need to fiddle with the design its not writing to the db yet.

http://www.s114085044.websitehome.co.uk/infection/

I kinda understand what you are talking about. Basically the field I am referring to is Resistence Pattern in the Primary Isolate Section. I have set up the option box - yep that was fine. What I have done though is numbered each option. Is this ok or should I have used codes as you did in your example?

Now where it comes to the DB im not so clear. I think i need to set up a new table which will have it own autonumber, and contain the number of record it refers to and the number of the infection. Is this right? I cant get my head around how the information is pulled together. Are there any tutorials about on this or is this easy to explain to a complete bimbo :) ?

Im not very practiced with databases yet and one of the things I would be really grateful of is if you could just have a look at what ive done. Its easy to see from the page because I have one main table which contains everything apart from the dropdown boxes. These are listed dynamically from separate tables. I did a little database work a while back and Im not sure that ive normalized (is that the right word) it properly.

Your assistance is greatly appreciated.
Kind regards
Emma

[brian.mark]

Numbers are fine. You'll just need a way to cross-reference them back, but that shouldn't be tough.

My only concern, ever, with using numbers (quickest method for your databases to do selects, by the way) is that at some point in the future someone may change what one of the numbers mean, making all the previously existing data linked incorrectly. As long as you're not concerned there, I won't be either.

Brian.

[EmmaGale]

Ok so I guess then im on the right track with the table set up.

Im still not clear on how i get the information from the page to the table correctly. I am using dreamweaver by the way but appreciate this may need some programming.

Are there any tutorials online that can help me with this. Basic ones that start from the beginning?

Maybe im asking too much of you - but did you take a glimpse at my page?

best wishes emma

[brian.mark]

Do you know any scripting languages? You'll need to use php, asp, .net, perl, or any other language that your server supports to actually do the saving.

The only one I'm currently fluent in (I dabble in them all, but don't claim to be fluent in most of them) is perl.

Quite a bit will depend on what your web host supports, so double check that before you begin trying to tackle any of them.

Brian.

[EmmaGale]

Im using Dreamweaver with ASP/VBScript. At first i only used the behaviours supplied with Dreamweaver, but as I am learning a bit more I am getting to grips with some of the programming basics.

I have set up quite a few dynamic pages now - and so i know my server supports ASP. Just this is the first time ive needed to use a list with more than one selection and i cant picture how the information goes from the page to the database and how they tie together.
Kind regards
emma

[kgun]

I found this book very valuable when I worked on a project for Norwegian oil plattforms.

Short about the project:

1. A central database.
2. Everything else organized around that database.
3. Documents organized in DocuLive.
4. A database for every oilfield.
5. Every little element had a tag in the database.
6. Used SyBase.

Excellent Book:
Bennet Wm. Mc.Ewan & David Solomon:
"Teach yourself Transact-SQL in 21 days from Sams Publishing."

Transact-SQL is a superset of SQL.

Invaluable tool.