插入单个属性的多个值 [英] insert multiple values in single attribute

问题描述

我有一个数据库的两个表，

  tbl_language：tbl_users
  LANG_ID LANG_NAME USER_ID USER_NAME user_lang
  1英语
  2西班牙语
  3德国
  4葡萄牙语

我的PHP code从tbl_lang选择语言，现在我有一个表格，用户可以选择一个以上的语言多个选择。同时插入数据库中的值，我想多语言值存储在一列/属性。凡user_lang属性只会在tbl_language的商店lang_ids用逗号（，）隔开。

如同

  tbl_users：
  USER_ID USER_NAME user_lang
  1 ABC 1,2
  2 XYZ 1,4

如何做到这一点使用PHP和MySQL？

我有样code位置：

 ＆LT; PHP$ USER_NAME = $ _ POST ['USER_NAME'];
$ user_lang = $ _ POST ['user_lang']; // user_lang从多个选择阵列
$ QRY =; //我需要此查询帮助
 

解决方案

从你的设置，考虑到 USER_ID 是一个自动增量PK：

  $ USER_NAME = mysql_real_escape_string（$ _ POST ['USER_NAME']）;
$ user_lang = mysql_real_escape_string（爆（''，$ _ POST ['user_lang']）;
$ SQL =INSERT INTO tbl_users（`user_name`，`user_lang`）VALUES（'{$ USER_NAME}，{$ user_lang}'）;
 

Howerver，如其他人所说的，你应该寻找一个3 ^RD 等级表：

  tbl_users_lang
USER_ID LANG_ID
 

和插入等（例如，没有错误处理，只是一个建议）

  $ 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为$ LANG）{
   $ LANG = mysql_real_escape_string（$ LANG）;
   $ SQL =INSERT INTO tbl_users_lang（`user_id`，`lang_id`）VALUES（{$ user_ID的}，{$ LANG}'）;
   的mysql_query（$的SQL）;
}
 

**编辑**

就作为奖金，与此查询，您可以获取分隔行对于任何给定用户的语言列表中的逗号。想你可能会感兴趣：

  SELECT DISTINCT u.user_id，u.user_name，GROUP_CONCAT（l.lang_name）
  FROM`tbl_users`为u
  LEFT JOIN`tbl_users_lang`作为UL ON u.user_id = ul.user_id
  LEFT JOIN`tbl_language`为L上ul.lang_id = l.lang_id
   - 在这里添加WHERE子句来过滤（例如：WHERE u.user_name LIKE'约翰％'）
 

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

解决方案

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%')

这篇关于插入单个属性的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！