checkbox inputs and DB search

Question

I'm tagging Movies and store this into a Database. a tag_id could be a car, train, boat and details could be color or specific types.

DB
movie_id | tag_id | tag_detailsid
2612     |   75   |   1
2612     |   10   |   3
2612     |   12   |   2

The tags are submitted via a form with checkboxes (all checkboxes checked are added to the db)

Now.. How do I keep track with checkboxes I uncheck..at a later stage So looking at the above example.. for movie_id 2612, I uncheck tag 12 with id 2 as details.

So $_POST holds only 75-1 and 10-3....12-2 should be deleted from dB.

So I thought.. I simply go through the dB with a while loop and compare the db value with the values I get from the checkboxes (via the $_Post method).. I count the values in the $_Post and it shows 2 (checkboxes checked). Database however holds 3 entries ($numberofrecords) for that specific Movie.

My script so far...

$sql_query = "Select tag_id, tag_details_id FROM movie_tags WHERE movie_id = '2612";                              
$report = MYSQL_QUERY($sql_query);  
$numberofrecords = mysql_num_rows ($report);

while ($report_row = mysql_fetch_array($report))
{  
$thistag_id = $report_row['tag_id'];
$tag_details_id = $report_row['tag_details_id'];

foreach($_POST as $checkbox_id => $value)
    {
    if ($thistag_id == $checkbox_id) // check if DB tag equals checkbox value
        {
            echo "checkbox value is checked equals value in DB";
    }
}
 }

How do I track the record I need to delete from the database?

Basically you have a form and you want to remove movies (from a database) whose checkboxes were unchecked on form submission? Is that it? — Yang, Jan 03 '16 at 14:59

score 0 · Answer 1 · answered Jan 03 '16 at 15:01

0

A simple solution would be to delete every rows based on your primary key such as movie_id in your case and do insert in loop for every checked checkboxes. That is:

Delete rows based on movie_id
Loop through POST data and do insert

answered Jan 03 '16 at 15:01

Sudhir Bastakoti

99,167
15
158
162

score 0 · Accepted Answer · edited May 23 '17 at 11:59

Sometimes the easiest solution is the way to go.

Unless you have a compelling reason NOT to, you can simply delete all of the tag records before saving, then insert only those that were checked:

sample code using OP's selected db driver

$sql = 'DELETE FROM movie_tags WHERE movie_id=2612';
mysql_query($sql);
foreach($_POST as $checkbox_id => $value) {
    // save your records here
    // To show you the code I'd need to see the HTML for the checkboxes.
}

NOTE: You should not be using mysql_. Use PDO / mysqli instead: Why shouldn't I use mysql_* functions in PHP?

checkbox inputs and DB search

2 Answers2