具有ON DUPLICATE KEY的预备语句 [英] Prepared Statement with ON DUPLICATE KEY

问题描述

我认为这应该很容易，但是我很难做到正确.我进行了一些搜索，但是对准备好的语句不熟悉，因此我无法通过查看在此处和其他地方找到的其他示例来弄清楚语法.无论如何，这是我的代码.

I think this one should be pretty easy but I am having trouble getting it right. I have searched a bit but being new to prepared statements I can't quite figure out the syntax from looking at other examples I have found here and elsewhere. Anyhow here is my code.

if($stmt = $mysqli -> prepare("INSERT INTO user_info (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, ?,?) 
    ON DUPLICATE KEY UPDATE (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, ?,?)")) {
    $stmt -> bind_param("sssssssi",$city, $state, $website, $public_contact, $user, $zipcode, $pic, $emailme);
    $stmt -> execute();
    $stmt -> bind_result($result);
    $stmt -> close();
}

用户是唯一的.这个IMO只是一个语法问题，所以有人可以帮我解决正确的语法吗?非常感激.

user is the unique. This IMO is just a syntax problem so could somebody help me out with the correct syntax? Much appreciated.

ETA:当我删除ON DUPLICATE KEY UPDATE部分时，只是为了帮助解决此问题，它确实按预期工作了，但显然，它只允许每个用户一个记录，并且不会更新

ETA: just to help trouble shooting this does work as intended when I remove the ON DUPLICATE KEY UPDATE part but obviously, it only allows the one record per user and will not update

UPDATE:永远无法找到一种有效的语法来使用ON DUPLICATE KEY UPDATE，所以我所做的(很可能不是最有效的方法)是为用户事先检查了表.如果存在该用户，则运行UPDATE，否则运行INSERT.下面是我的工作代码.希望这对陷入我困境的人有所帮助.

UPDATE: was never able to find a working syntax to use the ON DUPLICATE KEY UPDATE so what I did instead (admittedly probably not the most efficient way) was check the table before hand for the user. If the user exist I run and UPDATE, if not I run a INSERT. Below is my working code. Hope this helps somebody who gets stuck in my situation.

 $sql = "SELECT * FROM user_info WHERE user='$user'";

 if ($result=mysqli_query($mysqli,$sql))
 {
 /* Return the number of rows in result set */
 $rows=mysqli_num_rows($result);
 /* Free result set */
 mysqli_free_result($result);
 } 

 if($rows == 0) { 
if($stmt = $mysqli -> prepare("INSERT INTO user_info (city, state, website, public_contact, user, zipcode, pic, emailme) VALUES (?, ?, ?, ?, ?, ?, ?,?) ")) {

$stmt -> bind_param("sssssssi",$city, $state, $website, $public_contact, $user, $zipcode, $pic, $emailme);
$stmt -> execute();
$stmt -> bind_result($result);
$stmt -> close();
}

} else {

if($stmt = $mysqli -> prepare("UPDATE user_info SET city=?, state=?, website=?, public_contact=?, zipcode=?, pic=?, emailme=? WHERE user='$user'")) {

$stmt -> bind_param("ssssssi",$city, $state, $website, $public_contact, $zipcode, $pic, $emailme);
$stmt -> execute();
$stmt -> bind_result($result);
$stmt -> close();
}
    }

推荐答案

使用INSERT ... ON DUPLICATE KEY UPDATE的最简单方法是按以下方式使用VALUES子句，因此您无需重复执行UPDATE子句中的参数.它们仅对您在VALUES子句中传递的每一列使用相同的值:

The easiest way to use INSERT...ON DUPLICATE KEY UPDATE is to use the VALUES clause in the following way, so you don't need to repeat the parameters in the UPDATE clause. They just use the same values for each column that you passed in the VALUES clause:

if($stmt = $mysqli -> prepare("
    INSERT INTO user_info (city, state, website, public_contact, 
        user, zipcode, pic, emailme)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?) 
    ON DUPLICATE KEY UPDATE
        city = VALUES(city),
        state = VALUES(state),
        website = VALUES(website),
        public_contact = VALUES(public_contact),
        user = VALUES(user),
        zipcode = VALUES(zipcode),
        pic = VALUES(pic),
        emailme = VALUES(emailme)") {
    $stmt -> bind_param("sssssssi",$city, $state, $website, $public_contact, 
        $user, $zipcode, $pic, $emailme);
    $stmt -> execute();
    $stmt -> close();
}

IODKU语法要求您分别设置每一列.您不能像尝试那样在一个子句中列出所有内容.

The IODKU syntax requires that you set each column individually. You can't list them all in one clause like you were trying to do.

您应该始终报告任何对prepare()或execute()的调用的错误.或者，您可以使mysqli抛出异常:

You should always report any errors from any call to prepare() or execute(). Or you can make mysqli throw exceptions:

$mysqli -> report_mode = MYSQLI_REPORT_STRICT;

此外，您也不需要bind_result()，因为INSERT没有结果集:

Also, you don't need to bind_result(), since there is no result set from INSERT:

// NO: $stmt -> bind_result($result);

这篇关于具有ON DUPLICATE KEY的预备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！