Update data dengan checkbox php

Question

Name	Football	Hockey	Badminton	Cricket	Tennis
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

Table of Contents Show

Database table student_team
Query to collect records from table
JQuery part to manage the checkbox events
Back end PHP script to update database

Main file showing the Matrix with checkboxes to update tables

data-matrixck.php

Backend PHP script to update data

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

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