Insert Data into SQL if doesn't exist before using PHP-CodePudding

Hello I am trying to insert new client data into my sql table based on if customers_id exists or not. If the customer_id exists, it should just ignore the client data. I tried with primary keys, INSERT IGNORE and even with replace. But somehow its not working or just duplicating the existing data. Could you please help to insert this data from JSON array to SQL based on if customers_id already exists or not.

This is my Base Code, Of-course this duplicates data and just inserts new data.

 $datas = json_decode($jsondata, true);

 foreach ($datas as $data)
 {
    $customers_id = $data['customers_id'];
    $last_name = $data['last_name'];
    $first_name = $data['first_name'];
    $email = $data['email'];
    $phone = $data['phone'];
    $vat = $data['vat'];
    $country = $data['country'];
    $date_of_birth = $data['date_of_birth'];
    $customers_code = $data['customers_code'];
    $customers_ref_ext = $data['customers_ref_ext'];


 $sql = "INSERT INTO clients(customers_id, last_name, first_name, email, phone, vat, country, date_of_birth,customers_code,customers_ref_ext)
    VALUES('$customers_id', '$last_name', '$first_name', '$email', '$phone', '$vat', '$country', '$date_of_birth', '$customers_code', '$customers_ref_ext')";

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

 }

CodePudding user response：

foreach ($datas as $data)
 {
    $customers_id = $data['customers_id'];
    $last_name = $data['last_name'];
    $first_name = $data['first_name'];
    $email = $data['email'];
    $phone = $data['phone'];
    $vat = $data['vat'];
    $country = $data['country'];
    $date_of_birth = $data['date_of_birth'];
    $customers_code = $data['customers_code'];
    $customers_ref_ext = $data['customers_ref_ext'];

$query = $sql = "select * from clients  where customers_id ='".$customers_id."'";
    $result = $conn->query($query);
 
if ($result->num_rows > 0)
        {
      
    echo " File Exists ! ";
        }
else {
    
    $sql = "INSERT INTO clients(customers_id, last_name, first_name, email, phone, vat, country, date_of_birth,customers_code,customers_ref_ext) VALUES('$customers_id', '$last_name', '$first_name', '$email', '$phone', 
 '$vat', '$country', '$date_of_birth', '$customers_code', '$customers_ref_ext')";

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

CodePudding user response：

just execute a select query, if it returning record then it means that the record you trying to insert is already exist in table.
$datas = json_decode($jsondata, true);

 foreach ($datas as $data)
 {
    $customers_id = $data['customers_id'];
    $last_name = $data['last_name'];
    $first_name = $data['first_name'];
    $email = $data['email'];
    $phone = $data['phone'];
    $vat = $data['vat'];
    $country = $data['country'];
    $date_of_birth = $data['date_of_birth'];
    $customers_code = $data['customers_code'];
    $customers_ref_ext = $data['customers_ref_ext'];
    $query = "SELECT * FROM clients where customers_id = ".$customers_id;
    $result = $conn->query($query);

   if (mysql_num_rows($result) == 0) { 
    {
      $sql = "INSERT INTO clients(customers_id, last_name, first_name, email, phone, vat, country, date_of_birth,customers_code,customers_ref_ext) VALUES('$customers_id', '$last_name', '$first_name', '$email', '$phone', 
 '$vat', '$country', '$date_of_birth', '$customers_code', '$customers_ref_ext')";

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