Handling checkboxes in a Bootstrap table

[RogerTE]

Building a system for a local training charity, based on an old Delphi system, that was itself based on an old Clipper (DOS) system. (Basically teaching myself PHP as I am doing it)

Within it there is a module for skill awards.

There is a MySQL table with the structure :

person_id (int)

award_id int)

skills (varchar in the format "0,1,0,0.1.0,0,1,0,0" where each 0 or 1 indicates whether skill has been acheived (1) or not (0). This is "exploded" into an array $skillarr)

date_passed (date) This is null unless a certain number of skills has been acheived.

Person_id and award_id together make up the primary key.

 

The data is displayed in a HTML/Bootstrap table with each row consisting of:

Name

up to 10 checkboxes indicating whether passed (checked) or not

Date passed (if passed) or empty cell

Save button to allow updating  of current row. (cannot use single save button for whole table)

Currently each row is loaded using a similar routine I used for an attendance register:

 

                                   echo '<tr>';
                                    echo '<td id="' . $memid . '">' . $name . '<input type="hidden" name="memberid[]" value="' . $memid . '" /></td>';
                                    //Now the checkboxes
                                    for ($x = 0; $x < 10; $x++) {
                                        $visible = $checked = $bgcol = $dis = '';
                                        $chkval = 0;
                                        if ($skillarr[$x] != "0") {       // array setup from exploded datatbale skill column
                                            $checked = 'checked = "checked"';  // show it as checked
                                            $chkval = 1;
                                            $dis = 'disabled';                 // but disable it as don't want changed
                                        }
                                        echo '<td align="center"><input type="hidden" name="chk[' . $memid . '][]" value = "' . $chkval . '"><input type="checkbox" onclick="this.previousSibling.value=1-this.previousSibling.value"  class="style3"  value = "' . $chkval . '" ' . $checked . '" "' . $visible . ' ></td>';
                                    }
                                    echo '<td align="center" > $passdate</td>';    // Passed date goes here or null if not passed
                                    $var = $memid."/".$awardid;
                                    echo '<td><center><a href = "???????????????????????????" class = "btn btn-warning">Save</a></td>';  //  <-----Not certain whether to use a PHP function on same page or pass to external PHP program
                                    echo '</tr>';

