Updating records through Matrix view

John Deo
Max Ruin
Krish Star
John Mike
Alex John
My John Rob
Tes Qry
Big John
Tade Row
Main file showing the Matrix with checkboxes to update tables
Backend PHP script to update data
Database connection file with login details
MySQL dump file to create tables with sample data

Download Zip file at end of this tutorial.

Any change in status of checkboxes will update the database table. Here we are selecting students for different teams. By checking or unchecking the respective checkboxes in the grid we can update the matching field of the database table.

Checkbox in each row is represents one student ( with unique id ) and each column represents one type of game. By checking the checkbox ( making it True ) we select the student for the particular game.

This script uses PHP, MySQL database and JQuery

Database table student_team

Our table name is student_team. There is one record against each student and different types of games are arranged in columns ( field names ). All game name ( field name ) are binary field type, they store either True or False as data. MySQL keeps such binary data as Tinyint(1) and stores 1 for Ture and 0 for False. Table with sample data is stored in sql dump file and kept inside the zip file at the end of this tutorial.

Query to collect records from table

Our main file data-matrx.php file will collect records from the database table and display them. In our Query we will use SQL if condition so we will get output as checked when the field data is True and returns blank string when the data is equal to False. This way we can manage a checkbox based on the data present in the table.

For each student and against each game there is a checkbox. If the data in the table is True ( or 1 ) then the output should be equal to checked. Here is the query for that .
$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";
We will display checkboxes for each column will displaying the matrix. Here we have used MySQLi record display using fetch_array().
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; 
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>
if(fmod($i,10)==0){//echo $th;

echo "</table>";
This will display the grid as shown above.

JQuery part to manage the checkbox events

Each checkbox has one attribute data-id ( same as student id ) and column_name ( name of the game ). We will use change event of checkbox to pass these two data to our JQuery script and then to our backend script data-matrixck.php by using POST method.
$(document).ready(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){
setTimeout(function() { $("#msg_display").fadeOut('slow'); }, 5000);

Back end PHP script to update database

The back end PHP script will receive the data ( id and column name ) and update the matching record. Here we will updating the table with binary field, so we will just update by toggling the field value.
echo " Data error ";
include "config-mysqli.php"; // database connection details stored here
$q=" UPDATE student_team SET $column_name = !$column_name WHERE id=? ";
$stmt = $connection->prepare($q);
$stmt->bind_param('i',  $id);
$msg="Data Updated for : $column_name ";	
}else {
$msg="No Data Updated for : $column_name ";		
echo "$msg";

You can read more on updating records here.

You can add several more features to the script like , restricting number of students in a team and maximum number of games a student can participate etc …

How to Install and test
  • Download the zip file at the end of this page.
  • Inside you will find sql_dump.text file to create tables in the database.
  • Open config-mysqli.php and enter your MySQL database login details.
  • Go to data-matrix.php page and check records


* indicates required
Subscribe to plus2net



    nice script...really help. Weldone


    I am getting the following message "Sorry this Page or file Not Found https://www.plus2net.com/jquery/plus2_data_matrix3.zip"
    Was it ever available, would you help?


    Missed that file . Now it is added, please download.

    Post your comments , suggestion , error , requirements etc here .

    Most Popular JQuery Scripts


    Two dependant list boxes


    Calendar with Date Selection


    Data change by Slider


    Show & Hide element

    JQuery Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer