Array search, add and delete

Question

I have the following table:

CREATE TABLE sample (id INT AUTO_INCREMENT PRIMARY KEY, entry_id INT NOT NULL, last_change timestamp);
INSERT INTO sample (entry_id) VALUES (1), (2), (3), (4);

My code does the following:

Receives POST input and compares the values to the table above.
If the the POST data has a value that does not exist in the entry_id column, then it will perform an INSERT statement.
If the table has a value in the entry_id column that does not exist in the POST data, then it will perform a DELETE statement

PHP Code:

<?php
// $_POST data:
$submittedEntries = array(1, 3, 4, 5, 6);

// $dbORM->getEntries()
$currentEntries   = array(
    array("id" => 100, "entry_id" => 1, "timestamp" => "2014-07-24 2:14:00"),
    array("id" => 101, "entry_id" => 2, "timestamp" => "2014-07-24 2:14:00"),
    array("id" => 102, "entry_id" => 3, "timestamp" => "2014-07-24 2:14:00"),
    array("id" => 103, "entry_id" => 4, "timestamp" => "2014-07-24 2:14:00"),
)
$entriesToAdd     = array();
$entriesToRemove  = array();

// Find Entries to Add
// Loop through each entry submitted
foreach($submittedEntries as $entry_id) {
    $exists = false;

    // Loop through all Current Entries
    foreach($currentEntries as $existingEntry) {
        // Find matching IDs
        if ($existingEntry["entry_id"] == $entry_id) {
            $exists = true;
        }
    }

    if ($exists == false) {
        $entriesToAdd[] = $entry_id;
    }
}

// Find Entries to Remove
// Loop through all Current Entries
foreach($currentEntries as $existingEntry) {
    $remove = true;

    // Loop through each entry submitted
    foreach($submittedEntries as $entry_id) {
        // Find matching IDs
        if ($existingEntry["entry_id"] == $entry_id) {
            $remove = false;
        }
    }

    if ($remove == true) {
        $entriesToRemove[] = $existingEntry["entry_id"];
    }
}

// Add New Entries
foreach($entriesToAdd as $entry_id) {
    $dbORM->addEntry($entry_id);
}

// Remove Entries
foreach($entriesToRemove as $entry_id) {
    $dbORM->removeEntry($entry_id);
}

$entries = $dbORM->getEntries(); // SELECT * from sample;
print_r($entries);

/*
    Expected Output:

    array(
        array("id" => 100, "entry_id" => 1, "timestamp" => "2014-07-24 2:14:00"),
        array("id" => 102, "entry_id" => 3, "timestamp" => "2014-07-24 2:14:00"),
        array("id" => 103, "entry_id" => 4, "timestamp" => "2014-07-24 2:14:00"),
        array("id" => 104, "entry_id" => 5, "timestamp" => "2014-07-24 3:27:00"),
        array("id" => 105, "entry_id" => 6, "timestamp" => "2014-07-24 3:27:00")
    )
*/

Is there a better way to do this? I looked into all the php array functions and they do not seem to be able to deep search of multidimensional arrays.

It seems like a bad idea to just DELETE stuff that isn't in the post. It would be better to track which items you want to remove in your app then specify the ids you want removed. A misconfiguration in your form submission could drop all your data otherwise. — AeroX
– AeroX, Commented Jul 23, 2014 at 16:05
Unfortunately I'm in a position where I am unable to modify the front-end code that sends the POST data. — Matheos
– Matheos, Commented Jul 23, 2014 at 16:11

Salketer · Accepted Answer · 2014-07-23 16:14:34Z

2

If the table has to always be exactly like the POST, simplest way is to DELETE all rows and then INSERT all posts.

Another solution could be the use of unique key on entry_id... This way you could really simply do 2 small queries:

$insert = "INSERT IGNORE INTO sample (entry_id)VALUES(".implode('),(',$submittedEntries).")";
$delete = "DELETE FROM sample WHERE entry_id NOT IN(".implode(',',$submittedEntries).")";

edited Jul 23, 2014 at 16:14

answered Jul 23, 2014 at 16:02

Salketer

15.8k2 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matheos Over a year ago

Sorry, that is not an option as my table actually has many other columns that cannot be cleared when this update function is performed.

Salketer Over a year ago

Could we please have a bit more info on your table then? Because in your example, you simply select * from sample

Matheos Over a year ago

Ok, for example.. my table has a timestamp value: CREATE TABLE sample (id INT AUTO_INCREMENT PRIMARY KEY, entry_id INT NOT NULL, last_change timestamp);

AeroX · Accepted Answer · 2014-07-23 16:32:10Z

1

If you want to compair the two lists in PHP then you can use array_walk() to run through the db list and then use in_array() to see if it's in the submitted POST. You can then filter into two arrays.

<?php
$entriesToAdd     = array();
$entriesToRemove  = array();

$submittedEntries = array(1, 3, 4, 5, 6);
$currentEntries   = array(
    array("id" => 100, "entry_id" => 1),
    array("id" => 101, "entry_id" => 2),
    array("id" => 102, "entry_id" => 3),
    array("id" => 103, "entry_id" => 4),
);

function AWS(&$item, $key) {
    global $submittedEntries, $entriesToAdd, $entriesToRemove;

    if(in_array($item["entry_id"], $submittedEntries))
    {
        array_push($entriesToAdd, $item);
    }
    else
    {
        array_push($entriesToRemove, $item);
    }
}
array_walk( $currentEntries, 'AWS' );

echo "Add:";
print_r($entriesToAdd);
echo "Remove:";
print_r($entriesToRemove);

This outputs:

Add:Array
(
    [0] => Array
        (
            [id] => 100
            [entry_id] => 1
        )

    [1] => Array
        (
            [id] => 102
            [entry_id] => 3
        )

    [2] => Array
        (
            [id] => 103
            [entry_id] => 4
        )

)
Remove:Array
(
    [0] => Array
        (
            [id] => 101
            [entry_id] => 2
        )

)

answered Jul 23, 2014 at 16:32

AeroX

3,4712 gold badges28 silver badges40 bronze badges

1 Comment

AeroX Over a year ago

I know this isn't exactly what your looking for, but it's a possible way to approach assoc arrays

Collectives™ on Stack Overflow

Array search, add and delete

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related