With the register system I mentioned earlier there is just a single save button that POSTed everyone 's data. However this will not work for this so have added save button at end of every row.

 However not sure best way to implement the save the data, as need to return to page after the save routine has updated table by imploding checkbox data, and updating/inserting the person/award record (including adding todays date if required number of skills has been passed.

What would be the best way to handle this, and would welcome suggestions as to how to improve it.

I have included a view as to what table looks like currently

 

[Barand]

Before you go any further you should nomalize those skills and not have a comma-separated list.

EG

+--------------+                                 +--------------+
| person       |                                 | skill        |
+--------------+                                 +--------------+
| person_id PK |--+                           +--| skill_id  PK |
| name         |  |                           |  | skill_name   |
| etc          |  |                           |  +--------------+
+--------------+  |                           |
                  |     +-----------------+   |
                  |     | person_skill    |   |
                  |     +-----------------+   |
                  +----<| person_id   PK  |   |
                        | skill_id    PK  |>--+
                        | date_achieved   |
                        +-----------------+

In the SQL tutorial in my signature there is a similar example application which uses pupils/subects rather than person/skills

[RogerTE]

Hi,

I know I should, but want to continue format that was being used in the Delphi system which uses the same MySQL tables, and, the user wants to be able for both systems to co-exist, at least for the next year,

+-----------+            +-----------------+         
|StudentTbl |            | StudentAwardTbl |
|-----------|            |-----------------|
| person_id |------------|  person_id      |
| forename  |   +--------|    award_id     |
| surname   |   |        |     skills      |
| (etc,)    |   |        |  date_passed    |
+-----------+   |        +-----------------+
                |
+-----------+   |         +--------------+
| AwardsTbl |   |         |AwardSkillsTbl|
|-----------|   |         |--------------|
| award_id  |---+---------|   award_id   |
|award_name |             |   skill_desc |
|passes_rqd |             |  display_ord |
|award_body |             +--------------+
+-----------+

There are other tables (e.g. a PersonSessionTbl which is also used but not shown here)

Basically there are several awards, each award has several skills, some of which follow a natural order, and, for some awards the student only needs to complete, say, 8 out of the 10 (passes_rqd column) and it is this that is used to determine "date_passed" .

Each HTML table will show all the students for a single session, and for a single award (AwardTbl)

The AwardSkillsTbl is used to build the headings for the table rows, in the order determined by display_ord. The skills "array" follows the same display order.

Hope this explains why not using completely normalised tables.

 

Edited November 30, 2021 by RogerTE

[gizmola]

Systems are often hamstrung from the get-go with bad database design decisions.  Since you are doing this for a charity (and I assume, donating your time?), I really don't see any reason to force yourself to build upon a poorly designed system.  If you're doing this in part to hone your skills, then there's even less reason to reinforce a bad design, by building something new against it.

There is a way to continue to use both systems, which is to create the tables you should have, then develop some bridge code that will keep them in sync.  

It's not simple, but it can be done, and then you won't be stuck with the mistakes of the past forever.  

As for updating in page, the answer is to use ajax. 

Have the button click submit values to an endpoint you develop that take the changes and modify the data as needed.

Modern ajax code should be written using ES6 and best practice techniques:

• Use Fetch  with promises (https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API/Using_Fetch)
• Pass data to/from your ajax endpoint in json format

I don't know what version of bootstrap you are using, but resist the temptation to utilize jquery for this stuff, even though jquery does have the $.ajax() call, which could be used.

As for your PHP endpoint, just think of it as the same script you might write if you had a standalone form that was submitting only the data you need to do this update.  You will want to read the data from POST, json_decode it, and from there the actual processing should be straightforward.

[gizmola]

Just looking at your design, the obvious missing piece is that you don't know when a particular award-skill is passed, so that's a benefit to the system, although maybe that isn't something you can manifest at this juncture.

Looks like your new tables would be:

StudentAwardSkill
-----------------
person_id
award_id
display_ord
date_skill (optional)

You need a unique index on (person_id, award_id, display_ord)

 

The scary thing about the current design, is that if anyone were to modify the display_ord value of any skills, it would basically change/corrupt the entire meaning of any stored Skills in the StudentAwardTbl.skills column.

You might be wondering how you can build the bridge I described:

1. Your new app should only be concerned with writing out the proper relational design - writing rows to StudentAwardSkill. 
2. Your checks = UPSERT  (typically use INSERT ignore.)  You attempt to insert a new row for every checkbox using person_id, award_id, display_ord.  Set date_skill to now() if you want to use that.
3. The main issue with this design is the handling of "unchecked boxes" .  An unchecked box means that you need to issue a delete against StudentAwardSkill, again for that person_id, award_id, display_ord combination.

You will need a table named something like AwardSkillSync

AwardSkillSync
---------------
awardskillsync_id
created_on (timestamp)
changeby (tinyint)
student_id
award_id
processed (tinyint, default 0)

When your endpoint runs, you create a row in AwardSkillSync, with changeby = 1.
StudentUpdateTrigger runs when StudentAwardTbl.skills is changed.  Creates an AwardSkillSync row with changeby = 2 row.

Now you are only left with writing a sync command line script.  That script should query for oldest rows with processed = 0.  It should SELECT for update, and depending on the changeby it will either look at the flags in StudentAwardSkill and conform the values in StudentAwardSkill to what is in that array OR query the values from StudentAwardSkill, and generate the string, updating it.  

If there are few users and few updates, most likely changes will be synchronized in either direction in what appears as near instantaneous fashion.  There will be extra queries needed when a change is made in the old delphi system, but that is the cost to being able to have this bridge with no changes to the existing delphi system.

The important thing to keep in mind for this to work, is that your sync routine should not update StudentAwardTbl.skllls if things are already correct.  Just mark the row as processed.  Although a bit less important, as no trigger is involved, would be any updates to StudentAwardSkill.  Once the sync worked for either update, you could put it in a cron that you could run every second.  You want to make sure that you use some type of semaphore that would prevent the job from running if another job was already in process.