CODEWITHSUNDEEP
phpmysqlsqldatabase
Amjad
Amjad

Reputation: 2090

Updating database according to check-boxes checked

I've the following table layout in my database with some data. Table Layout in database

I'm taking input by check-boxes so user can select all applicable accident road conditions and it to database. I would say it is okay if you're adding a new record you just loop through the checkboexs checked and insert them in DB

First screen user checked some road conditions

The first information is now saved in the database, now user decided to change the road conditions for any reasons user came back and change it the to the following.

User change the checkboexs values

Now my question, how should I update my table. The first thing that came into my mind was to delete the record that were already there and insert the new one's.

My real issue here is, assume user have choose the 3 items before but changed it two or one then how would i delete the those are not checked you know what I'm saying. Below is some code snippets that I've been trying.

$accidentRoadConditions = AccidentRoadConditions::findAccidentRoadConditions($acc_det_id);
$wc_array = [];

while ($roadConditions = $accidentRoadConditions ->fetch(PDO::FETCH_OBJ)) {
    $wc_array[] = $roadConditions ->rc_id;
}

Above I'm selecting all the road conditions that is already stored in the database.

if (isset($_POST['rta_ad_rc'])) {
    foreach ($_POST['rta_ad_rc'] as $rc_id) {
        //AccidentRoadConditions::save(array(null, $ad_lsid, $rc_id));
        // $tmprory = AccidentRoadConditions::findByADAndRCIds($acc_det_id, $rc_id);
        // if(!$tmprory){
        //  AccidentRoadConditions::save(array(null, $acc_det_id, $rc_id));
        // }
        if(in_array($rc_id, $wc_array)){
            $errors[] =  "in array <br />";
            unset($wc_array[0]);
        }
    }
}

So my question is how to update values in database according to what was checked by user and deleting those which were unchecked which were checked before. Getting bit complicated so simply how to update database according to above mention scenario.

Any Idea?

Upvotes: 2

Views: 81

Answers (2)

Sharif
Sharif

Reputation: 728

I think you need to do the following

  • Store the selected checks in an array
  • Check in the database if any of those are already saved or not

  • if yes, skipped them otherwise add them into an array

    $old_rc_array = []; $new_rc_array = [];

    while ($roadConditions = $accidentRoadConditions->fetch(PDO::FETCH_OBJ)) { $old_rc_array[] = $roadConditions->rc_id; }

    if (isset($_POST['rta_ad_rc'])) {

    foreach ($_POST['rta_ad_rc'] as $rc_id) {

    if(in_array($rc_id, $old_rc_array)){
    unset($old_rc_array[array_search($rc_id, $old_rc_array)]);
}else{
    $new_rc_array[] = $rc_id;
}

    } }

    foreach ($old_rc_array as $rc_to_delete) { AccidentRoadConditions::deleteByADIdAndRCId($hidden_acc_det_id, $rc_to_delete); }

    foreach ($new_rc_array as $rc_to_insert) { AccidentRoadConditions::save(array(null, $hidden_acc_det_id, $rc_to_insert)); }

Upvotes: 1

bitkot
bitkot

Reputation: 4504

I think this is what you should do.

  • Create composite unique constraint on ad_id and rc_id
  • Delete all the rows not in the selected checkbox ids.
  • Try to insert all the rows but user INSERT IGNORE. This will insert the record if it does not exist or it will just ignore it. As you are using some framework see how you can do that. If you can not then just wrap it using try/catch and ignore if the error is related to constraint violation.

This way You don't need to check if the values exist and also there will not be any unnecessary inserts.

Upvotes: 1

Related Questions