Insert and delete function foreach loop in PHP

General PHP Help
#1

This is the console data POST, I am trying to create a function where if array value does not exist in the database then it will insert, and If the database value not matched with the array value then the value which already existed in the database will be deleted.
I created the code, it deletes the already inserted value and regains inserts the value in the array.
I also read the other threads similar the post but all was not helpful.
Please help me, How I achieve the function.

Insert array value if not exist in DB
Delete the value if the Database Column Value not matched with the array value.

console sent post data -

state: 14506
district: 16564
pincode[]: 844101
pincode[]: 844102
pincode[]: 844103
pincode[]: 844111

PHP ARRAY

Array
(
    [state] => 14506
    [district] => 16564
    [pincode] => Array
        (
            [0] => 123
            [1] => 456
            [2] => 789
        )
)

PHP SQL

$sqlInsert = "INSERT INTO `table` (`alt_pincode`,`alt_district`,`alt_state`)VALUES (:pin,:district,:state)";
$stmtInsert = $con->prepare($sqlInsert);

foreach ($_POST['pincode'] as $key => $binded_pinvalue) {
    if (!empty($binded_pinvalue)) {
        $stmt = $con->prepare("SELECT * FROM `table` WHERE `state` = :state AND `district` = :district AND `pin` = :pincode");
        $stmt->execute([
            ':district' => $district,
            ':state' => $state,
            ':pin' => $binded_pinvalue,
        ]);
        if ($stmt->rowCount() > 0) {
            $stmt = $con->prepare("DELETE FROM `table` WHERE `state` = :state AND `district` = :district AND `pin` = :pincode");
            $stmt->execute([
                ':district' => $district,
                ':state' => $state,
                ':pin' => $binded_pinvalue,
            ]);
        } else {
            //INSERT FUNCTION
            $stmtInsert->bindParam(':pin', $binded_pinvalue, PDO::PARAM_STR);
            $stmtInsert->bindParam(':district', $district, PDO::PARAM_STR);
            $stmtInsert->bindParam(':state', $state, PDO::PARAM_STR);
            $stmtInsert->execute();
        }
    }
}

also used this code but repeating - Inserts

foreach ($_POST['pincode'] as $key => $binded_pinvalue) {
   $stmt = $con->prepare("DELETE FROM `table` WHERE `state` = :state AND `district` = :district AND `pin` = :pincode");
   $stmt->execute([
     ':district' => $district,
     ':state' => $state,
     ':pin' => $binded_pinvalue,
   ]);
}

foreach ($_POST['pincode'] as $key => $binded_pinvalue) {
  //INSERT FUNCTION
  $stmtInsert->bindParam(':pin', $binded_pinvalue, PDO::PARAM_STR);
  $stmtInsert->bindParam(':district', $district, PDO::PARAM_STR);
  $stmtInsert->bindParam(':state', $state, PDO::PARAM_STR);
  $stmtInsert->execute();
}

Please help me, it inserting again and again and deletes exists value. How I make it correct

#2

Your existing DELETE operation is deleting row(s) if they ARE already in the table. What you want to do is delete any row(s) that are not in the set of submitted pin values.

Next, there’s no need to SELECT data first. For the DELETE operation, just delete any rows that are not in the set of submitted pin values. For the INSERT operation, just INSERT the data IGNORING existing values.

For this to work, the state, district, and pin columns need to be defined as a unique composite index. You should already have this definition in place so that the database will enforce uniqueness, prevent duplicates.

See the following example code -

// delete any existing rows that are not in the set of submitted pincodes
$sql = "DELETE FROM `table` WHERE `state` = ? AND `district` = ? AND NOT FIND_IN_SET(`pin`,?)";
$stmt = $con->prepare($sql);
$stmt->execute([ $state, $district, implode(',',$_POST['pincode']) ]);

// insert new data, ignoring any existing values
// note: the IGNORE keyword in the sql statement
// for this to work, the state, district, pin columns need to be defined as a unique composite index
$sql = "INSERT IGNORE INTO `table` (`state`, `district`, `pin`) VALUES (?,?,?)";
$stmt = $con->prepare($sql);
foreach($_POST['pincode'] as $pin)
{
	$stmt->execute([ $state, $district, $pin ]);
}
#3

it is inserting the value again and again even it already exists…

#4

Did you do ALL the things that someone took the time to write -

#6

sorry, but answer was not helpful.

Sponsor our Newsletter | Privacy Policy | Terms of Service