SQL防止重复插入 [英] SQL Prevent Duplicate INSERT
问题描述
我有一个脚本,我已经设置了一个CRON来通过JSON(cURL)从第三方服务器获取值
I have a script that I have setup a CRON for that is getting values from a 3rd party server via JSON (cURL)
现在每次cron运行时它将插入一个全新的记录。导致重复,并导致我手动删除了重复数据。
Right now every time the cron runs it will INSERT a completely new record. Causing duplicates, and resulting me in manually removing the dups.
我将如何防止重复,仅更新丢失的信息或与$不同的信息var值?
How would I go about preventing duplicates, and only update the information that is either missing, or different from the $var values?
我想做什么
$prep_stmt = "SELECT * FROM members WHERE record NOT LIKE record=? ";
$stmt = $mysqli->prepare($prep_stmt);
if ($stmt) {
$stmt->bind_param('s');
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows !== 1) {
if ($insert_stmt = $mysqli->prepare("
INSERT INTO members (
start_date
)
VALUES (?)"))
{
$insert_stmt->bind_param('s',$repStartDate);
if (! $insert_stmt->execute()) {header('Location: ../error.php?err=Registration failure: INSERT');}
}
}
}
推荐答案
我结束了编写另一个if语句,以检查传入数据中是否存在唯一值以及现有的db值是否存在,并将其留空以防止其导入重复项。我还编写了一个单独的文件来更新,其中值区分了我收到的(新)和数据库中的(旧)值,这实际上对我的应用程序非常有用。
I ended up writing another if statement to check if a unique value existed from incoming and the existing db value existed and leaving it blank to prevent it from importing duplicates. I also wrote a separate file to update where values differentiate between what I am receiving as (new) and what is in the database (old) which actually worked out great for my application.
这是遇到这个问题的其他人的答案:)
Here is my answer for anyone else that runs into this issue :)
$prep_stmt = "SELECT * FROM table WHERE column_keys=?";
$stmt = $mysqli->prepare($prep_stmt);
if ($stmt) {
$stmt->bind_param('s',$varvalues);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows == 1) {
if ($insert_stmt = $mysqli->prepare("")) {
$insert_stmt->bind_param('');
if (! $insert_stmt->execute()) {
echo 'shits broke'; }
}
}
else { if ($insert_stmt = $mysqli->prepare("
INSERT INTO table (column_keys)
VALUES (?)")) // you will need a ? per column seperate by a , (?,?,?...?)
{ $insert_stmt->bind_param('s',
$varvalues
); // you will also need to bind a 's' (string) 'i' for num, etc per $var value.
if (! $insert_stmt->execute()) { echo 'shits broke';} //lol
}
}
}
我偶然发现了一个简单的错误报告技巧,它帮助我清理了一些我忽略的东西。只需将其放在文件顶部或您要调试的上面即可。)
Also a simple error reporting trick I stumbled upon that helped me clean up a few things I overlooked. Just place it at the top of the file, or above you want to debug ;)
error_reporting(E_ALL);
这篇关于SQL防止重复插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!