How to update a MySQL field in a table matrix

This is a tutorial on updating a unqiue mysql field in a matrix grid of elements by using PHP and jQuery.

In this tutorial, we are presuming you are familiar with PHP and MySQL.
This is the example we will use. The scenario is giving users access to special features on movies created by producers. This will be useful in the near future when producers have control of film distribution.

We start with the database design creating four tables.

  1. producers
  2. users
  3. Features
  4. feature_access

Step 1. Import schema

Copy paste the code or import the TEST.SQL file into phpMyAdmin (download file link at the bottom of tutorial).

The USERS and PRODUCERS tables are simplified to id and names. The FEATURES table has a foreign key to link the producer who made the feature. The FEATURE_ACCESS table contains 3 foreign keys, linking the 3 other tables. The additional field we are calling “access” is a BOOLEAN allowing NULL.
If you are wondering why were are allowing NULL values besides 0 and 1, it’s because we want the FEATURE_ACCESS records to exist regardless if they are active or not. More on this later.

Step 2. Create an empty file and name it features.php, this file will include HTML and PHP. Insert PHP code to connect to your database. I chose the method mysqli for simplicity of this tutorial.

features.php
js/ (for jQuery files or you can link directly to the CDN)

<?php
//Access DB Info
$host="localhost";
$user="";
$password="";
$dbname="";
$link=mysqli_connect($host, $user, $password, $dbname) or die ("Error connecting!");
$dbstatus = mysqli_select_db($link, $dbname);?>

