Updating records through Matrix view

NameFootballHockeyBadmintonCricketTennis
John Deo
Max Ruin
Arnold
Krish Star
John Mike
Alex John
My John Rob
Asruid
Tes Qry
Big John
Ronald
Recky
Kty
Bigy
Tade Row
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>

Backed PHP script to update database

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

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


Visitors Rating
Your Rating




12-07-2019

nice script...really help. Weldone

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







Most Popular JQuery Scripts

1

Two dependant list boxes

2

Calendar with Date Selection

3

Data change by Slider

4

Show & Hide element
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer