countableSet Rachel Aurand

Ajax/jQuery Autocomplete with PHP and MySQL

Auto-complete is plugin for jQuery that allows for a textbox to have that drop down suggestion effect like what Google is using currently.
First download the plugin for jquery to do all the auto-complete stuff, you can find that here. Also make sure in include the jquery library's too. Thus, your includes should look a little like this:

<link rel="stylesheet" type="text/css" href="jquery.autocomplete.css" />
<script type="text/JavaScript" src="jquery-1.4.2.js"></script>
<script type="text/JavaScript" src="jquery.autocomplete.js"></script>

Yup, you see correct there is a css file included there. You are welcome to edit that if you like. I didn't.

The auto-complete works for text form input objects. Make sure to also add an id to the input field because this is how javascript is going to identify them. Here's a sample of what I used:

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

I used a simple get method form action with id's of firstname and lastname for their fields.

The javascript call when a user starts typing in the text box form, it will dynamically call the $("").autocomplete() script. That code looks like this:

<script type="text/JavaScript">
$(document).ready(function()
{
    $("#firstname").autocomplete("autocomplete.php?arg=1");

    $("#lastname").autocomplete("autocomplete.php?arg=2");
});
</script>

Therefore, if you know javascript you can see that the "#firstname" in the id that I was referring to that the javascript will "watch" for dynamic changes.

Finally, the last step in this process is looking at "autocomplete.php". Here is the code:

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

if($_GET["arg"] == 1)
    $query = "SELECT * FROM Persons WHERE FirstName LIKE \"" . $_GET["q"] . "%\" LIMIT 10;";
else
    $query = "SELECT * FROM Persons WHERE LastName LIKE \"" . $_GET["q"] . "%\" LIMIT 10;";

$result = mysql_query($query);

$output_items = array();

while($row = mysql_fetch_array($result))
{
    $output_items[] = $row["FirstName"];
} // while

print(implode("\n", $output_items));

mysql_close($con);

?>

This is a simple connection to a MySQL database with the command "mysql_connect()". Make sure to check if you have a good connection, that's what the die line is for. I wrote my query in a variable then queried the database with "mysql_query()". I then stored the results of the query in an array so I can easily print them out. I suppose you could skip that step and just go to printing them out.
The key "trick" however is knowing where the string that was being typed in is located at. The auto-complete plugin store that string in a variable called q. To access that variable just use the call $_GET["q"]. There is also a limit that is passed, called $_GET["limit"]. I didn't use it in this example, but you could if you'd like.

There are also many more options to customize the ("").autocomplete() call. I haven't played with any of them. But here's a list of them here.

I hope this helps anyone.

Ajax/jQuery PHP MySQL Form

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>. 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 + '&box=1',
        });
    } // if
    // the checkbox was unchecked
    else
    {
        $.ajax({
            type: 'POST',
            url: 'click.php',
            data: 'user=' + id + '&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.