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屋！