How to update records from a datatables table

Beginners - Learning PHP
#1

Hi

I have a HTML datatables that I load using inline PHP. One of the table fields is a dropdown, and is the only field that is changable.

The code I use is:

SELECTS:

$groupqry = $conn->query("SELECT group_id, groupname FROM classgroup WHERE class_id= " . $class) or die("groups query : " . mysqli_error($conn));

$g_query1 = $conn->query(“SELECT m.mem_id, AES_DECRYPT( m.firstname, '” . $token . “’) AS firstname, AES_DECRYPT(m.lastname, '” . $token . "’) AS lastname, m.gender, m.dob, c.group_id FROM classmember c INNER JOIN memberdetails m ON c.mem_id = m.mem_id WHERE c.class_id= " . $class) or die("Gymnast query: " . mysqli_error($conn));

The classgroup table consists of:

class_id
group_id
groupname

and each class can have between 1 and 6 groups

The classmember table consists of:

class_id
mem_id
group_id

The Table code:

            <table id = "table" class="table table-striped table-bordered dt-responsive nowrap table-sm"  width="100%">
                <thead class="bg-secondary text-white">
                    <tr>
                        <th>Name</th>
                        <th>Gender</th>
                        <th>Age</th>
                        <th>Group</th>
                    </tr>
                </thead>
                <tbody  class="bg-light text-dark">
                    <?php
                    while ($f_query = $g_query1->fetch_array()) {
                        echo '<tr>';
                        echo '<td>' . $f_query['firstname'] . ' ' . $f_query['lastname'] . '</td>';
                        echo '<td>' . $f_query['gender'] . '</td>';
                        echo '<td>' . getAge($f_query['dob']) . '</td>';
                        $grpid = $f_query['group_id'];
                        echo '<td><select name = "group">';
                        $optid = "0";
                        selectdCheck($optid,$grpid,"-----");
                        while ($grp = $groupqry->fetch_array()) {
                            $optid = $grp['group_id'];
                            selectdCheck($optid,$grpid,$grp['groupname']);
                        }
                        $groupqry->data_seek(0);
                        echo '</select></td>';
                        echo '</tr>';
                    }
                    ?>
                </tbody>
            </table>

The selectdCheck function code:

function selectdCheck($value1,$value2, $display)
{
if ($value1 == $value2)
{
$select = ‘selected=“selected”’;
} else
{
$select = ‘’;
}
echo ‘<option value ="’.$value1.’" '.$select. ‘">’ .$display. ‘’;> $grp[‘groupname’] . ‘’;
return;
}

Basically this all works - the correct data is selected and displayed, and I can change the group using the dropdown on each table row.

I was quite pleased how this worked. Then I realised I hadn’t worked out how to save back to the classmember table any changes made to members groups.

There will be no changes to the number of records, it is just that some records will have the group_id changed to match the option id.

I assume I will need to put the datatable into a form to allow a SUBMIT, but other than that I am lost.

Can anyone let me know how to do the update based on the above code?

Thanks

#2

The form will submit a <select …> menu choice for each member. The submitted data must identify the member id the data belongs to and what group_id was selected. Use an array name for the select name attribute, with the member id as the array index. You would then loop over the array of submitted data to get each key (member id) and value (group id.)

The following line - echo '<td><select name = "group">'; would become -

echo "<td><select name = 'group[{$f_query['mem_id']}]'>";

A laundry list of problems/suggestions -

  1. The option tag markup you have now is broken. There’s no closing /option tag.
  2. You can put php variables directly into double-quoted strings and eliminate all the extra quotes and concatenation dots.
  3. Don’t put external/unknown data directly into sql queries (the $class value.) Use a prepared query.
  4. Don’t use or die() logic for error handling. By unconditionally outputting errors, you are giving hackers important information when they intentionally trigger errors. Instead, use exceptions for errors and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database errors will get displayed or logged the same as php errors.)
  5. Don’t put database specific code in the html document. Instead, fetch the data from any query into an appropriately named php variable, then just use the variable in the html document. In addition to simplifying and making the html document general-purpose, this will eliminate the need to perform the data seek on the group data.
  6. Functions should not echo output. They should return the result they produce to the calling code to use however it needs to be used.
  7. To pre-select an option choice, you only need to output the selected keyword.
#3

I had completely overlooked the use of an array variable, even though elsewhere I had used them for manipulating checkboxes!.

Comments in the list noted and sorted.:

The $class variable was passed as a SESSION variable and was originally chosen by clicking on a list of available classes. Throughout the rest of the system, I have used prepared statements using “sanitised” variables whenever user-entered data is involved…

When it goes “live” the dies will be replaced with TRY…structures

Anyway it now works perfectly

Thanks for your input - much appreciated.

#4

Other than trimming data to detect if all white-space characters were entered, you should only validate that user submitted data meets the requirements of your application. You should not sanitize/alter the submitted data.

You shouldn’t find yourself altering code just because it gets moved somewhere. That’s wasting your time and leads to making mistakes.

With exceptions, the only time your code should catch and handle an exception is for ‘recoverable’ errors. The only database related errors that are recoverable are inserting/updating duplicate and out of range user submitted data. All other connection, query, prepare, and execute errors are nothing the visitor can do anything about or should even know specifically what actually occurred on the server.

Sponsor our Newsletter | Privacy Policy | Terms of Service