best practice for updating a list in database associated with checkboxes in a html form

Question

I have thought of two solutions to my problem and I would like to know all good viable solutions if you think there may be better.

I have a list in my MySQL database which is associated with a check-box in my html form. I want to add or delete the list items with the form and subsequently do the same in my database. I have an auto-increment ID column for the list. The problem is, how do I update the table as I perform my add and delete operations. The list must be ordered chronologically, which is usually done by the database automatically as we add new items to the table.

Solution 1: The check-boxes will have name attribute 'items[]' so when I can read them in as a $_POST array and delete the matching list item in my database. This of course requires that my ID column always be ordered continuously from first to the last, ie. 1, 2, 3... So I'll have to update the ID column every time I delete an item. The solution is suggested here: Reorder / reset auto increment primary key

Solution 2: I give the new item a checkbox value that equals the max ID+1, so that each time an item is entered, the ID is distinct. This way I don't have to update the ID column. I just need to find the largest ID in my table and add 1 to it for my checkbox value.

To summarize, the first way updates the list ID's each time an item is deleted, which seems to be a bigger hassle. The second way just gives a new distinct ID value and I'll just query the database for the largest ID at the time, which seems more efficient.

I'm open to other suggestions :) much obliged

What is the problem you aim to solve? I can't really think of an application like this one. YOu don't have to provide an id to a database, a database can assign one automatically. — Willem Van Onsem, Aug 20 '14 at 01:32
The problem is really simple. I have a list of texts that are associated with checkboxes in a form. I can add more text or delete existing items. Just wondering how to best achieve this. — TurtleTread, Aug 20 '14 at 01:41
Personally I would use AngularJS, and then handle the add, and delete functions on the server side through the API. AngularJS has a nice ng-repeat option which would allow you to populate your existing list, and remove things as the $scope changes. — Benji Vesterby, Aug 20 '14 at 01:49
You should provide more information on the goal of doing this because there are many options that do what you say but are very different: If you need this only for client side, use a javascript library. If you need this changes to persist in later visits and want to reorder the radios, db is okay but order can be done with a "order" or "rank" column. If you just want to remove and add radios without reordering them, just insert them (without id) and have the id as an autoincrement id. — JMerino, Aug 20 '14 at 02:01
I want these entries to persist. So the entire problem is still just a list of texts each attached with a checkbox, so I can select which one to delete. Using my first solution, if I delete item with ID=4,in a list of 10 items, the ID's then will miss a '4'. So my form will have only 9 items but the array index won't match these ID's which is the reason I have to update the ID's. In solution 2, I opt to just give a new value to the checkbox, so the script will match the value to the ID in the database, and not use the $_POST array index to read the value. I hope I clarified my question. — TurtleTread, Aug 20 '14 at 02:20

score 1 · Answer 1 · edited May 23 '17 at 12:13

This is a quick response and may not do everything the way you need, but hopefully there are some ideas you can use.

Here's a solution using one field, in one table.

Imagine a table named "scratchpad", with one field called "cb_list".

Store the checkboxes/IDs in the cb_list field as a JSON string:

{"cb1":1,"cb2":0,"cb3":0,"cb4":1}

When creating page, a PHP command reads the field and converts into an array:

$cb_json = mysql_result(mysql_query("SELECT `cb_list` FROM `scratchpad`"), 0);
$cb_arr = json_decode($cb_json);

Now, loop through that array and create HTML for the checkboxes:

$out = '<div id="chkboxDIV">';
foreach( $cb_arr AS $key =>$val ){
    $chkd = ($val==1) ? 'checked="checked"' : '';
    $out .= $key. " <input type='checkbox' id='" .$key. "' " .$chkd. " />";
}
$out .= "</div>";
echo $out;

To add a new checkbox, you can use jQuery (or js) code to append a new checkbox to the DIV:

var lbl = prompt('Label?');
$('#chkbox').append(lbl+ ': <input id="' +lbl+ '" type="checkbox" /> ');

When checkboxes are ready to be stored again, use this javascript to loop through the checkboxes and save their IDs and values:

var arrCB = {};
$("#chkbox>input[type='checkbox']").each(function(){ 
    var el = $(this);
    var id = el.attr('id');
    arrCB[id] = (this.checked ? 1 : 0)
});
var json_cb = JSON.stringify(arrCB);

Now, you can use AJAX to send the string -- similar to what you got from the database at the beginning -- back to PHP for storage:

$.ajax({
    type: 'POST',
     url: 'your_php_file.php',
    data: the_json=json_cb
});

On the PHP side, your AJAX processor file (in this example called your_php_file.php) will look something like this:

<?php
$j = $_POST['the_json'];
$result = mysql_query("UPDATE `scratchpad` SET `cb_list` = '$j' ");

Here is a jsFiddle with examples of the javascript/jQuery.

Resources:

You might find this helpful: $.ajax - dataType

best practice for updating a list in database associated with checkboxes in a html form

1 Answers1

Linked