mysql Update Statement depending on number of checkbox checked

Question

Problem statement:

I have a form with multiple checkbox Fields, i have validated it so user can select maximum 9 checkbox and atleast 1 with jquery.
I collect the Form checked values using Post method.
i have mysql table with 12 columns.
first 3 columns are "id", "rollnum", "selectStatus"
Through session variables created during Login, i get roll number of student. So i can Run Update Query on particular row.
Question: How do i Update those 9 subject columns according to user checked inputs. Note : i stored those checked input field values in an array.

Code

<form action="index.php" id="form-3" method="post">
    <input class="form-check-input" name="year-3-checkbox[]" type="checkbox" value="ucs303">UCS303 Operating Systems
    <input class="form-check-input" name="year-3-checkbox[]" type="checkbox" value="ucs406">UCS406 Data Structures and Algorithms
    <input class="form-check-input" name="year-3-checkbox[]" type="checkbox" value="uec401">UEC401 Analog Communication Systems
    <input class="form-check-input" name="year-3-checkbox[]" type="checkbox" value="uec612">UEC612 Digital System Design
    <input class="form-check-input" name="year-3-checkbox[]" type="checkbox" value="uec307">UEC307 Electromagnetic Field Theory & Trans Lines
    <input class="form-check-input" name="year-3-checkbox[]" type="checkbox" value="uec502">UEC502 Digital Signal Processing
    <input class="form-check-input" name="year-3-checkbox[]" type="checkbox" value="uec510">UEC510 Computer Architecture
    <button type="submit" name="year-3-submit">Submit Selection</button>
</form>
<?php
    if(isset($_POST['year-3-submit'])){
        if(!empty($_POST['year-3-checkbox'])){
            $subjectCheckList = array();
            $subjectCheckList = $_POST['year-3-checkbox'];
        }
    }
?>

It depends on user how many checkbox is selected.
I donot know how to write UPDATE sql query which updates values of number of columns == size of array.

for example: User 1 has selected 3 checkbox and submitted form, we have array of size 3 and UPDATE 3 columns of table. User 1 has selected 6 checkbox and submitted form, we have array of size 6 and UPDATE 6 columns of table.

I donot want to write 9 switch case statements for all possible sizes of array. Any idea? please?

simply loop over the array and prepare your update query using the loop by concatenating the sql string — Madhur Bhaiya, Sep 02 '18 at 07:14
@MadhurBhaiya , it would be of much great help if you talk with sample code/snippet. — krupesh Anadkat, Sep 02 '18 at 07:15
As an example: `$sql = "Update table SET "; foreach ($post_values as $post) { $sql .= $post['field'] . " = " . $post['value']; }` — Madhur Bhaiya, Sep 02 '18 at 07:16
Also, give your query which you have created for one of the case(s), I will modify it to make it generic — Madhur Bhaiya, Sep 02 '18 at 07:17
@MadhurBhaiya i will write here in a minute, post it as answer and i will mark it — krupesh Anadkat, Sep 02 '18 at 07:18
//Assuming user has Checked 9 feilds $sql = "UPDATE subjectmaster SET substatus=1, sub1=$subjectCheckList[0],sub2=$subjectCheckList[1],sub3=$subjectCheckList[2],sub4=$subjectCheckList[3],sub5=$subjectCheckList[4],sub6=$subjectCheckList[5],sub7=$subjectCheckList[6],sub8=$subjectCheckList[7],sub9=$subjectCheckList[8] WHERE rollno= '$rollnumber'"; — krupesh Anadkat, Sep 02 '18 at 07:22
i donot know how to put code in comments, please bear with raw text code — krupesh Anadkat, Sep 02 '18 at 07:23
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/179265/discussion-between-madhur-bhaiya-and-krupesh-anadkat). — Madhur Bhaiya, Sep 02 '18 at 07:43

Madhur Bhaiya · Accepted Answer · 2018-09-02T07:46:06.433

Based on OP's comments, you can make the code generic as follows:

// Check if atleast one subject has been selected
$selectedSubjects = array_filter($subjectCheckList);

// If no subject selected
if (empty($selectedSubjects)) {
    $sql = "UPDATE subjectmaster 
            SET substatus = 0 
            WHERE rollno = '" . mysqli_real_escape_string($rollnumber) . "'";
} else {
    // Initialize the sql string
    $sql = "UPDATE subjectmaster 
            SET substatus = 1 ";

   $i = 1;
   foreach ($subjectCheckList as $subject) {
       $sql .= ", sub" . $i . " = '" . mysqli_real_escape_string($subject) . "' ";
   }

   $sql .= " WHERE rollno = '" . mysqli_real_escape_string($rollnumber) . "'";
}

Also, note the use of mysqli_real_escape_string. It helps in preventing SQL injection. For better ways to prevent SQL injection, you may check How can I prevent SQL injection in PHP?

Thanks for this clear answer. Please upvote my question, Someone downvotes it on purpose everytime i put a question in stackoverflow — krupesh Anadkat, Sep 02 '18 at 08:06

score 0 · Answer 2 · answered Sep 02 '18 at 07:42

0

Well, 1st of all it is not clear what should be the default valued for each column. Since your MySQL columns are set by numbers (sub1, sub2, etc) then your form should represent them accordingly, with the proper value. for example:

<input class="form-check-input" name="year-3-checkbox[]" type="checkbox" value="1">

This way, you can loop easily and update the table (I assume the sub columns are TINYINT(1) DEFAULT NULL) :

<?php
    if(isset($_POST['year-3-submit'])){
        if(!empty($_POST['year-3-checkbox'])){
            $subjectCheckList = array();
            $query = "UPDATE table SET ";
            foreach ($_POST['year-3-checkbox'] as $key => $value) {
              $query .= " sub" . $value . " = 1, " 
            }
            $query = substr($query, 0, -1);
        }
    }
?>

Hope this helps Guy

answered Sep 02 '18 at 07:42

Guy Louzon

1,175
9
19

i need the values as the subject code, it cant be changed under any circumstances – krupesh Anadkat Sep 02 '18 at 08:04
2

then you should store them in some sort of a keys table, something like: headers **subs, values** and values like __sub1,ucs303__ etc – Guy Louzon Sep 02 '18 at 08:05
1

I got the answer most suitable for me, thankyou for your help. – krupesh Anadkat Sep 02 '18 at 08:07
3

@krupeshAnadkat The accepted answer is good, but the de-normalised subN columns are still bad database design for entirely different reasons. Check out "normal form" for details. – OwlsSleeping Sep 02 '18 at 08:25

score 0 · Answer 3 · answered Sep 02 '18 at 08:23

You can also use array_filter, array_combine and array_slice.

<?php

$subs = [':sub1',
         ':sub2',
         ':sub3',
         ':sub4',
         ':sub5',
         ':sub6',
         ':sub7',
         ':sub8',
         ':sub9'
];

// use $dataFromForm = array_filter($_POST['year-3-checkbox'])

$dataFromForm = ['11111',
                 '222222',
                 '3333333'];

$dbh = new PDO('mysql:host=localhost;dbname=test', 'root', '*******');


$sql = 'UPDATE test SET sub1 = :sub1, sub2 = :sub2, sub3 = :sub3, sub4 = :sub4, sub5 = :sub5, sub6 = :sub6, sub7 = :sub7, sub8 = :sub8, sub9 = :sub9';
$sth = $dbh->prepare($sql);
$sth->execute(array_combine(array_slice($subs, 0, count($dataFromForm), $dataFromForm)));

mysql Update Statement depending on number of checkbox checked

3 Answers3