CODEWITHSUNDEEP
phpmysql
Jack
Jack

Reputation: 722

MySQL add values to SET element

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.

Upvotes: 1

Views: 812

Answers (2)

miken32
miken32

Reputation: 42718

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.

Upvotes: 0

Gavriel
Gavriel

Reputation: 19237

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

Upvotes: 2

Related Questions