I’ve been trying to develop an easy way to update a MySQL database using checkboxes. At first I wanted to only use php means of running update queries. However, I was able to update the database if the checkbox was checked but I could get the POST or GET command to take an unchecked value for processing. Thus, I was forced to use Java scripting to solve this problem of unchecked boxes. Please also note that this is the first time I’ve ever written php and javscript, so sorry if I didn’t really follow style and rules on inclusion and readability.

This gave me the perfect opportunity to learn Java script and jQuery which is “a fast and concise JavaScript Library that simplifies HTML document traversing, event handling, animating, and Ajax interactions for rapid web development. jQuery is designed to change the way that you write JavaScript.” The first step was to create the form to display information from my database. I have a GET query for getting peoples first name and last name, which is pretty simple:

<form action="test.php" method="get">
First Name: <input type="text" name="fname" />
Last Name: <input type="text" name="lname" />
<input type="submit" />
</form>

Then I display the information by checking if the fname (first name) or lname (last name) is set. If it is connect to the database, select the database you want to work with, and write the query into a variable. Then run the query to store it into a variable of some kind– I called it result– I also find the number of rows the query called to error check to make sure it returned something, if it didn’t it prints “Person not Found!”.

<?php

// checks if there was a query done
if(isset($_GET["fname"]) || isset($_GET["lname"]))
{
 // Datebase varibles
 $db_host = "localhost";
 $db_user = "username";
 $db_pass = "password";

 // Esstablish connect to MySQL database
 $con = mysql_connect( $db_host, $db_user, $db_pass);

 // Checking is there is a connection to the db if not print out error
 if(!$con)
   die('Could not connect: ' . mysql_error() );

 // Selecting the right db from the connection
 mysql_select_db("bucket_db", $con);

 // Setting up the query string
 $query = "SELECT * FROM Persons WHERE FirstName LIKE "" . $_GET["fname"] . "" AND LastName LIKE "" .$_GET["lname"] . "" ORDER BY LastName, FirstName";

 // Query the db and put the results in the results variable
 $result = mysql_query($query);
 // Find the number of rows in the query to see if anything was returned
 $num_rows = mysql_num_rows($result);
?>

I next step was to start printing out the results of the query. I just used a while loop to display the form information. Base on a column in the database– which is of type bool– I store a 1 if checked and a 0 if it wasn’t. However, for a checkbox form to display a check it needs to have CHECKED somewhere in the input field. Thus I use the simple expression:

(($row["checkIn"] == 1)?"checked":"")

The rest of it was printed into a table. I used the onClick event to check if a user clicked anything to call the Java Script function checkbox_click() and pass the parameters that I needed to run an update query on the database. This case I passed the person id number in the table, the column name, and this.checked which return a true if the event was a check and fale is it was an uncheck.

<?php
// If there were results from the query start printing them out
    if($num_rows > 0)
        while($row = mysql_fetch_array($result))
        {
	        echo "<td>" . $row['FirstName'] . " " . $row["LastName"] . "</td> n ";
	        echo "<td>";
	        echo "<input type="checkbox" name="checkin[]" id="checkbox" value="" . $row["id"] . "" " . (($row["checkIn"] == 1)?"checked":"") . " ";
	        echo "onClick="checkbox_click('" . $row["id"] . "','checkIn', this.checked);" /> n";
	        echo "</tr> n ";
        } // while
    else
	    echo "Person not found! <br />";
?>

So, without all the horrible styling I have the checkbox form that looks like this:

<input type="checkbox" name="checkin[]" id="checkbox" value="3" checked onClick="checkbox_click('3','checkIn', this.checked);" />

The Java Script that uses the onClick event to deal with the action uses the jQuery $.ajax() command to run a POST script in “click.php” on the fly. I also had to do a simple check to see which case to send it:

<script type="text/JavaScript" src="jquery-1.4.2.js"></script>
<script type="text/JavaScript">

function checkbox_click ( id, checkin, value )
{
    // checking if the checkbox was checked
    if(value == true)
    {
        $.ajax({
            type: 'POST',
            url: 'click.php',
            data: 'user=' + id + '&amp;box=1',          
        });
    } // if
    // the checkbox was unchecked
    else
    {
        $.ajax({
            type: 'POST',
            url: 'click.php',
            data: 'user=' + id + '&amp;box=0',        
        });
    } // else
}
</script>

The final part in the process is looking at “click.php”. It’s pretty simple, and basically does the same thing as the first time mysql was run. Here’s the snippet:

<?php
// Datebase varibles
$db_host = "localhost";
$db_user = "username";
$db_pass = "password";

// Esstablish connect to MySQL database
$con = mysql_connect( $db_host, $db_user, $db_pass);
if(!$con)
	die('Could not connect: ' . mysql_error() );

mysql_select_db("bucket_db", $con);

    $query = "UPDATE Persons SET checkIn=" . $_POST["box"] . " WHERE id=" . $_POST["user"] . ";";

mysql_query($query);

mysql_close($con);

?>

There you have it, a dynamically updating database based on a checkboxes, jQuery, Javascript, and php. I hope you find it helpful. Thanks.