Asked  7 Months ago    Answers:  5   Viewed   263 times

I am looking to do multiple inserts using PHP PDO.

The closest answer I have found is this one

how-to-insert-an-array-into-a-single-mysql-prepared-statement

However the example thats been given uses ?? instead of real placeholders.

I have looked at the examples on the PHP doc site for place holders

php.net pdo.prepared-statements

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

Now lets say I wanted to achieve the above but with an array

$valuesToInsert = array(
  0 => array('name' => 'Robert', 'value' => 'some value'),
  1 => array('name' -> 'Louise', 'value' => 'another value')
);

How would I go about it with PDO and multiple inserts per transaction?

I imagine it would start of with a loop?

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");

foreach($valuesToInsert as $insertRow){

    // now loop through each inner array to match binded values
    foreach($insertRow as $column => value){
        $stmt->bindParam(":{$column}", value);
    }
}
$stmt->execute();

However the above does not work but hopefully will demonstrate what im trying to achieve

 Answers

20

First of all, ? symbols are real place-holders (most drivers allow to use both syntaxes, positional and named place-holders). Secondly, prepared statements are nothing but a tool to inject raw input into SQL statements—the syntax of the SQL statement itself is unaffected. You already have all the elements you need:

  • How to insert multiple rows with a single query
  • How to generate SQL dynamically
  • How to use prepared statements with named place-holders.

It's fairly trivial to combine them all:

$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
$n = 0;
foreach ($data as $row) {
    $insertQuery[] = '(:memberID' . $n . ', :programID' . $n . ')';
    $insertData['memberID' . $n] = $memberid;
    $insertData['programID' . $n] = $row;
    $n++;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
}
Wednesday, March 31, 2021
 
Daveel
answered 7 Months ago
48

Be sure you name your inputs uniquely. But you can name every column like this (see here for example):

<input type="text" name="column1[]" />
<input type="text" name="column2[]" />
<input type="text" name="column3[]" />

That way you can access the columns via PHP using a for loop.

for($i = 0; $i < $n; $i++) // If you have $n rows
{
    echo($_POST["column1"][$i]);
    echo($_POST["column2"][$i]);
    echo($_POST["column3"][$i]);
}

To insert multiple rows into your mySQL database use the following syntax (also: see here).

INSERT INTO
    tbl_name (column1, column2, column3)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

Now you should be set to build your SQL query.

Saturday, May 29, 2021
 
BartmanEH
answered 5 Months ago
57

No, there is no way to get a better error message with information of what table that is failing. The message you see in the array "Duplicate entry 'myuser' for key 3" is almost the information you want. The number 3 is actually the name of the index that you've made. You could change to a more meaningful, like "username", and get a better error message which you could forward to the user. If you want to make custom error messages you have to extract the information you want out of that message.

Saturday, May 29, 2021
 
treeface
answered 5 Months ago
16

The restrictions on multitable inserts include:

  • You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.

That isn't quite true - you can use a sequence, it just always gets the same value, so it can be useful to create parent and child records in one go by referring to the same sequence.

If you want to continue to use insert all you could work around that by using a non-deterministic function that gets the sequence value:

CREATE FUNCTION get_seq RETURN NUMBER IS
BEGIN
  RETURN postal_code_seq.nextval;
END;
/

INSERT ALL
  INTO POSTAL_CODE( postal_code,description)
    VALUES(get_seq,'Coimbatore')
  INTO POSTAL_CODE (postal_code,description)
    VALUES(get_seq,'Mumbai') SELECT * FROM DUAL;

2 rows inserted.

SELECT * FROM postal_code;

                            POSTAL_CODE DESCRIPTION        
--------------------------------------- --------------------
                                      1 Coimbatore          
                                      2 Mumbai              

But that's a bit awkward. You're probably better off using individual insert statements - using a multitable insert into a single table isn't really gaining you much anyway - or a trigger to set the unique column from the sequence, or a CTE/inline view to generate the values to insert.

Saturday, July 3, 2021
 
williamcarswell
answered 4 Months ago
42

If you use PDO::exec() the return value will be 1 if the row has been inserted and 2 if the row has been updated.

If you're using a prepared statement and PDOStatement::execute() the same is true for PDOStatement::rowCount()

Saturday, October 9, 2021
 
SheppardDigital
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share