The HTML doctype is not important, but we will use HTML5. Make sure to include the jQuery framework and UI subset either locally or from the CDN. CSS is just to outline the table, which is the main element to display the features and yes, the table is used semantically in the form of a data matrix.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title></title>
</head>
<style>
table { border: 1px solid #000; }
th { background-color: #CCC; }
td { border: 1px solid #CCC; }
</style>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.9.2/themes/base/jquery-ui.css" />
<script type="text/javascript" src="js/jquery-1.8.3.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.9.2.custom.min.js"></script>
<body>
<div id="content"><h1></h1>
<table>
 <tr class="table_header">
 <th>&nbsp;</th>
 </tr>
</table>
 </div>

</body>
</html>

Step 3. In this step we are preparing two SELECT queries and arrays. The FEATURES will be for the column headers. The second query inner joins USERS as the row headers and FEATURE_ACCESS as the inner cells.

+----------------+
| Tables_in_test |
+----------------+
| feature_access 
| features 
| producers  
| users 
+----------------+
+USERS--+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(32) | NO | MUL | NULL | |
+----------+------------------+------+-----+---------+----------------+
+PRODUCERS--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(32) | NO | MUL | NULL | |
+----------+------------------+------+-----+---------+----------------+
+FEATURES+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| producer_id | int(11) unsigned | NO | | NULL | |
| feature | varchar(32) | NO | MUL | NULL | |
+-------------+------------------+------+-----+---------+----------------+
+FEATURE_ACCESS---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| feature_id | int(10) unsigned | NO | | NULL | |
| producer_id | int(10) unsigned | NO | | NULL | |
| user_id | int(10) unsigned | NO | | NULL | |
| access | tinyint(1) | YES | | 1 | |
+-------------+------------------+------+-----+---------+----------------+

Let’s select the unique id and feature name where the PRODUCER_ID matches, because the table will represent features of one producer. For this example I will display the PRODUCER_ID in the cells for verification of data alignment in the table.

In the second query, we are selecting from two tables that’s why we are giving unique names followed by the “AS”. So the required filenames are:

-user.id, -username from USERS

then

-feature_access.id, feature_access.producer_id, feature_access.feature_id,
feature_access.access from FEATURE_ACCESS

$producerid = 1;//hard coded id
$sql_features = "SELECT id, producer_id, feature FROM features
 WHERE producer_id='$producerid' ORDER BY id ASC";

$sql_access = "SELECT users.id AS user_id,
 users.username AS username,
 feature_access.id AS fid, 
 feature_access.producer_id AS producer_id,
 feature_access.feature_id AS feature_id,
 feature_access.access AS access 
 FROM users
 INNER JOIN feature_access ON feature_access.user_id = users.id
 WHERE feature_access.producer_id='$producerid'
 ORDER BY feature_access.user_id, feature_id";

 $feature_array = array(); 
 $access_array = array();

The join statement matches user_id from both tables but only from the one selected producer. The ORDER is very important because it will make the table useless if not correct. This allows the record order inserted into the database be irrelevant.

Next is to create two arrays to hold the data to be rendered in the table cells.
$feature_array = array(); $access_array = array();



Step 4. Now to execute the queries and push the results in to the arrays.


$result = mysqli_query($link, $sql_features) or die(mysqli_error($link));
$feature_count = mysqli_num_rows($result);


This first $result captures the $sql_features query by the method mysqli_query() taking in two variables, the database link $link and query statement. The die() method allows an error to display if the connection fails. We get the feature count to determine how many columns to display using the method mysqli_num_rows().


while ($row = mysqli_fetch_assoc($result)) {
 array_push($feature_array, $row);
 }


$result = mysqli_query($link, $sql_access) or die(mysqli_error($link));
 $access_count = mysqli_num_rows($result);
 while ($row = mysqli_fetch_assoc($result)) {
 array_push($access_array, $row);
 }




Using a while loop we push the associative arrays from the $result resource into the $features_array. Then we do the execute the same two steps for the other query $sql_featuers. If you want to see your progress using PHP’s print_r() or var_dump() methods, I’ve included echo output in <pre> tags because they so you can see the arrays better.</pre>

Now let’s create a new empty variable to hold the boolean for the access field and a IF statement for reading the $_POST. I add the functions is_numeric() and isset() in the if statement as they are the two essential variable to UPDATE the database record for feature_access.


$new_access = ”;

if ( $_POST && is_numeric($_POST[‘fid’]) && isset($_POST[‘access’]) ) {


matrix jquery post


Step 5. Process the POST data.


You should be on line 53 now so we can filter the POST data. Inside the IF {} we cast the feature_id as an integer and assign the $new_access variable from the user input. This is another IF ELSE statement which accepts a string of “true” provided from the HTML checkbox and sets the variable to a boolean of 0 or 1 because that’s how it’s recorded in the database.


$fid = (int)$_POST['fid'];

 if (strtolower($_POST['access'])=='true') { 
 $new_access = 1;
 }
 else {
 $new_access = 0;
 }


$sql = "SELECT id, access FROM feature_access WHERE `id`=$fid LIMIT 1";
 $result = mysqli_query($link, $sql) or die(mysqli_error($link));
 $row = mysqli_fetch_assoc($result);


The next line (62) we prepare a SELECT query to see if the feature_access.id exists. Execute the query and if the id exists, you get a valid resource to match in the next IF ELSE statement. But first we will add an ELSE statement if the ID does not exist, a simple string assignment goes to the variable $status = “Tour Access ID does not exist.”. Back inside the nested IF, we will process the record update.


Prepare the UPDATE query matching the feature_access.id and execute it. The following iF ELSE runs mysqli_query() method or dies. And that’s all there is for the database work.


 /*if tour access id exists, then update it*/
 if ($row['id']==$fid) {
 //process sql update
 $sql = "UPDATE feature_access SET `access`=$new_access WHERE `id`=$fid";
 if (!mysqli_query($link, $sql))
 {
 die('Error: ' . mysqli_error($link));
 }
 else 
 {
 $status[] = "FID: ".$fid." updated to: ".$new_access;
 }
 }
 else {
 $status[] = "Tour Access ID does not exist.";


}


6. In In this step we render the table with PHP inside the HTML.


Let’s skip to line 108, where it should be in inside the table row

tag. This will be the first cell so let’s place the producer_id in it using echo.


<table>

<tr class=”table_header”>

<th><? echo $producerid;?></th>


For the next line use a for loop to create the y-axis headers showing the features names. Count the loop iteration using the variable $feature_count we created earlier and echo table headers tags <th>, then the name echoing $feature_array using the counter variable $i for the multi-dimensional array. For the verification purposes, I am also echoing the feature_id. Now close the tag and i’ve added \n for reading the code easier in “source view”.


<?php
//list features
for ($i = 0; $i < $feature_count; $i++) {
echo "<th align='center'>";
echo $feature_array[$i]['feature']."-".$feature_array[$i]['id'];
echo "</th>\n";
} ?>
</tr> 
<?php
// show access matrix
for ($j = 0; $j < $access_count; $j++) {

if ($j % $feature_count == 0) {
echo "<tr>\n";
echo "<td>".$access_array[$j]['username']."-".$access_array[$j]['user_id']."</td>\n";
}
echo "<td align='center'>".$access_array[$j]['name'];
echo "<input class='flipswitch' type='checkbox' name='".$j."' value='".$access_array[$j]['fid']."' "; 
echo ($access_array[$j]['access']==1)?'checked="checked"':''; 
echo "/>".$access_array[$j]['user_id']."</td>\n";
}


?>
</tr>
</table>


Close the for loop and create another one to show the user name with their unique feature access ids. Duplicate the loop, except use $access_count for number of iterations. The next line (121) which is an IF statement, is important because it determines when to create a new row. Within the next two lines you echo out table row <tr> and user names inside


tags. I’ve also included the user_id for visual verification.


This IF statement executes if the $feature_count divides the loop’s iteration into a zero. Line 125, we echo opening <td> tag followed by and <input> checkbox tag containing the feature_access.id value [fid] and if it is checked or not. Then we close the </input> tag and </td>.


This should out put your entire table followed by closing the final row and table tag.  You can confirm the data displayed by opening the feature.php file on your testing server.


Step 7.  For the final step to process the change we use jQuery to do an ajax call


Going to line 101, write the default jQuery document function to execute when page is loaded.  Then write a custom function binding a “change” event to all input tags with the class “.flipswitch”.


$(document).ready(function() {
 $(".flipswitch").change(function () {
 var flip = $(this).closest('td');
 //alert("fid="+this.value+"&access=" + this.checked);
 $.ajax({
 type: 'post',
 url: '<?php echo $_SERVER['PHP_SELF']; ?>',
 data: "fid="+ this.value + "&access=" + this.checked,
 success: function() {
 flip.effect("highlight", {color:"#F00"}, 2000)
 }
 });
 });
});


Inside this function we set a javascript variable to hold the parent tag <td> which will use for visual confirmation as a highlight.   You can uncomment the alert() line to confirm everything is working prior to the ajax all.  So the ajax method sends the “id” and “true” if checked strings to the same PHP page and hits the IF $_POST clause.


It the ajax call is a success, it will highlight the table cell for you by a fading out the color red.


After your first checkbox click, refresh the page to see if the change remain so you know the UPDATE occurred in the database.


DOWNLOAD TUTORIAL FILES


Have any questions or comments to improve this tutorial?  Comment below!