[SOLVED] multiple entries in 1 table

[ngreenwood6]

I have a section where a user can select multiple languages that they speak. I have a table in the database that when they register it puts it in the database as the language id from another table that is populated with available languages. It is working fine but the problem that I run into is if the user selects more than 1 language. I want them to be able to select 2 or more languages and it put them in the database as an id from the languages and then be able to call it later. Can someone please help me?

[flyhoney]

The robust fix is to use a join table:

 

table users

    id

 

table languages

    id

 

table users_languages

    user_id

    language_id

 

The users_languages table links users to languages.  So for every language they choose, create an entry in the join table.

 

[ngreenwood6]

i think you read the question wrong. I know how to join tables and my table is pretty much set up like that but a little different. the problem I am having is that in language id there will be 5 different id's. for example for user_id 1 they speak language id 1,3,5 so when it gets put in the database it will just look like 135. how can I make it so that I can pull it later on from the database as the actual language. splitting up the numbers because that would just look like language id 135

[gevans]

surely id's 1, 3, 5 should be different entries.

 

You could add them in comma seperated `1,3,5` and split the string into an array when you get them out of the table. Personally I would have 3 entries.

 

Example

--------

 

The user's id: 15

The user's languages: 1, 3, 5

 

users_languages table

user_id    lang

  15          1

  15          3

  15          5

[ngreenwood6]

That seems like a better idea but how could I have it split them when a user registers. Basically they are checking boxes that allows them to select there languages. I am confused as how to split them into 3 different values to insert into the database. Any help?

[gevans]

That'd depend on your form. If you used an array to name your check boxes you can just loop through them in your php code. If each is specifically names. You check in your php whether it has been checked or not.

 

For each item that has been checked add a new entry to your table

[ngreenwood6]

can you give me an example?

[gevans]

can you post your form?

[ngreenwood6]

I haven't actually created one yet but basically it would look like this:

 

<form method="post" action="submit.php">
  <p>
    <label>
      <input type="radio" name="languages" value="1">
      English</label>
    <br>
    <label>
      <input type="radio" name="languages" value="2">
      Spanish</label>
    <br>
    <label>
      <input type="radio" name="languages" value="3">
      Chinese</label>
    <br>
    <label>
      <input type="radio" name="languages" value="4">
      Japanese</label>
    <br>
  </p>
</form>

[flyhoney]

If they are selecting multiple languages it should be more like this:

 

<form method="post" action="submit.php">
    <label>
     <input type="checkbox" name="languages[]" value="1">
      English</label>
    <br />
    <label>
      <input type="checkbox" name="languages[]" value="2">
      Spanish</label>
    <br />
    <label>
      <input type="checkbox" name="languages[]" value="3">
      Chinese</label>
    <br />
    <label>
      <input type="checkbox" name="languages[]" value="4">
      Japanese</label>
    <br />
</form>

 

And then the code to handle that would look something like this:

<?php
$languages = implode(';', $_POST['languages']);
// and then update the languages field with the languages string
?>

 

But I would again like to stress, that a good database design does no serialize the language ids and store them in one field, it uses a table for users, and a table for languages and a table to join the two.