Autocomplete field with jQuery and PHP

Filed in Lab Leave a comment

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 */;

,

jQuery solution to the add ingredient row problem

Filed in Recipe Manager Leave a comment

Due to the fact that I could not find a reasonable solution within the CakePHP framework to add a new row of ingredient list inputs to recipe form I solved the problem using jQuery.

This is the script.

$('#add_ingredient_row').click(function()
{
	// Clone the last row of the table.
	var clonedRow = $("#ingredient_list tr:last").clone();
 
	// Generate an incremented index for the new row. -1 because there is a table header row
	var newIndex = (document.getElementById('ingredient_list').rows.length -1);
 
	// Set the ID of the row with the new index
	clonedRow[0].id = 'ingredient_' + newIndex;
 
 
	//console.log('ingredient_' + newIndex);
	// Loop through all of the inputs and select in the cloned row
	// Find the name and ID of the input/select element and find the number in it abd replace it with the
	// new index.
	$.each($('input, select', clonedRow), function(i, val)
	{
		//console.log(val);
 
		//console.log('Index -1: ' + (newIndex -1) + '. Index: ' + newIndex);
		val.name = val.name.replace((newIndex - 1), newIndex);
		val.id = val.id.replace((newIndex - 1), newIndex);
		val.value = '';
 
	});
	// put the row into the table
	$("#ingredient_list").append(clonedRow); 
}
);

This is the table that it operates on.

<table id="ingredient_list">
<tr>
	<th>Amount</th>
 
	<th>Measurement Type</th>
	<th>Description</th>
	<th>Ingredient</th>
</tr>
	<tr id="ingredient_0">
		<td>
			<div class="input text"><input name="data[IngredientList][0][amount]" type="text" maxlength="5" value="" id="IngredientList0Amount" /></div><input type="hidden" name="data[IngredientList][0][id]" value="" id="IngredientList0Id" />		</td>
 
		<td>
			<select name="data[IngredientList][0][measurement_type_id]" id="IngredientList0MeasurementTypeId">
				<option value=""></option>
				<option value="0000000002">cup</option>
				<option value="0000000005">fluid ounce</option>
				<option value="0000000001">ounce</option>
				<option value="0000000004">tablespoon</option>
				<option value="0000000003">teaspoon</option>
			</select>		
		</td>
 
		<td>
			<div class="input text"><input name="data[IngredientList][0][description]" type="text" maxlength="255" value="" id="IngredientList0Description" /></div>		</td>
		<td>
			<select name="data[IngredientList][0][ingredient_id]" id="IngredientList0IngredientId">
				<option value=""></option>
				<option value="0000000006">broccoli</option>
				<option value="0000000008">garlic</option>
				<option value="0000000009">olive oil</option>
				<option value="0000000010">peanut oil</option>
 
				<option value="0000000012">red onion</option>
				<option value="0000000007">white onion</option>
				<option value="0000000011">yellow onion</option>
			</select>		
		</td>
	</tr>
</table>

Here is the final solution I came up with. I abstracted out the cloning and manipulation code into a separate function so that it would be DRY.

 
$(document).ready(function()
{
	$('#add_recipe_row').click(function()
	{
		add_row('recipes_table', 'menu_item_');
	}); 
 
	$('#add_ingredient_row').click(function()
	{
		add_row('ingredient_list', 'ingredient_');
	}
	);
 
}); // ends document.ready
 
 
function add_row(tableID, rowIDprefix)
{
 
	// Clone the last row of the table.
	var clonedRow = $('#'+ tableID + ' tr:last').clone();
 
	// Generate an incremented index for the new row. -1 because there is a table header row
	var newIndex = (document.getElementById(tableID).rows.length -1);
 
	// Set the ID of the row with the new index
	clonedRow[0].id = rowIDprefix + newIndex;
 
 
	//console.log('ingredient_' + newIndex);
	// Loop through all of the inputs and select in the cloned row
	// Find the name and ID of the input/select element and find the number in it abd replace it with the
	// new index.
	$.each($('input, select', clonedRow), function(i, val)
	{
		//console.log(val);
 
		//console.log('Index -1: ' + (newIndex -1) + '. Index: ' + newIndex);
		val.name = val.name.replace((newIndex - 1), newIndex);
		val.id = val.id.replace((newIndex - 1), newIndex);
		val.value = '';
 
	});
	// put the row into the table
	$('#' + tableID).append(clonedRow);
}

TOP