insert multiple values in single attribute

Question

I have two tables in one DB,

  tbl_language:                           tbl_users
  lang_id    lang_name               user_id          user_name        user_lang
  1          English                                 
  2          Spanish                                 
  3          German
  4          Portuguese

I have PHP code to select languages from tbl_lang, Now I have a multiple select in a form where users can choose more than one languages. While inserting the values in database, I want to store multiple language values in one column/attribute. Where user_lang attribute would only store lang_ids of the tbl_language separated by comma(,).

Like

  tbl_users:
  user_id          user_name        user_lang
  1                 ABC              1,2
  2                 XYZ              1,4

How to accomplish this using PHP and MySQL ?

I have sample code here:

<?php

$user_name = $_POST['user_name'];
$user_lang = $_POST['user_lang']; // user_lang as array from multiple select
$qry = ""; // I need help with this query

Answer 1

From your setup, considering that user_id is an auto increment PK :

$user_name = mysql_real_escape_string($_POST['user_name']);
$user_lang = mysql_real_escape_string(implode(',', $_POST['user_lang']);
$sql = "INSERT INTO tbl_users (`user_name`, `user_lang`) VALUES ('{$user_name}', '{$user_lang}');

Howerver, as others have mentioned, you should probably look for a 3^rd level table :

tbl_users_lang
user_id    lang_id

And insert like (for example, no error handling, just a suggestion)

$user_name = mysql_real_escape_string($_POST['user_name']);
$sql = "INSERT INTO tbl_users (`user_name`) VALUES ('{$user_name}');"
mysql_query($sql);

$user_id = mysql_insert_id();
foreach ($user_lang as $lang) {
   $lang = mysql_real_escape_string($lang);
   $sql = "INSERT INTO tbl_users_lang (`user_id`, `lang_id`) VALUES ({$user_id}, '{$lang}');"
   mysql_query($sql);       
}

** Edit **

Just as a bonus, with this query, you can fetch a comma separated row for the language list of any given user. Thought you might be interested :

SELECT DISTINCT u.user_id, u.user_name, GROUP_CONCAT(l.lang_name) 
  FROM `tbl_users` as u
  LEFT JOIN `tbl_users_lang` as ul ON u.user_id = ul.user_id 
  LEFT JOIN `tbl_language` as l on ul.lang_id = l.lang_id
  -- add WHERE clause here to filter (ex : WHERE u.user_name LIKE 'John%')

Answer 2

This is what you want:

File view.html

<form method="POST" action="test.php">


    <label>Select langs: </label>
            <select name="languages[]" multiple="yes" size="5">

                <!--OR PUT lang id's instead of "en, fr, de" -->
                <option value="en">English</option>         <option value="fr">French</option>      <option value="de">Deutsch</option>


    </select>

    <button type="submit">Sub the form</button>

</form>

File : test.php

<?php

 // check if form submitted if so do 
 if ( !empty($_POST) ){

    //$_POST['languages'] is an array itself
    $langs = implode(', ', $_POST['languages']);
    //$langs you want to insert into the table:
    print $langs;

 }

Answer 3

Like Marc B suggested above, I wouldn't store multiple values in the same column - this is bad practice. You can save one line per language in the second table and still achieve the required result using group_concat as follows:

select t2.user_id, t2.user_name, 
group_concat(t1.lang_name separator ', ') as language
from tbl_users, tbl_language
where t1.lang_id = t2.user_lang

you can see an example here

Answer 4

Firstly, yes, not placing multiple values into one field, spreadsheet cell, or array element is the official answer. However, when you need to:

1. The field type to use is integer or large integer
2. Use code values that are prime numbers
   • 3 == English
   • 5 == French
   • 7 == Spanish
   • 11 == Italian
3. Store the product of all that apply into the field.
   • 21 == English and Spanish
   • 385 == French, Spanish and Italian

4. Use modulo functions to determine which values are in the field

   if ( field % 3 == 0 ) {  english() ;}
   if ! (field % 5) { french() ;}
   

5. The same value can appear multiple times
   • 9 == English, English

I first used this technique to store dimensions.

• 3 == time
• 5 == length
• 7 == mass
• 11 == charge
• 13 == temperature
• 17 == moles

For example, a first moment lever arm would have a dimension value of 35 == mass * length. To store fractional dimensions in an integer, I multiplied fractional dimensions by the product of all of them and dealt with it in processing.

• 255255 == 3*5*7*11*13*17
• force == mass * length / (second^2)
• force == ( 7 * 5 / ( 3 * 3 ) ) * 255255 * 255255
• force == 253381002875

Please do not ask for the code to extract the fractional dimensions. All this was 40 years ago in APL.