Updating Records through a Data Matrix

A data matrix is useful when each row represents one record and each checkbox represents a Boolean field. The checkbox carries the record ID and allowed column name through data-* attributes.

Checkbox Markup Top ↑

<input type="checkbox"
       data-column-name="football"
       data-id="12">

Send the Changed Cell Top ↑

$( "#matrix-demo" ).on(
    "change",
    ".matrix-check",
    function () {
        const $checkbox = $( this );

        $.post(
            "data-matrixck.php",
            {
                column_name:
                    $checkbox.data(
                        "column-name"
                    ),
                id:
                    $checkbox.data( "id" )
            },
            null,
            "json"
        )
        .done(function ( response ) {
            $( "#matrix-message" ).text(
                response.message
            );
        })
        .fail(function () {
            // Restore the previous state when
            // the server update fails.
            $checkbox.prop(
                "checked",
                !$checkbox.prop( "checked" )
            );

            $( "#matrix-message" ).text(
                "The update failed."
            );
        });
    }
);

Safe Dynamic Column Update Top ↑

The column name cannot be bound as an SQL parameter, so validate it against a fixed allow-list before inserting it into the SQL statement. Bind the record ID separately.

$allowedColumns = [
    'football',
    'hockey',
    'badminton',
    'cricket',
    'tennis'
];

if (
    !in_array(
        $columnName,
        $allowedColumns,
        true
    )
) {
    http_response_code(400);
    exit;
}

$stmt = $connection->prepare(
    "UPDATE student_team
     SET $columnName = NOT $columnName
     WHERE id = ?"
);

$stmt->bind_param( 'i', $id );
$stmt->execute();

Related tutorials: checkboxes, POST, MySQLi UPDATE, and Boolean/TINYINT data.

Original grid-style demo →

Download original Data Matrix project

Add row/column selection limits →

Original Source Examples Retained for Migration Reference Top ↑

Original example 1

$q="SELECT id,name,
if( football=true,'checked','') as football,
if( hockey=true,'checked','') as hockey,
if( badminton=true,'checked','') as badminton,
if( cricket=true,'checked','') as cricket,
if( tennis=true,'checked','') as tennis
FROM student_team limit 0,15";

Original example 2

echo "<table class='table table-striped'> ";
$th="<tr class='info'> <th>Name</th><th>Football</th><th>Hockey</th><th>Badminton</th><th>Cricket</th><th>Tennis</th></tr>";
echo $th; 
$i=1;
if ($result_set = mysqli_query($connection,$q)) {
while($row = $result_set->fetch_array(MYSQLI_ASSOC)){
echo "<tr><td>$row[name]</td>
<td><input type=checkbox  data-column_name='football' data-id='$row[id]' $row[football]></td>
<td><input type=checkbox  data-column_name='hockey' data-id='$row[id]' $row[hockey]></td>
<td><input type=checkbox  data-column_name='badminton' data-id='$row[id]' $row[badminton]></td>
<td><input type=checkbox  data-column_name='cricket' data-id='$row[id]' $row[cricket]></td>
<td><input type=checkbox  data-column_name='tennis' data-id='$row[id]' $row[tennis]></td>
</tr>";
$i=$i+1;
if(fmod($i,10)==0){//echo $th;
}
}
 $result_set->close();
}

echo "</table>";

Original example 3

<script>
$(document).ready(function() {
////////////////////	
$('input[type="checkbox"]').change(function(){
var column_name=$(this).data('column_name');
var id=$(this).data('id');
$.post( "data-matrixck.php", {"column_name":$(this).data('column_name'),"id":$(this).data('id')},function(return_data,status){
$("#msg_display").html(return_data);
$("#msg_display").show();
setTimeout(function() { $("#msg_display").fadeOut('slow'); }, 5000);
});
});
//////////////////////////
});
</script>

Original example 4

<?Php
$column_name=$_POST['column_name'];
$id=$_POST['id'];
if(!ctype_alpha($column_name)){
echo " Data error ";
exit;
}
include "config-mysqli.php"; // database connection details stored here
$q=" UPDATE student_team SET $column_name = !$column_name WHERE id=? ";
$stmt = $connection->prepare($q);
if($stmt){
$stmt->bind_param('i',  $id);
$stmt->execute();	
$msg="Data Updated for : $column_name ";	
}else {
$msg="No Data Updated for : $column_name ";		
}
echo "$msg";
?>

Additional Original Learning Routes Top ↑

• ../php_tutorial/mysqli_connection.php
• ../php_tutorial/mysqli_fetch_array.php
• ../sql_tutorial/sql_if.php
• http-get.php
• load-record.php
• plus2_data_matrix3.zip
• record-edit.php

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: