CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Mr.Manhattan
Mr.Manhattan

Reputation: 5504

PHP Prepared Statements fails to assign correct values

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.

Upvotes: 0

Views: 126

Answers (3)

Mr.Manhattan
Mr.Manhattan

Reputation: 5504

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]);
}

Upvotes: 0

matewka
matewka

Reputation: 10148

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
+------------+-------+

Upvotes: 1

Jay Blanchard
Jay Blanchard

Reputation: 34416

Change this - 

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

to this - 

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

So your parent id gets incremented too.

Upvotes: 1

Related Questions