(MYSQL) How to insert a record only if it doesn't exist in a table

Question

Hi maybe this question has already been answered but nothing has been helpful.

I'm using Mysql and I want to insert some data in a table but i want to check for duplicated records before inserting .

I'm very confused about how to achieve this.

I need to check if "RFC==$Expediente AND Diagonal ==10 or Diagonal==20" and show something like "Record already Exist", or insert all the data if record doesn't exist

this is my script :

<?php
$Person_name=$_POST['Person_name'];
$Expediente=$_POST['Expediente'];
$Sexo=$_POST['Sexo'];
$Edad=$_POST['Edad'];
$Domicilio=$_POST['Domicilio'];
$numero=$_POST['Numero'];
$colonia=$_POST['Colonia'];
$Ciudad=$_POST['Ciudad'];
$Parentesco=$_POST['Parentesco'];
$ClinicaAds=$_POST['ClinicaAds'];
$Dependencia=$_POST['Dependencia'];
$Entidad=$_POST['Entidad'];
$Foraneo=isset($_POST['Foraneo']) ? 1 : 0;

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "issste";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "  INSERT INTO paciente (RFC, Nombre, Sexo, FechaNacimiento, Calle, NumeroDomiciliario, Colonia, Ciudad, Dependencia, ClinicaAdscrip, Entidad, Foraneo, Diagonal)  
                SELECT '$Expediente', '$Person_name', '$Sexo', '$Edad', '$Domicilio', '$numero', '$colonia', '$Ciudad', '$Dependencia', '$ClinicaAds', '$Entidad', '$Foraneo', '$Parentesco' FROM paciente  
                WHERE NOT EXIST(  
                 SELECT RFC, Diagonal FROM paciente WHERE RFC = '$Expediente' AND Diagonal='10' OR Diagonal='20') LIMIT 1 ";  

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

when i run my program i get this error

"you have an error in your sql syntax check the manual that corresponds to your mysql server version"

Possible duplicate of [How to 'insert if not exists' in MySQL?](https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — am05mhz, Oct 21 '17 at 03:32
Your title asks one thing but you finish asking another. What is your question? Re error message, please read & act on [mcve]. Start removing code until there's no error. Try your SQL at sqlfiddle.com or other site (google). Read the manual re the statements you are trying to use. Googel re the exact error message & number. — philipxy, Oct 21 '17 at 05:17

score 0 · Answer 1 · answered Oct 21 '17 at 03:37

Modified your query, try something like this:

INSERT INTO paciente (RFC, Nombre, Sexo, FechaNacimiento, Calle, NumeroDomiciliario,
            Colonia, Ciudad, Dependencia, ClinicaAdscrip, Entidad, Foraneo, Diagonal)  
  SELECT '$Expediente', '$Person_name', '$Sexo', '$Edad', '$Domicilio', '$numero',
         '$colonia', '$Ciudad', '$Dependencia', '$ClinicaAds', '$Entidad', '$Foraneo',
         '$Parentesco'
  WHERE NOT EXISTS( SELECT RFC, Diagonal FROM paciente
                    WHERE RFC = '$Expediente' AND Diagonal='10' OR Diagonal='20' LIMIT 1 )

(MYSQL) How to insert a record only if it doesn't exist in a table

1 Answers1