RemiC RemiC - 4 months ago 32
PHP Question

INSERT INTO array with MySQL and PDO

I read many tutorials to understand how inserting multiple lines in one query since I have to deal with multiple choices question in a PHP form (v5.4).

Unfortunately, I still have errors in my query.

Could you please help me?

To be more precise about the purpose, I made a form and I created a Database in MySQL with:


  • one table (A) to store all single values from radio button and text questions with a SERIAL id.

  • a table for each multiple choices question, in which I listed all possible answers in one column, and an ID code in another col.

  • and an "intermediate" table for each multiple choices question, to store the same id than the one created automatically during insertion in table A and the ID from the values selected in the checkbox question
    (so, for one form filled, I expect to get one row in table A, and as much rows as the selected values in each corresponding "intermediate" table.



My PHP code (which refers only to one multiple choices question as a test):

try {
$pdo = new PDO('mysql:host=myserver_url;dbname=my_db', 'my_user','my_pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
}
catch(PDOException $e) {
echo 'Fail to connect';
exit();
}

try {
if(isset($_POST['add'])){
if(isset($_POST['checkbox_name'])) {
$values = '('.implode('),(', $_POST['checkbox_name']).')';
$sql =$pdo->exec("INSERT INTO my_db.my_table (field1)
VALUES ($values) ");
}
}
}
catch(PDOException $e) {
$msg = 'ERROR PDO in ' . $e->getFile() . ' L.' . $e->getLine() . ' : ' . $e->getMessage();
die($msg);
}


HTML code:

<body>
<form name="form_1" method="post" action="form_1.php">
<input type="checkbox" name="checkbox_name[]" value="1"><label >Telephone</label><br/>
<input type="checkbox" name="checkbox_name[]" value="2"><label >Mail</label><br/>
<input type="checkbox" name="checkbox_name[]" value="3"><label >Other</label><br/>
<br/>
<br/>
<input type="submit" name="add" value="SEND"/>
</form>


Thanks a lot for your help!

Answer

I finally solved my problem. For those interested in the solution, here it is (with a "get the last id bonus"):

try {
    if(isset($_POST['add'])){   
        if(isset($_POST['nature_contact'])) {
            $sql = "INSERT INTO db.int_contact (id_g,id_nature_contact) VALUES " .rtrim(str_repeat('('.$lastID.', ?),', count($_POST["nature_contact"])), ',');
            $statement = $pdo->prepare($sql);   
            $count = 1;
            foreach($_POST["nature_contact"] as $nature_contact) {
                $statement->bindValue($count++, $nature_contact);
            }
            $statement->execute();
        }
    }   
}
// and then add a catch exceptions