Jump to content

multiple INSERT commands


phppup
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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 by Barand
add example
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.