MySQL add values to SET element

Question

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)
);

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)

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']) . "'";

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);

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.'\'';

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')";

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";
}

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.

You're not fixing the security issues with the data, let alone getting your data into the table. You should use a [prepared statement](http://stackoverflow.com/questions/9629328/how-to-use-mysqli-prepared-statements-in-php) instead — Machavity, Jan 20 '16 at 22:47
Besides it's not very good way of doing db queries to answer your question `$qlanguages` has already `'` around it. You should do `...VALUES ('$qfirst_name', '$qlast_name', $qlanguages)";` — manRo, Jan 20 '16 at 22:54

score 2 · Accepted Answer · answered Jan 20 '16 at 22:52

2

You did this:

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

so you got this:

$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

answered Jan 20 '16 at 22:52

Gavriel

18,880
12
68
105

You have no idea, how long this has been bugging me... THANKS A LOT GOOD SIR. This is clearly a newbie error, but i'm still an undergrad :) – Jack Jan 20 '16 at 22:54

score 0 · Answer 2 · answered Jan 20 '16 at 23:05

The SET datatype is stored internally as a binary digit. IIRC you can take advantage of that fact and dispense with all this string manipulation. Also, I could erase your database if you put me in front of that web page for 5 seconds. You should use prepared statements to mitigate SQL injection attack risk.

<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='1'>English</option>
                <option value='2'>Greek</option>
                <option value='4'>German</option>
                <option value='8'>Japanese</option>
                <option value='16'>Spanish</option>
                <option value='32'>Italian</option>
                <option value='64'>French</option>
                <option value='128'>Wookie</option>
                <option value='256'>Klingon</option>
                <option value='512'>Other</option>
            </select>
</form>


<?php
$qfirst_name     = $_POST['first_name'];
$qlast_name      = $_POST['last_name'];
$qlanguages      = array_sum($_POST['languages']);

$stmt = $dbconn->prepare("INSERT INTO person (first_name, last_name, languages) VALUES (?, ?, ?)");
$stmt->bind_param("ssd", $qfirst_name, $qlast_name, $qlanguages);
if ($stmt->execute($sql1)) {
    echo "SUCCESS";
}
else {
    echo "FAIL";
}
?>

Untested, but I did something similar a couple of years ago when I was silly enough to use a SET column. Now I would suggest using a separate languages table.

separate languages tables is 3NF (I think) that's why I think it's better, I was thinking about this couple of hours ago when I couldnt figure out the SET problem at hand. — Jack, Jan 20 '16 at 23:24

MySQL add values to SET element

2 Answers2

Linked