INSERT INTO array with MySQL and PDO

Question

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!

While not answering your question, please use prepared statements! Just using PDO doesn't make your script secure to SQL-Injections. Build&prepare your query with [number of array elements] question marks, and then insert it with `$stmt->execute($array)` instead of imploding it. Check [this answer](http://stackoverflow.com/a/327384/1154316) on how to do this. — tillz, Jul 05 '16 at 10:47
$values displays (Array) both in my form and in my DB's table when I change "VALUES ($values)" into "VALUES '($values')". I read the answer and other (random) tutorials, and I have to admit I am a bit lost with all the PDO functions. Would you mind to write the syntax for my particular example please? Should I include any loop so that it repeats the INSERT INTO for every ticked answer? Sorry, I'm newbie... — RemiC, Jul 05 '16 at 15:30

score 1 · Accepted Answer · answered Jul 09 '16 at 12:24

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

INSERT INTO array with MySQL and PDO

1 Answers1