CODEWITHSUNDEEP
phpmysqlpdo
nrm
nrm

Reputation: 153

inserting multiple values in mysql table using PDO

I have a form that submits check boxes to a PHP PDO script. I have code that is as follows to store the values into a mysql table

$stmt = $dbPDO->prepare("INSERT INTO group_members(g_id, em_id) 
                         VALUES(:g_id,:em_id) 
                         ON DUPLICATE KEY UPDATE g_id = :g_id,  em_id = :em_id");
foreach ($_POST['id'] as $email) {
  $stmt->bindParam(':g_id', $gid , PDO::PARAM_INT);
  $stmt->bindParam(':em_id', $email , PDO::PARAM_STR);
  $stmt->execute();
}

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number'

This generates an error. What is the best way to insert multiple values into a mysql table with different values?

Upvotes: 0

Views: 150

Answers (3)

amflare
amflare

Reputation: 4113

You can't reuse a placeholder unless you have emulation mode turned on.

Generally speaking, PDO_MYSQL should have emulation on by default. The reason being that MySQL performs poorly with prepared statements. Emulation works significantly faster.

That said, if it is not on for whatever reason, you can set it manually using: 

$dbc->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);

In fact, if you are not sure, just set it anyway.

Upvotes: 0

JustOnUnderMillions
JustOnUnderMillions

Reputation: 3795

Every named placeholder have to be unique!

$stmt = $dbPDO->prepare("INSERT INTO group_members(g_id, em_id) VALUES(:g_id,:em_id) ON DUPLICATE KEY UPDATE g_id = :g_id2,  em_id = :em_id2");

$email=null;
//just bind once, that the logic behind 'bind()'
$stmt->bindParam(':g_id', $gid , PDO::PARAM_INT);
$stmt->bindParam(':em_id', $email , PDO::PARAM_STR);
$stmt->bindParam(':g_id2', $gid , PDO::PARAM_INT);
$stmt->bindParam(':em_id2', $email , PDO::PARAM_STR);

foreach ($_POST['id'] as $email) {
    $stmt->execute();//write your rows
}
$stmt->close();

Upvotes: 1

aynber
aynber

Reputation: 23011

You can't reuse placeholder names. You have to create new ones, or use VALUES in your update portion:

$stmt = $dbPDO->prepare("INSERT INTO group_members(g_id, em_id) VALUES(:g_id,:em_id) ON DUPLICATE KEY UPDATE g_id = VALUES(g_id),  em_id = VALUES(em_id)");

Upvotes: 0

Related Questions