使用INSERT ... ON DUPLICATE KEY UPDATE更新表时出错

Question

I'm relatively new to mySQL. For some reason, I'm getting an error with my INSERT ON DUPLICATE KEY UPDATE command. This is what it looks like:

print "Writing status to file 
";
$mysqliNode = new mysqli('blah.net', 'us', 'pw', "CATV_Mon");
$stmtNode = $mysqliNode->prepare("INSERT INTO CATV_Mon.nodeDetail (nodID, pctRedYellow, count)
                                  VALUES (?,?,?) 
                                  ON DUPLICATE KEY UPDATE pctRedYellow   = VALUES(percentRedYellow)
                                                         ,count     = VALUES(count)                               
                                  ");
$index = "ABCF";
$tempCount = 5000; 
$node_percent_down = 55;
$stmtNode->bind_param("sss",$index,$node_percent_down,$tempCount);
if(!$stmtNode->execute) {
        $tempErr = "Error getting Node $nod info and put in table: " . $stmtNode->error;
        printf($tempErr . "
");    //show mysql execute error if exists  
        $err->logThis($tempErr);
        printf ("will die on purpose now 
");  
        die();          
} //if stmtNode didn't execute ok

When I run it, I see (it's not showing an error message):

Writing status to file
Error getting Node WILL info and put in table:  
will die on purpose now

I tried using the statement in MySQL Workbench:

INSERT INTO CATV_Mon.nodeDetail (nodID, pctRedYellow, count)
                                  VALUES ("ABCE",30,5000) 
                                  ON DUPLICATE KEY UPDATE pctRedYellow   = VALUES(pctRedYellow)
                                                         ,count     = VALUES(count) ;

And that works, and says 1 row(s) affected and I see it in the table. When I change the values to update and run it in workbench, it updates what is changed and looks good in the table.

Any ideas why it's failing in the php script? i tried changing the nodeID I'm trying to insert and it fails similarly.

The table structure is: nodID varchar(20) primary key pctRedYellow int(5) count int(8)

I searched online and it looks like I'm doing what I'm supposed to do. insert on duplicate key update. I was thinking I needed WHERE in my statement, but it's working in workbench, and looks good with the syntax at the link.

Answer 1

I found this, and it fixed it. bind param doesn't work

$stmtNode->bind_param('sii', $index, $node_percent_down, $tempCount);