MySQL 向 SET 元素添加值 [英] MySQL add values to SET element

问题描述

我正在尝试借助 php 表单向我的数据库中插入一个新行.

I'm trying to insert a new row to my database with the help of a php form.

CREATE TABLE person (
first_name      VARCHAR(30) NOT NULL,
last_name       VARCHAR(30) NOT NULL,
languages       SET('english', 'greek', 'german', 'japanese', 'spanish', 'italian', 'french', 'wookie', 'klingon', 'other') NOT NULL,
pid             INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(pid)
);

首先，我有上面的 MySQL 表.

First, I have the above MySQL table.

然后，通过下面的代码，我得到了所有内容的字段值

Then, with the following code, I get the field values for everything

<form class="sform" method="post">
<input type="text" name="first_name" value="" placeholder="First name" maxlength="30">
<input type="text" name="last_name" value="" placeholder="Last name" maxlength="30">
<select multiple="multiple" id="languages" name="languages[]">
                <option value='english'>English</option>
                <option value='greek'>Greek</option>
                <option value='german'>German</option>
                <option value='japanese'>Japanese</option>
                <option value='spanish'>Spanish</option>
                <option value='italian'>Italian</option>
                <option value='french'>French</option>
                <option value='wookie'>Wookie</option>
                <option value='klingon'>Klingon</option>
                <option value='other'>Other</option>
            </select>
</form>

此时请注意这是一个多选表格.(一个人可能会说一种以上的语言)

Note at this point that this is a multiple select form.(a person may speak more than 1 language)

然后，使用 php 获取表格中每个元素的值，但对于我最感兴趣的元素，我执行以下操作:

Then, using php I get the values for each element of the table, but for the one i'm most interested in I do the following:

$qfirst_name     = $_POST['first_name'];
$qlast_name      = $_POST['last_name'];
$qlanguages      = "'" . implode("','", $_POST['languages']) . "'";

我在这里调用 implode 方法的原因是，我得到一个以下形式的字符串:ex.'english','greek' 但是，那不是 MySQL 接受要初始化的 SET 的方式，因此，我像这样替换 单引号:

The reason I call the implode method here is that so I get a string back in the following form: ex.'english','greek' But, that is not how MySQL accepts SETs to be initialized so, I replace the single quotes like so:

$qlanguages = str_replace('\'', '', $qlanguages);

现在，$qlanguages 将是:ex.english,greek但这还不够接近，所以最后一步是在变量的开头和结尾都应用引号，如下所示:$qlanguages = '\''.$qlanguages.'\'';

Now, $qlanguages would be: ex.english,greek But that's still not close enough, so the final step is to apply quotes in both the beginning and the end of the variable like so: $qlanguages = '\''.$qlanguages.'\'';

最后，$qlanguages 以正确的形式'english,greek' 插入到数据库中，代码如下:

Finally, $qlanguages is in the correct form 'english,greek' to be inserted in the database with the following code:

$sql1 = "INSERT INTO person (first_name, last_name, languages) 
         VALUES ('$qfirst_name', '$qlast_name', '$qlanguages')";

然后我连接到我的数据库以将条目添加到数据库注意还有另一个文件建立连接，我刚刚包含它在我的php文件的顶部.

And then I connect to my db in order to add the entry to the database Note here There is another file which establishes the connection and i've just included it at the top of my php file.

if ($dbconn->query($sql1)) {
    echo "SUCCESS";
}
else {
    echo "FAIL";
}

我已经尽可能地解释了我的步骤，我的问题是:为什么当我在查询中包含 SET 字段时，我无法插入新的 person 在数据库中.我错过了什么?

I've explained my steps as best as I could, and my question is this: Why when I include the SET field in my query, I can't insert the new person in the database. What am I missing?

抱歉发了这么长的帖子.

Sorry for the long post.

推荐答案

你做到了:

$qlanguages = "'english,greek'";
$sql1 = "INSERT INTO person (first_name, last_name, languages) 
     VALUES ('$qfirst_name', '$qlast_name', '$qlanguages')";

所以你得到了这个:

$sql1 = "INSERT INTO person (first_name, last_name, languages) 
     VALUES ('qfirst_name', 'qlast_name', ''english,greek'')";

您可以看到双"单"引号代替语​​言

you can see the "double" "single" quotes in place of languages

这篇关于MySQL 向 SET 元素添加值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！