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."')"
);
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
}
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.