Multiple Select into Single Field

[TamedTech]

Hello guys,

I've got a form that submits mulitple values from a select field, they are numberic values.

I'm looing for a method to insert them into a single database field which would preferably have the values delimited by a comma or somthing.

Any ideas?

Thanks,

Rob

 

[sleipnir214]

My first idea is to tell you "Don't put multiple values into a single field in a database". Under nearly all circumstances, you will be much better off storing those multiple values in a related table.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[vacunita]

Assuming the select box looks like the one below
Notice the brackets [red][][/red] at the end of the select's name: This will turn the selected options into an array. Which can then be imploded into a comma separated string.

<SELECT multiple name="options[red][][/red]">
<option value=1>One</option>
<option value=2>Two</option>
<option value=3>Three</option>
<option value=4>Four</option>
<option value=5>Five</option>
</select>

And is submitted:

[green]Put the selected values into a variable[/green]
$opts=$_POST['options'];

[green]Implode the values into a comma separated string.[/green]
$lista=implode(",",$opts);
[green]Do whatever with the string.[/green]
echo $lista;

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[TamedTech]

Thanks for your help guys,

The basis background behind the issue is that i have a Table full of Books, and nother table full of catagories.

Each book can belong to a number of catagories, so i need a field in the book table that says a book belongs to catagories 3,7,10,15,24 for instants, so thats why i want to do it this way.

That impode feature sounds like the job for me, i'll give it a go this afternoon.

Thanks,

Rob

 

[sleipnir214]

TamedTech:
I strongly recommend that you reconsider your database schema. Your intended method of multiple values in one field will make updates much harder than if you used related tables.


If, for example, you needed to remove a category from a book, with your method, you would have to fetch the field, take it apart, remove the value, put it back together again, and store the new value back in the field. If you use related tables, it will be a matter of deleting one record from the appropriate table.

If you want to add a category to a book, you'll have to fetch the field, take it apart, add the new value, convert it back into a comma-delimeted list, then restore the new value. If you use related tables, it's just a matter of storing a new record in the appropriate table.


Also, you haven't specified which database system you're using, but multiple values in a single field also can't be indexed efficiently. You're going to lose a lot of performance trying to find all books in a particular category.


Again, I strongly recommend that you reconsider your database schema. For something like this, I would use three tables: one to record books, one to record categories, and a third which relates a record in the book table to a record in the category table.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[vacunita]

I agree with Sleipnir.

That method is just going to give you more head aches than its worth.

The correct method in relational Databases would be to have a table that relates the book to category.

Book Id CatID
1 1
1 2
1 4
2 3
2 5
3 7
3 1
3 2
4 5

This will make slectign and updating categories and boks a lot easier than multiple values in a field.

I strongly suggest you head Sleipnir's observations.


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[TamedTech]

Ah i see Sleipnir,

That is a very good suggestion, I'm using MySQL as my database.

Thanks for the suggestion about the database Schema .. i hadnt really given much thought to handling it that way.

I'll re think it and give it a go,

Thanks,

Rob