PDO Insert on Duplicate Key Update

问题:

After posting this question MySQL update or insert or die query I've change to using PDO but I'm having some issues using the on duplicate key update phrase.

Here's an example of my array data

array(114) {
["fname"]=>
string(6) "Bryana"
["lname"]=>
string(6) "Greene"
["m080"]=>
string(1) "c"
["t080"]=>
string(1) "-"
["w080"]=>
string(1) "-"
["r080"]=>
["notes"]=>
string(4) "yoyo"}

In reality there are 113 fields but I didn't want to waste the space showing them all here. I'm currently trying to INSERT/UPDATE into my database via the following code

try {
    $dbh = new PDO('login info here');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $stmt = $dbh->prepare(
        'INSERT INTO fhours ('.implode(",", array_keys($faculty)).')'.
        ' VALUES (:'.implode(",:", array_keys($faculty)).')'.
        ' ON DUPLICATE KEY UPDATE :fieldlist');

    $stmt->bindParam(':field_list', $field_list);

    foreach($faculty as $key=>$val){
        $stmt->bindParam(':'.$key, $val);
        $fields[] = sprintf("%s = :%s", $key, $key);
    }
    $field_list = join(',', $fields);
    //echo $stmt->debugDumpParams();
    $stmt->execute();
}
catch(PDOException $e){
    echo $e->getMessage();
    exit(); 
}

I'm getting the Invalid parameter number: parameter was not defined error message. I'm pretty sure my issues lies in ON DUPLICATE KEY UPDATE :fieldlist'); but I've made so many different attempts and none of them have worked. Should I be using ON DUPLICATE KEY UPDATE anymore at all?

Also, I'm new to the : and :: syntax, does :name mean it's a named variable kind of like $name and does PDOStatement::bindValue kind of like PDOStatement->bindValue?

Edit

In response to the first two comments below I've updated the code thusly (but still to no avail, the debugDumpParams says I have no params). Also, why create the $array_of_parameters when it becomes the exact same array as $faculty to begin with?

  //grab form data
$faculty = $_POST;
$fname = $_POST['fname'];
$lname = $_POST['lname'];
//delete the submit button from array
unset($faculty['submit']);
$array_of_parameters = array();
foreach($faculty as $key=>$val){
        $array_of_parameters[$key] = $val;
        $fields[] = sprintf("%s=?", $key);
}
$field_list = join(',', $fields);

try {
    $dbh = new PDO('mysql:host=localhost;dbname=kiosk', 'kiosk', 'K10$k');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $update =   'UPDATE fhours SET '.$field_list. 'WHERE fname="'.$fname.'" AND '.
                        'lname="'.$lname.'"';
    $stmt = $dbh->prepare($update);
    //echo $stmt->debugDumpParams();
    $stmt->execute(array($array_of_parameters));

    if($stmt->rowCount() == 0){
        $insert = 'INSERT INTO fhours ('.implode(",", array_keys($faculty)).')'.
                    ' VALUES (:'.implode(",:", array_keys($faculty)).')';
        $stmt = $dbh->prepare($insert);
        $stmt->execute(array($array_of_parameters));
    }
}
catch(PDOException $e){
    echo $e->getMessage();
    exit(); 
}

$dbh=null;

回答1:

What you have attempted to do is to dynamically build a SQL string that will become parameterized. The :paramname parameters are expected to be single values mapped to column values, where clause parameters, etc. Instead you have used $fields[] = sprintf("%s = :%s", $key, $key); to create a string of :paramname fields in order to plug into the query. This just won't work in a parameterized statement.

Rather than doing ON DUPLICATE KEY UPDATE :fieldlist, you should build the whole sql string before passing it into prepare().

Then rather than use the bindParam() method to bind each one individually, you can use an alternate syntax to execute() to pass in an array of expected parametric values. They need to be in the correct order, or have array keys the same names as the :param parameters in your SQL. See the docs for more info and examples.

$array_of_parameters = array();
foreach($faculty as $key=>$val){
    $array_of_parameters[$key] = $val);
}
$stmt->execute($array_of_parameters);

EDIT To properly use parameters in your UPDATE statement, do it like this:

// Create your $field_list before attempting to create the SQL statement
$field_list = join(',', $fields);

$update = 'UPDATE fhours SET '.$field_list. 'WHERE fname=:fname AND lname=:lname';
// Here, echo out $update to make sure it looks correct

// Then add the fname and lname parameters onto your array of params
$array_of_parameters[] = $_POST['fname'];
$array_of_parameters[] = $_POST['lname'];

// Now that your parameters array includes all the faculty in the correct order and the fname & lname,
// you can execute it.
$stmt->prepare($update);
$stmt->execute($array_of_parameters);

回答2:

The colon-prefixed name is nothing more than named placeholder. When you go to bind your params, you just bind your placeholder to some arbitrary value.

ON DUPLICATE KEY UPDATE is not very multi-DBMS friendly, but if you're connected to a database compatible it should work (as I don't believe PDO will block all that, but I could be wrong). I wouldn't use it just for portability's sake. You may want to check how you are binding your fieldlists though, bindparam should only do one parameter, and those are colums, which should not be quoted like values should (which bind param will do).

I design upserts by running a max of two queries: Update then Insert. I will first update and check if the number of rows updated is greater than 0. If the rows affected is 0, run the Insert.

Just an idle comment, 113 fields is a lot of fields, you may suffer some table performance if you're not careful