PHP Prepared Statements fails to assign correct values

Question

What i'm trying to do is to insert multiple rows of key/value pairs. The Entries need to be Unique (PrimaryKeys to be specific).

The statement is prepared with the count of values to insert:

$sql = "INSERT INTO `test_values` (`foreignID1`,`value`) VALUES";

for($ix=0;$ix<count($values);$ix++){
    if($ix>0){ $sql.= ", ";}
    $sql .= "(:parentID,:value$ix)";
}

echo $sql; //INSERT INTO `test_values` (`parentID`,`value`) VALUES (:parentID,:value0), (:parentID,:value1)

then, when preparing the statement i loop the values:

$stmt = $dbh->prepare($sql);
$stmt->bindParam(':parentID',$parentID);

foreach($values as $key => $value){
    $valName = ":value$key";

    var_dump($valName);
    var_dump($value);

    $stmt->bindParam($valName,$value);
}

the dumps tell me that every time other values are (correctly) assigned:

string(7) ":value0"
string(14) "one.domain.com"
string(7) ":value1"
string(18) "another.domain.com"

still, i get the SQL error:

Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '18-another.domain.com' for key 'PRIMARY'

i definitely know that these entries do not exist. I highly suspect the PDOStatement to bind the same value twice, but i have no idea why it should do so.

[EDIT]: suggested answer implemented, new output:

string(7) ":parent0"
string(14) "18"
string(7) ":value0"
string(14) "one.domain.com"
string(7) ":parent1"
string(14) "18"
string(7) ":value1"
string(18) "another.domain.com"

still the same error.

[EDIT2]: to avoid discussions:

INSERT INTO `test_values` (`parentID`,`value`) VALUES ('18','one.domain.com'),('18','another.domain.com')

applied directly to the database, using phpMyAdmin, works like a charm...what i fear happens to my statement is that the values somehow aren't correctly set, and something like this results:

INSERT INTO `test_values` (`parentID`,`value`) VALUES ('18','another.domain.com'),('18','another.domain.com')

because the constraint always fails on the LAST entry, no matter how many i try to insert.

[EDIT3]:

table structure, as asked:

CREATE TABLE IF NOT EXISTS `test_values` (
  `foreignID1` int(11) NOT NULL,
  `value` varchar(256) NOT NULL,
  PRIMARY KEY (`foreignID1`,`value`)
)

[EDIT4]:

what DOES work is not to use the chained insert, but single ones for each value:

$sql = "INSERT INTO `test_values` (`foreignID1`,`value`) VALUES (:parendID,:value)";
foreach($values as $key => $value){
    $stmt = $dbh->prepare($sql);

    $stmt->bindParam(":parendID",$parentID);
    $stmt->bindParam(":value",$value);

    $stmt->execute();  
}

....but that's not the actual point of the question.

You can't re-use placeholders in PDO. You've got the SAME `parentID` in EACH of your value tuples. just make it `":parentID$ix"` like you are for the value. — Marc B, Jan 14 '15 at 17:20
possible duplicate of [Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'](http://stackoverflow.com/questions/19359120/integrity-constraint-violation-1062-duplicate-entry-1-for-key-primary) — matewka, Jan 14 '15 at 17:20
Which field is set to the primary key? Does it have the `UNIQUE` constraint on it? — Jay Blanchard, Jan 14 '15 at 17:23
No, they can't be executed twice. Also, since the UNIQUE Constraint fails, there is no data inserted...so technically it could be called twice with no new effect — Mr.Manhattan, Jan 14 '15 at 17:26
On the larger scheme, there's no point in preparing one huge query. one major purpose of prepared statements is that you eliminate the repeated parsing of queries. you could prepare one SINGLE value insert, then repeatedly execute that with your different values. — Marc B, Jan 14 '15 at 17:28
@MarcB: i'll probably have to do it this way, but that can get quite a bit slower than compiling the query — Mr.Manhattan, Jan 14 '15 at 17:29
maybe. but also don't forget there's a length limit on query strings (set by max_allowed_packet). if you exceed that, then your query will get truncated and undoubtedly corrupted. — Marc B, Jan 14 '15 at 17:33
@matewka: that duplicate is nonsense, since my table does not have an auto increment. — Mr.Manhattan, Jan 14 '15 at 17:46

score 1 · Answer 1 · answered Jan 14 '15 at 17:21

1

Change this -

$sql .= "(:parentID,:value$ix)";

to this -

$sql .= "(:parentID$ix,:value$ix)";

So your parent id gets incremented too.

answered Jan 14 '15 at 17:21

Jay Blanchard

34,243
16
77
119

1

That did NOT work. Still the same error. Also, i'm often reusing parameters, so this can't be the root of the error. – Mr.Manhattan Jan 14 '15 at 17:24
It looks like you're trying to insert a duplicate value into a column which is the primary key @Mr.Manhattan. – Jay Blanchard Jan 14 '15 at 17:26
It's an connected primary, so 1-value and 1-anotherValue are possible! – Mr.Manhattan Jan 14 '15 at 17:28
Connected primary? Yes, multiple unique values are possible, but your error is pointing to a duplicate value @Mr.Manhattan. – Jay Blanchard Jan 14 '15 at 17:29
if i use phpMyAdmin and execute the resulting query, it works like a charm – Mr.Manhattan Jan 14 '15 at 17:30

matewka · Answer 2 · 2015-01-14T18:10:26.647

As far as I believe, the error which you're receiving indicates that the PRIMARY KEYs combination already exists in the table. Your table has composite primary keys which means, having

PRIMARY KEY (`foreignID1`,`value`)

you can INSERT such data

+------------+-------+
| foreignID1 | value |   # There are duplicated `foo` values
+------------+-------+   # for column `value` which is ok
|     1      |  foo  |   # because PRIMARY KEY is made of
+------------+-------+   # two columns together,
|     2      |  foo  |   # not each respectively
+------------+-------+
|     3      |  bar  |
+------------+-------+

while this situation is not possible

+------------+-------+
| foreignID1 | value |   # First two entries are
+------------+-------+   # a constraint violation.
|     1      |  foo  |   #
+------------+-------+   # Columns `foreignID1` and `value`
|     1      |  foo  |   # create a single PRIMARY KEY
+------------+-------+   # therefore having the same combination
|     2      |  bar  |   # of values in these columns is impossible
+------------+-------+

Please read the last edit i made to my post. if i seperate the inserts, it's working fine. this would not be the case if there already are same entries. — Mr.Manhattan, Jan 15 '15 at 08:12
It ALSO works with the chained insert when i manually execute the resulting statement via phpMyAdmin — Mr.Manhattan, Jan 15 '15 at 08:16

Mr.Manhattan · Accepted Answer · 2015-01-15T12:35:59.663

I finally found the answer myself:

the problem is the foreach loop, since bindParam() doesn't make a copy of the variable, but saves a reference to it. when the statement is executed, the values are read. after the foreach loop is done, the $value variable will hold the last value of the foreach loop.

To get it working, there has to be used either a for loop or the value may not be used:

foreach($values as $key => $value){
    $stmt->bindParam(":parendID$key",$parentID);
    $stmt->bindParam(":value$key",$values[$key]);
}

PHP Prepared Statements fails to assign correct values

3 Answers3