multiple INSERT commands

[phppup]

It would be GREAT if someone could provide some specific structural info for this:

I have one checkbox each for Moe, Larry, and Curly respectively.  I have managed to assemble code that allows for each of their names:

$sql="INSERT INTO $table (first_name) VALUES...

It works great INDIVIDUALLY.  But I want to be able to insert any combination into the $table by checking the respective checkboxes and submitting the form.

It seems that initiating more than a single INSERT will not get the job done, and separating the values as variables hasn't worked either.

What is required?  Can you offer an example?

[Barand]

INSERT INTO tablename (first_name) VALUES ('Peter'), ('Paul'), ('Mary'), ('John'), ('George'), ('Ringo'), ... , ('Name1000');

       //
       //  Example using prepared query (PDO)
       //
if ($_SERVER['REQUEST_METHOD']=='POST') {
    foreach ($_POST['firstname'] as $name) {
        $params[] = '(?)';
        $data[] = $name;
    }
    $sql = "INSERT INTO tablename (first_name) VALUES " . join(',', $params);
    $stmt = $conn->prepare($sql);
    $stmt->execute($data);
}

 

Edited December 18, 2018 by Barand
add example

[phppup]

Thanks, but it may be a little over my head (although I think I have a slight understanding).

Any web-links that might help me grasp this better?

PS: in case we get busy, let me wish you a Happy Holiday and joyous New Year now.

[gizmola]

I thought that Barand did a pretty good job showing how to do this with the PDO Api.  If you don't understand some part of it you should reply with that portion of his code.  

Here's the underlying fundamentals from a SQL standpoint:

Insert 2 values (name, age) into a table:

INSERT INTO some_table (name, age)
VALUES
('Bob', 22)

The obvious syntactic matching is that after some_table you have a list of the columns that you will be specifying values for -  (name, age).

Then you have the VALUES keyword, and a parens which will contain the values in the order specified in the column list  (name, age):

VALUES ('Bob', 22)

SQL will also allow you to specify more than one set of values, which is what you are looking to do.  In order to do so it's as simple as adding additional parens sets, seperated by a comma:

VALUES (name1, age1), (name2, age2), (name3, age3) ....

INSERT INTO some_table (name, age)
VALUES
('Bob', 22),
('Fred', 25),
('Sam', 19)

 

So the question now becomes, how can you do this with PDO using a prepared query?  Well PDO is nice in that it will let you pass an array variable with the values so Barand's code is creating 2 queries.  His code assumes that your form provides a form element for each name using the attribute name="firstname[]"

When you utilize this, PHP will automatically put multiple values into a $_POST['firstname'] variable in the form of an array.  He then traverses that array and sets up an accompanying placeholder in the query:   

$params[] = '(?)';

In the same loop he sticks the data into the $data array.  

To finalize everything, he uses JOIN to turn the array of placeholders into a string that matches the number of names you had.  Let's assume you had 'Manny', 'Moe', 'Jack':

$sql becomes $sql = "INSERT INTO tablename (first_name) VALUES (?),(?),(?)";

This gets prepared, and then executed with the array of actual values.  

Try implementing this code with your form processing script and if you have problems let us know.

 

[phppup]

Thanks for the additional info.  I will give it a try.

I hadn't realized that the '(?)' was for the prepared statement.  I thought it was for me to add parameters of my own.

The shortcuts in PDF tend to confuse me.  Thanks again.