Geek Answers Handbook

Insert PDO when updating a duplicate key

After posting this question, Updating MySQL or asking for insertion or death, I changed the use of PDO, but I had problems using the duplicate key update phrase.

Here is 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"}

Actually there are 113 fields, but I did not want to waste space, showing them everything here. I am currently trying to inject INSERT / UPDATE into my database using 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 get an Invalid parameter number: parameter error message was not specified. I am pretty sure that my problems lie in ON DUPLICATE KEY UPDATE :fieldlist');, but I made so many different attempts, and none of them worked. Should I use ON DUPLICATE KEY UPDATEmore?

, : ::, :name , $name PDOStatement::bindValue PDOStatement->bindValue?

( , debugDumpParams , ). , $array_of_parameters, , $faculty ?

  //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;
+5
2

, , - SQL, . , :paramname , , .. $fields[] = sprintf("%s = :%s", $key, $key); :paramname, . .

, ON DUPLICATE KEY UPDATE :fieldlist, sql prepare().

, bindParam() , execute() . , :param SQL. . .

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

EDIT UPDATE, :

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

- , -. , placeholder .

ON DUPLICATE KEY UPDATE , , ( , PDO , ). . , , bindparam , , ( bind).

upserts, : Update, Insert. , . 0, Insert.

, 113 - , , .

+2

More articles:


All Articles