Insert data into database if value doesn't exists in specific column

Question

I've tried to follow several answers on this question but can't seem to get it to work for my specific problem.

I want to insert data but only if the flight_number doesn't exists already. How can I do that?

$sql = mysqli_query($con,
  "INSERT INTO space (`flight_number`, `mission_name`, `core_serial`, `payload_id`)
  VALUES ('".$flight_number."', '".$mission_name."', '".$core_serial."', '".$payload_id."')"
);

Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, May 15 '19 at 12:53
You could do a select mission_name FROM space where flight_number is equal given flight number, if u get row then don't insert otherwise insert — Masivuye Cokile, May 15 '19 at 12:53
@Rob Have a look at these examples using INSERT IGNORE: https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ — David, May 15 '19 at 12:55
If its unique, this query will fail. So all you need to do is check the error code is the one for trying to store 2 row with same unique value and you can ignore the error — RiggsFolly, May 15 '19 at 12:56
It does beg the question.... why is your app getting into a situation where it might even try and create the same Flight_Number more than once — RiggsFolly, May 15 '19 at 12:59
@RiggsFolly To answer your first point - `mysql_real_escape_string($unsafe_variable)` will avoid SQL injection? The second point, it's just some test data that I'm running over and over again where I don't want duplicates. It won't run like that on the real thing. — Rob, May 15 '19 at 13:02
To answer **your** first point which was actually in my original comment, (second link) but obviously needs re-iterating Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) — RiggsFolly, May 15 '19 at 13:04

score 1 · Accepted Answer · answered May 15 '19 at 13:05

Rob since you saying flight_number is a unique then you can use INSERT IGNORE

<?php


    $sql = "INSERT IGNORE INTO space (`flight_number`, `mission_name`, `core_serial`, `payload_id`) VALUES (?,?,?,?)";

    $stmt = $con->prepare($sql);
    $stmt->bind_param('isss',$flight_number,$mission_name,$core_serial,$payload_id);
    if($stmt->execute()){

        echo 'data inserted';
        // INSERT YOUR DATA
    }else{

        echo $con->error;
    }

?>

OR you could select any row from your database that equal to the provided flight number then if u getting results don't insert.

$sql = "SELECT mission_name WHERE flight_number = ? ";
    $stmt = $con->prepare($sql);
    $stmt->bind_param('i',$flight_number);
    if(mysqli_num_rows($stmt) === 0){


        // INSERT YOUR DATA
    }

denodster · Answer 2 · 2019-05-15T13:41:50.360

A unique index on flight number should do the trick.

CREATE UNIQUE INDEX flight_number_index
ON space (flight_number);

If you want to replace the existing row with the new one use the following:

$sql = mysqli_query($con,
  "REPLACE INTO space (`flight_number`, `mission_name`, `core_serial`, `payload_id`)
  VALUES ('".$flight_number."', '".$mission_name."', '".$core_serial."', '".$payload_id."')"
);

Make note that I just copied your code and changed INSERT to REPLACE to make it easy to understand. PLEASE PLEASE PLEASE do not use this code in production because it is vulnerable to injection.

If you don't want to replace the existing row, run an insert and check for errors. If there is an error related to the index, the row already exists.

Disclaimer: I haven't tested any of this code, so there may be typos.

Insert data into database if value doesn't exists in specific column

2 Answers2