Home > Lab > Autocomplete field with jQuery and PHP

Autocomplete field with jQuery and PHP

For the recipe manager project I need to allow the entering of ingredients that the system does not know about. I decided to use an auto complete field to allow users to easily see what was already in the system. If the ingredient does not exist then the one they entered will added to the system.

To test this out I wrote a quick little program that assigns employees to an assigned employees table. The autocomplete does a look up in the employees table. If the entered employee is not found then the new employee is added to the employees table and then assigned.

I used the following jQuery stuff:

jQuery1.2.6
Autocomplete – jQuery plugin 1.0.2 (copyright (c) 2007 Dylan Verheul, Dan G. Switzer, Anjesh Tuladhar, Jörn Zaefferer)

Main interface: index.php

<?php
 
	include('config.php');
	$employees = array();
	$assigned_employees = array();
 
 
	$query = "select * from employees";
	$result = mysql_query($query) or die('Query failed: ' . mysql_error());
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
	{
		$employees[$line['id']] = $line['first_name'];
	}	
 
	$query = "select * from assigned_employees";
	$result = mysql_query($query) or die('Query failed: ' . mysql_error());
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
	{
		$assigned_employees[$line['id']] = $line['first_name'];
	}
 
?>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
                    "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<script src="../javascript/jquery-latest.js"></script>
 	<link rel="stylesheet" href="../javascript/jquery.autocomplete.css" type="text/css" />
	<script type="text/javascript" src="../javascript/jquery.autocomplete.js"></script>
	<script>
		$(document).ready(function()
		{
			// var data = "Core Selectors Attributes Traversing Manipulation CSS Events Effects Ajax Utilities".split(" ");
			$("#first_name").autocomplete('autocomplete.php');
 
			$("#first_name").result(function() 
			{
				console.log(arguments);
			}
			);
		}
		);
  </script>
 
</head>
<body>
	<form action="insert.php" method="post">
		Assigned an employee: <input id="first_name" name="first_name" />
		<input type="submit" value="Submit" />
	</form>
 
	<h3>Employees</h3>
<?php
	foreach($employees as $employee)
	{
		echo "$employee <br>";
	}
?>	
 
<h3>Assigned Employees</h3>
<?php
	foreach($assigned_employees as $employee)
	{
		echo "$employee <br>";
	}
?>
 
<div>--------------------------------------------</div>
<a href="clear.php">Remove all assignments</a>
 
</body>
</html>

Auto Complete Function: autocomplete.php

<?php
include('config.php');
 
$q = $_GET['q'];
 
// Performing SQL query
$query = "SELECT id, first_name FROM employees";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
 
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
	if (substr($line['first_name'], 0, strlen($q)) == $q) 
	{
		echo $line['first_name'] .'|'. $line['id'] . "\n";
	}
}
 
?>

Insert Function: insert.php

<?php
include('config.php');
 
//get the first_name from the form
$first_name = $_POST['first_name'];
 
// see if we have it in the database. If so then grab the ID
$query = "SELECT id FROM employees WHERE first_name = '$first_name'";
 
//print "$query<br>";
 
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
if(!$row = mysql_fetch_assoc($result))
{
	// If not then insert it into the employees  table. Then get the ID of the new employee from the employees table.
	$query = "INSERT INTO employees(first_name) VALUES('$first_name')";
	//print "$query<br>";
	$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
	$id = mysql_insert_id();
}
else
{
	$id = $row['id'];
}
 
// Now insert into the ingredient list table.
 
$query = "INSERT INTO assigned_employees(id, first_name) VALUES($id, '$first_name')";
//print "$query<br>";
 
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
header( 'Location: http://localhost/lab/autocomplete/index.php' ) ;
 
?>

Clear Function: clear.php

<?php
include('config.php');
 
$query = "delete FROM assigned_employees";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
header( 'Location: http://localhost/lab/autocomplete/index.php' );
?>

Database Schema

CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `test`;
 
/*Table structure for table `assigned_employees` */
 
DROP TABLE IF EXISTS `assigned_employees`;
 
CREATE TABLE `assigned_employees` (
  `id` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
 
/*Data for the table `assigned_employees` */
 
INSERT  INTO `assigned_employees`(`id`,`first_name`) VALUES (0000000002,'sam');
 
/*Table structure for table `employees` */
 
DROP TABLE IF EXISTS `employees`;
 
CREATE TABLE `employees` (
  `id` INT(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
 
/*Data for the table `employees` */
 
INSERT  INTO `employees`(`id`,`first_name`) VALUES (0000000001,'steve'),(0000000002,'sam'),(0000000003,'heather'),(0000000004,'heath'),(0000000005,'shamus'),(0000000006,'jennifer'),(0000000007,'geronimo'),(0000000008,'gerald');
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

,

Leave a Reply

Trackbacks:0

Listed below are links to weblogs that reference
Autocomplete field with jQuery and PHP from rhythmicalmedia.com
TOP