how to prevent insertion of the existed data in SQL

Question

I'm been working for a codes to avoid data insertion that's already existed, but I left it undone because I have to work another important features of my system, now, when I want to code it back using the suggestions and answers from this site, I found it so stressful, because my SQL statement is too crowded, resulting fatal error, now I have to ask what should be added to my SQL line when I want to avoid duplication of the data named invoice. here's how I constructed my codes:

if($_POST["controller"] == "add") {
    // validations here
    if(empty($_POST["main_date"])) {
        echo 'error'; die();
    }else{
        // upload file
        $uniquesavename = time().uniqid(rand());
        $extension = pathinfo($_FILES['fileToUpload']['name'], PATHINFO_EXTENSION);
        $target_dir = "../files/laboratory/receipt/";
        $uploadOk = 1;

        $target_file = $target_dir . $uniquesavename . '.' . $extension;

        // check filesize
        if ($_FILES["fileToUpload"]["size"] > 1000000) {
            $uploadOk = 0;
        }

        if ($uploadOk == 0) {
            echo 'error in upload attachment 1'; die();
        // if everything is ok, try to upload file
        } else {
            if (!move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
                // move file failed
                echo 'error in upload attachment 2'; die();
            }
        }
    }

    $result = $_POST["employee"];
    $result_explode=explode('|', $result);
    $result = query("INSERT INTO tbl_maintenance 
        (employee_id,
        carid,
        account_id,
        main_date,
        invoice,
        up_date,
        previous,
        present,
        particular,
        code,
        amount,
        attachment,
        dateAdded) 
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)", 
        $result_explode[1],  
        $_POST["platenumber"], 
        $_POST["account_id"], 
        $_POST["main_date"], 
        $_POST["invoice"], 
        $_POST["up_date"], 
        $_POST["previous"], 
        $_POST["present"], 
        $_POST["particular"], 
        $_POST["code"], 
        $_POST["amount"], 
        $target_file,
        date("Y-m-d"));
    if($result === false) {
        echo 'mali imong code'; die();
    }
    else {
        $row = query("SELECT LAST_INSERT_ID() AS mId");
        $id = $row[0]["mId"];
        redirect("maintenance.php?profile=" . $id);
    }
}

Load your data into a staging table and then run code to insert only where the data from the staging table does not exist in the primary table. You can also dedupe on the staging table itself first to ensure no duplicates exist in the file itself. — Brad, Mar 14 '19 at 16:38
Well you either have to make the `invoice` column unique and then test for an error on insert that says you are duplicating it ( and work out what to do with all the other data at that point). Or query that database for the invoice number you are about to use before inserting the new row. (and work out what to do in that situation) — RiggsFolly, Mar 14 '19 at 16:41

Cid · Answer 1 · 2019-03-14T17:03:18.327

I would update the table and add an UNIQUE constraint to it :

ALTER TABLE tbl_maintenance ADD CONSTRAINT invoice_unique UNIQUE (invoice);

When you'll try to insert a duplicated data, MySQL will return a unique constraint violation error. You'll have to check it manually with mysqli_error() or PDO::errorInfo() or whatever you are using to query your DB. (Hint : the error code is 1062).

If you prefer to update the duplicated row instead of doing nothing, you can use ON DUPLICATE KEY UPDATE such as :

INSERT INTO tbl_maintenance
(
    yourCols
)
VALUES
(
    yourValues
)
ON DUPLICATE KEY UPDATE
    col1 = val1,
    col2 = val2,
    ...

score 1 · Answer 2 · answered Mar 14 '19 at 16:45

1

You don't necessarily need a lot of PHP for that. The only thing you need is:

try {
    $result = query($yourLongQuery);
} catch (Exception $e) {
    if(mysqli_errno() === '1062') {
        echo "Duplicate data!";
    } else {
        echo "Something went wrong!";
    }        
}

To make this work, you have to define UNIQUES on your table. By default, the primary will always be unique, but you can add more UNIQUE with:

ALTER TABLE tableName ADD UNIQUE INDEX indexName (columnName);

If you try to insert a duplicate, it will be errorcode 1062. You can catch that case and react accordingly.

answered Mar 14 '19 at 16:45

Realitätsverlust

3,941
2
22
46

1

In the case of other errors I would rethrow the PDOException, `throw $e;` – ArtisticPhoenix Mar 14 '19 at 16:48
I just realized it's not PDO, ha. Same thing applies though as long as MySqli is setup to throw exceptions. `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` -> from this answer https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli – ArtisticPhoenix Mar 14 '19 at 16:51
This is also a nice way to do Insert Or Update in some cases. Such as when your importing data that may have duplicates, in my case we had an UID in the data we could use as a unique constant and a way to update without the PK. So what I did for that was a `try { insert }catch{ update }` which saved me a lookup in the database. there was a noticeable performance gain. – ArtisticPhoenix Mar 14 '19 at 16:55

how to prevent insertion of the existed data in SQL

2 Answers2