CODEWITHSUNDEEP
phpmysqlsql-delete
drummer392
drummer392

Reputation: 473

Deleting Database Entries Not in Array

I have an array that grabs checkbox data and posts certain information into the database if the checkbox data isn't a copy of something already in the database. What I would like to know is how can I create a code that scans through the database and finds data that wasn't part of my checkbox data and delete it from the database.

Okay, for example let's say I have values 1,2,3,4 in my database, but in my checkboxes I only get back 1,2,4. I would like a code that scans my database and deletes that value(s) (in this case 3) from the database.

Here is my current code:

foreach($_POST['publish'] as $index => $val){
$matches = mysql_query("SELECT * FROM `$blog_table` WHERE `postID` = '$val");
    if (mysql_num_rows($matches) > 0)
    {
        // do nothing
    } else {
    $query3 = "insert into `$blog_table`
                     (`postID`)values 
                     ('$val')";
    mysql_query($query3);   
    }
}

Upvotes: 0

Views: 1534

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53870

For the MySQL query, you can use NOT IN:

DELETE FROM tablename
WHERE col1 NOT IN (1, 2, 4)

Upvotes: 0

user973254
user973254

Reputation:

you should use query like this:

delete from table where id NOT in (3)

in php like:

$query = "DELETE FROM `$blog_table` WHERE `postID` NOT IN (" . implode(',', $array) . ")";

Upvotes: 0

RumpRanger
RumpRanger

Reputation: 654

Here would be the code I would use with escaped input

if (!empty($_POST['publish']))
{
    $inserts = array();
    $deletes = array();
    foreach ($_POST['publish'] as $val)
    {
        $deletes[] = intval($val);
        $inserts[] = '('.intval($val).')';
    }
    $delete = "DELETE FROM `".$blog_table."` WHERE postID NOT IN (".implode(',',$deletes).");";
    $insert = "INSERT INTO `".$blog_table."` (`postID`) VALUES ".implode(',',$inserts)."";
}

Upvotes: 1

Related Questions