dongtangu6889 2013-05-14 05:24
浏览 616

具有位置占位符的ON DUPLICATE KEY UPDATE的语法

This is REPLACE syntax

$sql = "REPLACE INTO 2_1_journal (Number,RecordDay, RecordMonth) VALUES ";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
$insertData[] = $_POST['date_month'][$i];
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}

However read that instead of REPLACE better use ON DUPLICATE KEY UPDATE

Trying to change like this

$sql = "INSERT INTO 2_1_journal (Number,RecordDay, RecordMonth) VALUES ON DUPLICATE KEY UPDATE (RecordDay, RecordMonth) ";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?, ?, ?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
$insertData[] = $_POST['date_month'][$i];
$insertData[] = $_POST['date_day'][$i];
$insertData[] = $_POST['date_month'][$i];
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}

But does not work (neither inserts nor updates)

What is incorrect?

If downvote, please write in comments why (for me to avoid write things that cause downvote)

Update

Changed code to this

$sql = "INSERT INTO 2_1_journal (Number, RecordDay) VALUES ON DUPLICATE KEY UPDATE RecordDay";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
}

get SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE RecordDay('21', ''), ('22', '')' at line 1

Changed to this

$sql = "INSERT INTO 2_1_journal (Number, RecordDay) VALUES ON DUPLICATE KEY UPDATE RecordDay=VALUES(Number)";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];

get SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE RecordDay=VALUES(Number)('21', ''), ('22', '')' at line 1

Changed code to this

$sql = "INSERT INTO 2_1_journal (Number, RecordDay) VALUES ON DUPLICATE KEY UPDATE RecordMonth=?";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
$insertData[] = $_POST['date_month'][$i];

get SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Can I make conclusion that last example is valid SQL syntax. But why error? Number, RecordDay, RecordMonth=? and $insertQuery[] = '(?, ?, ?)'; 3 variables and 3 tokens? Or I am wrong?

If $insertQuery[] = '(?, ?)'; the same SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Please, help. I am stuck.... no idea

Working code!!!

try {
$sql = "INSERT INTO 2_1_journal (Number, RecordDay) VALUES ";
$insertQuery = array();
$insertData = array();
foreach ($_POST['row_id'] as $i => $row_id) {
$insertQuery[] = '(?, ?)';
$insertData[] = $row_id;
$insertData[] = $_POST['date_day'][$i];
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery) . 'ON DUPLICATE KEY UPDATE RecordDay = VALUES(RecordDay);';
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}
}
catch (PDOException $e){
echo "DataBase Error: " .$e->getMessage() .'<br>';
}
catch (Exception $e) {
echo "General Error: ".$e->getMessage() .'<br>';
}

Your comments, please. And see location of ON DUPLICATE KEY UPDATE!!!

  • 写回答

1条回答 默认 最新

  • dongshui2254 2013-12-30 19:59
    关注

    Whenever you use code to build SQL statement, add a line:

    echo $sql;
    

    just before executing the statement. This way you can see what you are actually executing.

    You can then cut & paste the SQL statement directly into the database, and see what happens.

    Also -- to prevent SQL injection, add

    $row_id = mysql_real_escape_string($POST['row_id']);
    $date_day = mysql_real_escape_string($_POST['date_day']);
    $date_month = mysql_real_escape_string($_POST['date_month']);
    

    to the beginning of your code, and then use $row_id instead of $POST['row_id'] in the rest of the code. And $date_day instead of $_POST['date_day'], and $date_month instead of $_POST['date_month'], etc -- do that for all your POST and GET variables.

    Check this: http://xkcd.com/327/

    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题