Updating records through Matrix view

data-matrix.php

Main file showing the Matrix with checkboxes to update tables

data-matrixck.php

Backend PHP script to update data

config-mysqli.php

Database connection file with login details

sql_dump.txt

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; 
$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>";

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.

<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>

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.

<?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";
?>

Using CheckboxRadio JQuery UI
In place of checkboxes we can use checkboxradio JQuery UI Checkbox control to display colourful user interface to toggle the data.
Check the demo

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

This article is written by plus2net.com team.

plus2net.com

	12-07-2019
nice script...really help. Weldone

	09-11-2019
Hi, 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?