我尝试实施 UPSERT 时出现问题

我在检查更新 PostgreSQL 中的表的条件时遇到此问题。它必须检查用户是否下载过一次，如果是，则添加 +1acessos.

<?php
$result2 = pg_query("SELECT * from downloads WHERE (nome = $_POST[nome_download] AND email = $_POST[email_download])");
if (pg_num_rows($result2) == 0){
$result = pg_query("INSERT INTO downloads (nome, email, estado, arquivo, acessos) VALUES ('$_POST[nome_download]','$_POST[email_download]','$_POST[estado_download]','$_SESSION[nome_arquivo_download]','1')");
}else{
$arr[acessos] = $arr[acessos] + 1;
$result = pg_query("UPDATE downloads SET acessos = $arr[acessos] WHERE (nome = $_POST[nome_download] AND email = $_POST[email_download])");
}


if (!$result){
echo "Não foi possível realizar o cadastro. Tente fazer o download mais tarde.";
}
else
{
echo "soft_bd";
pg_close();
}
?>

你参考$arr但从您发布的代码中并不清楚分配的位置。不管怎样，如果你想增加当前值acessos乘以 1，该方法是在多用户环境中完全不安全.

你也完全开放SQL注入。请改用准备好的语句。

In Postgres 9.5你甚至可以在单一陈述与新的UPSERT 实施INSERT ... ON CONFLICT ON ... DO UPDATE http://www.postgresql.org/docs/9.5/interactive/sql-insert.html#SQL-ON-CONFLICT- 假设有一个UNIQUE or PRIMARY KEY约束于(nome, email):

$sql = 'INSERT INTO downloads AS d (nome, email, estado, arquivo, acessos)
        VALUES ($1, $2, $3, $4, 1)
        ON CONFLICT ON (nome, email) DO UPDATE 
        SET    acessos = EXCLUDED.acessos + 1';

对于重复调用，您可以使用pg_prepare http://php.net/manual/en/function.pg-prepare.php and pg_execute http://php.net/manual/en/function.pg-execute.php。对于单次调用使用pg_query_params http://php.net/manual/en/function.pg-query-params.php:

pg_query_params($sql, array($_POST[nome_download]
                          , $_POST[email_download]
                          , $_POST[estado_download]
                          , $_SESSION[nome_arquivo_download]));