PHP - 用倍数行更新SQL语句 [英] php - update sql statement with multiples rows

问题描述

我知道这个问题已经存在了，但我无法弄清楚如何解决它在我的情况。我尝试用用sql UPDATE语句，但我有问题的倍数行的时候。

I know this problem has already been around but I cannot figure out how to solve it in my case. I try to use the UPDATE statement with sql but I have a problem when dealing with multiples rows.

这是我的code：

$body = file_get_contents('php://input');
$jsonArray = json_decode($body, true);

$sql = array();
foreach ($jsonArray as $row) {   

    $sql[] = '("'.$row['firstname'].'", "'.$row['lastname'].'", "'.$row['sex'].'", "'.$row['dateOfBirth'].'", "'.$row['email'].'")';

}

$column_name = "(firstname, lastname, sex, dateOfBirth, email)";
$stringImplode = implode(',', $sql);

$action = $mysqli->query('INSERT INTO tbl_syncList '. $column_name .' VALUES '.$stringImplode .'ON DUPLICATE KEY UPDATE XXXXXXX'); // --> I get stuck here

我不知道我应该如何对重复密钥更新后写：我知道它应该像栏=值，column_name2 =值2 ......但也才有可能在某种程度上使用上述阵列像（column_name1，column_name2 ...）=（值1，值2 ...），可能是有用的，如果我是加数倍的属性？

I do not know how I should write after the ON DUPLICATE KEY UPDATE: I know it should be something like "column_name = value1, column_name2 = value2..." but could it be possible to use the arrays above in a way like "(column_name1, column_name2...) = (value1, value2...) that could be useful if I were to add multiples attributes?

由于这是位于循环外，我不知道如何引用我的价值的sql []数组里面，因为我可以有很多不同的价值为同一列名。

Since this is located outside the loop I am not sure how to refer to my value inside the sql[] array since I could have a lot of different value for the same column_name.

此外，我想在喜欢的东西WHERE timestamp_column_name＆LT; timestamp_value末尾添加一个WHERE子句，但我不知道这是否是可能的。

Moreover I would like to add a WHERE clause at the end with something like "WHERE timestamp_column_name < timestamp_value" but I don't know if it is possible..

如果有人可以帮助我解决这个问题将是巨大的。

If anyone could help me to solve this problem that would be great.

推荐答案

要回答你的第一个问题，你可以使用在对重复KEY UPDATE语句VALUES（）：

To answer your first question, you can use VALUES() in the ON DUPLICATE KEY UPDATE statement :

$action = $mysqli->query('INSERT INTO tbl_syncList '. $column_name .' VALUES '.$stringImplode .'ON DUPLICATE KEY UPDATE firstname = VALUES(firstname), lastname = VALUES(lastname), sex = VALUES(sex), dateofbirth = VALUES(dateofbirth), email = VALUES(email)');

不过，我不认为你的第二个问题，可以使用这种方法来解决。我的建议是首先记录插入其中并没有关于你插入/更新列中的任意唯一约束一个临时表。然后使用进一步查询（或preferably数据库程序）来执行单独的更新和插入基于表之间的连接查询，和你有什么其他的标准。

However, I don't think your second problem can be resolved using this approach. My recommendation would be to first insert all of the records into a staging table which doesn't have any unique constraints on the columns you are inserting/updating. Then use further queries (or preferably a database procedure) to perform separate update and insert queries based on a join between the tables, and whatever other criteria you have.

我的MySQL是一个有点生疏，但我认为这应该是你需要大致相当于一旦你插入的数据到临时表：

My MySQL is a bit rusty, but I think this should be roughly what you need once you have inserted the data into the staging table :

UPDATE a
SET a.firstname = b.firstname,
    a.lastname  = b.lastname,
    a.sex       = b.sex,
    ...
FROM stagingtable b
JOIN tbl_syncList a ON a.idcolumn = b.idcolumn
WHERE b.timestamp > a.timestamp

INSERT tbl_syncList(firstname, lastname, sex, dateOfBirth, email)
SELECT 
    b.firstname,
    b.lastname,
    b.sex,
    b.dateOfBirth,
    b.email
FROM stagingtable b
LEFT JOIN tbl_syncList a ON a.idcolumn = b.idcolumn
WHERE a.idcolumn IS NULL

这篇关于PHP - 用倍数行更新SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！