Deleting Database Entries Not in Array

Question

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);   
    }
}

RumpRanger · Accepted Answer · 2012-03-09 18:30:54Z

1

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)."";
}

answered Mar 9, 2012 at 18:30

RumpRanger

6544 silver badges9 bronze badges

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

Comments

user973254 · Accepted Answer · 2012-03-09 18:31:20Z

0

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) . ")";

answered Mar 9, 2012 at 18:31

user973254

Comments

Marcus Adams · Accepted Answer · 2012-03-09 19:46:51Z

0

For the MySQL query, you can use NOT IN:

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

edited Mar 9, 2012 at 19:46

answered Mar 9, 2012 at 18:29

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

Collectives™ on Stack Overflow

Deleting Database Entries Not in Array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related