Creating Dropdowns with PHP and Xajax
Contents
- Abstract
- The Database Schema
- Connecting to the Database
- The Xajax Script
- The Xajax Response
- The HTML Page
- Putting it all together
Abstract
In this short tutorial we will look at putting together some form elements with PHP and Xajax. This will show how to dynamically fill form dropdown select menu's asynchronisly with data from a MySQL database. The second dropdown select menu will then be populated with data from the database, based on a value from the first dropdown select menu. All this, without a page refresh.
Several elements will be used here, PHP, MySQL, and Xajax. No javascript is used, it is all dynamically generated via the Xajax libraries. We will use an imaginary cart for a PHPRO Bookstore to be the basis of the script.
The Database Schema
The basis for this tutorial is the PHPRO Bookstore. The database is simple with a table of categories and a table of books. Each book belongs to a category and each book(product) has an ID as does each category. Here is the SQL dump. So, create a database named phpro_cart dump this into it.
category_id int(11) NOT NULL auto_increment,
category_name varchar(30) NOT NULL,
category_description varchar(200) NOT NULL,
PRIMARY KEY (category_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO phpro_categories (category_id, category_name, category_description) VALUES
(1, 'romance', 'Mushy stuff'),
(2, 'Thriller', 'Murder Mystery and Mayhem'),
(3, 'Adventure', 'Swash buckling yarns'),
(4, 'Sport', 'Get out and about'),
(5, 'autobiography', 'whos who'),
(6, 'non fiction', 'Get the facts');
CREATE TABLE phpro_products (
product_id int(11) NOT NULL auto_increment,
category_id int(11) NOT NULL,
product_name varchar(30) NOT NULL,
product_price double NOT NULL,
PRIMARY KEY (product_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO phpro_products (product_id, category_id, product_name, product_price) VALUES
(1, 1, 'kiss me quick', 23.4),
(2, 1, 'stolen kiss', 58.44),
(3, 1, 'a long road', 12.4),
(4, 1, 'I never touched her', 72.46),
(5, 2, 'She did it', 39.39),
(6, 2, 'No he did', 29.44),
(7, 2, 'Broken alibi', 33.3),
(8, 2, 'The dark shadow', 44.4),
(9, 3, 'A pirates life', 73.2),
(10, 3, 'the mountain climber', 88.22),
(11, 3, 'The deep sea', 55.5),
(12, 3, 'the final frontier', 22.2),
(13, 4, 'Kicking goals', 33),
(14, 4, 'Scoring', 99.99),
(15, 4, 'Mouse Whittling', 55.5),
(16, 6, 'Bible', 22.4),
(17, 6, 'Koran', 22),
(18, 6, 'Bhagavad Gita', 44.3);
Connecting to the Database
With the above tables installed, we can move onto the script itself. Like many PHP script we need to connect to the dataase to collect the information we need. To achieve this a global instance is used of the PDO class. This class is taken directly from the PHPRO Tutorial http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html tutorial, and is repeat here for convenience.
<?php
/*** database class ***/
class db{
/*** Declare instance ***/
private static $instance = NULL;
/**
*
* the constructor is set to private so
* so nobody can create a new instance using new
*
*/
private function __construct() {
/*** maybe set the db name here later ***/
}
/**
*
* Return DB instance or create intitial connection
*
* @return object (PDO)
*
* @access public
*
*/
public static function getInstance() {
$hostname = 'localhost';
$username = 'username';
$password = 'password';
if (!self::$instance)
{
self::$instance = new PDO("mysql:host=$hostname;dbname=phpro_cart", $username, $password);
self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
return self::$instance;
}
/**
*
* Like the constructor, we make __clone private
* so nobody can clone the instance
*
*/
private function __clone(){
}
} /*** end of class ***/
?>
The Xajax Script
This script contains a few parts where the tasks are broken into several simple functions. The first two to look at are the getCategorData() function and the getProductData function. These do, as the name suggests, get the categories data and the products data from the database. The return value makes use of a small snippet that can be found here on PHPRO at http://www.phpro.org/examples/PDO-to-Array.html which formats the PDO result sets into a format we can use with the Xajax functions. Lets have a look at this section first, comments though-out the code will help guide you through. Note that we also include the xajax libraries here.
<?php
/*** include the xajax libraries ***/
include '/www/xajax/xajax_core/xajax.inc.php';
/**
*
* @convert pdo multi-d array to single d
*
* @param array $result
*
* @return $array
*
*/
function pdo2array($result){
$new=array();
foreach ( $result as $val )
{
$keys = array_keys($val);
$new[$val[$keys[0]]] = $val[$keys[1]];
}
return $new;
}
/**
*
* @getCategoryData
*
* @return array
*
*/
function getCategoryData()
{
/*** database instance ***/
$db=db::getInstance();
/*** the SQL query ***/
$sql="SELECT category_id, category_name from phpro_categories";
/*** always user prepared statements ***/
$stmt = $db->prepare($sql);
/*** execute the query ***/
$stmt->execute();
/*** use pdo2Array function to flatten out the array ***/
return pdo2Array($stmt->fetchAll(PDO::FETCH_ASSOC));
}
/**
*
* @fetch data from products table
*
* @param INT $category_id
*
* @return array
*
*/
function getProductData($category_id)
{
/*** a new db instance ***/
$db=db::getInstance();
/*** the SQL query ***/
$sql="SELECT product_id, product_name FROM phpro_products WHERE category_id=:category_id";
/*** always user prepared statements ***/
$stmt = $db->prepare($sql);
/*** bind the category_id ***/
$stmt->bindParam(':category_id', $category_id);
/*** execute the query ***/
$stmt->execute();
/*** use pdo2Array function to flatten out the array ***/
return pdo2Array($stmt->fetchAll(PDO::FETCH_ASSOC));
}
?>
In the code above we fetch data from the phpro_categories table and the phpro_products table. the difference between the two is that the getProductsData function requires a single paramenter, the category_id. This is called when the first dropdown value is selected, and the category_id is passed from the Xajax to the PHP function, which, in turn, fetches all the books(products) from the respective category.
The Xajax Response
Here we see the functions that contain the Xajax goodness. As you can see they are simple PHP functions that return a value. These values are used by Xajax to generate the javascript needed to update the dropdown select menu's.
<?php
/**
*
* fetch categories into response object
*
* @return obj
*
*/
function getCategories()
{
/*** a new xajax response object ***/
$objResponse=new xajaxResponse();
$objResponse->script("clearOption('categories');");
/*** get the categories from the database ***/
$OL=array();
$OL=getCategoryData();
/*** a return value ***/
$ret = '';
/*** loop of the database results ***/
foreach ($OL as $value => $text)
{
/*** set the option names and values ***/
$ret .= '<option value="'.$value.'">'.$text.'</option>';
}
/*** assign the response to the categories div ***/
$objResponse->assign('categories', 'innerHTML', $ret);
/*** return the object response ***/
return $objResponse;
}
/**
*
* @fetch the products into a response object
*
* @param INT $category_id
*
* @return object
*
*/
function getProducts($category_id)
{
/*** a new xajax response object ***/
$objResponse=new xajaxResponse();
$objResponse->script("clearOption('products');");
/*** get the products data ***/
$OL=array();
$OL=getProductData($category_id);
/*** get the products into a value ***/
$ret = '';
foreach ($OL as $value => $text)
{
/*** the product dropdown options ***/
$ret .= '<option value="'.$value.'">'.$text.'</option>';
}
/*** assign the options to the products div ***/
$objResponse->assign('products', 'innerHTML', $ret);
/*** and return the response ***/
return $objResponse;
}
/**
*
* @add item to cart
*
* @param array $FormValues
*
* @return object
*
*/
function addToCart($FormValues)
{
/*** a new xajax response ***/
$objResponse=new xajaxResponse();
/*** assign the category id to its div ***/
$objResponse->assign("cart_category_id", "innerHTML", $FormValues["categories"]);
/*** assign the product id to its div ***/
$objResponse->assign("cart_product_id", "innerHTML", $FormValues["products"]);
/*** return the object response ***/
return $objResponse;
}
/*** a new xajax object ***/
$xajax = new xajax();
/*** register the PHP functions ***/
$xajax->registerFunction('getCategories');
$xajax->registerFunction('getProducts');
$xajax->registerFunction('addToCart');
$xajax->processRequest();
?>
The HTML Page
The final piece of this puzzle is some basic HTML and a few xajax calls to the PHP functions above. The script starts by clicking on the Show Categories link. This populates the categories dropdown menu by calling the getProducts() PHP function via the xajax_getProducts() function. To populate the second dropdown select menu an onChange event is used so when a new category is selected, the products dropdown is populated with a list of books from that category, along with the id of each item. When a book is selected, another onChange event is triggered and calls the addToCart() function. Of course, this function is incomplete and merely shows the category ID and the product ID of the item selected. From there you can handle the data any way you wish to complete your task.
<html>
<head>
<title>PHPRO Xajax Dropdowns</title>
<?php
/*** print the javasript ***/
$xajax->printJavascript('/xajax');
?>
</head>
<body>
<h2>XAJAX Cascading dropdowns</h2>
<p><a href="#" onClick="xajax_getCategories();">Show Categories</a></p>
<form name="cart_form" method="post" action="post" id="cart_form">
<select name="categories" id="categories" onChange="xajax_getProducts(this.value);">
<option value="-1">(No Selection)</option>
</select>
<select name="products" id="products" onChange="xajax_addToCart(xajax.getFormValues('cart_form'));">
<option value="-1">(No Selection)</option>
</select>
<p>Category ID:</p>
<div id="cart_category_id"></div>
<p>Product ID:</p>
<div id="cart_product_id"></div>
</form>
</body>
</html>
Putting it all together
As with all good tutorials, a completed script has been prepared to work out-of-the-box..
<?php
/*** database class ***/
class db{
/*** Declare instance ***/
private static $instance = NULL;
/**
*
* the constructor is set to private so
* so nobody can create a new instance using new
*
*/
private function __construct() {
/*** maybe set the db name here later ***/
}
/**
*
* Return DB instance or create intitial connection
*
* @return object (PDO)
*
* @access public
*
*/
public static function getInstance() {
$hostname = 'localhost';
$username = 'username';
$password = 'password';
if (!self::$instance)
{
self::$instance = new PDO("mysql:host=$hostname;dbname=phpro_cart", $username, $password);
self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
return self::$instance;
}
/**
*
* Like the constructor, we make __clone private
* so nobody can clone the instance
*
*/
private function __clone(){
}
} /*** end of class ***/
/*** include the xajax libraries ***/
include '/www/xajax/xajax_core/xajax.inc.php';
/**
*
* @convert pdo multi-d array to single d
*
* @param array $result
*
* @return $array
*
*/
function pdo2array($result){
$new=array();
foreach ( $result as $val )
{
$keys = array_keys($val);
$new[$val[$keys[0]]] = $val[$keys[1]];
}
return $new;
}
/**
*
* @getCategoryData
*
* @return array
*
*/
function getCategoryData()
{
/*** database instance ***/
$db=db::getInstance();
/*** the SQL query ***/
$sql="SELECT category_id, category_name from phpro_categories";
/*** always user prepared statements ***/
$stmt = $db->prepare($sql);
/*** execute the query ***/
$stmt->execute();
/*** use pdo2Array function to flatten out the array ***/
return pdo2Array($stmt->fetchAll(PDO::FETCH_ASSOC));
}
/**
*
* @fetch data from products table
*
* @param INT $category_id
*
* @return array
*
*/
function getProductData($category_id)
{
/*** a new db instance ***/
$db=db::getInstance();
/*** the SQL query ***/
$sql="SELECT product_id, product_name FROM phpro_products WHERE category_id=:category_id";
/*** always user prepared statements ***/
$stmt = $db->prepare($sql);
/*** bind the category_id ***/
$stmt->bindParam(':category_id', $category_id);
/*** execute the query ***/
$stmt->execute();
/*** use pdo2Array function to flatten out the array ***/
return pdo2Array($stmt->fetchAll(PDO::FETCH_ASSOC));
}
/**
*
* fetch categories into response object
*
* @return obj
*
*/
function getCategories()
{
/*** a new xajax response object ***/
$objResponse=new xajaxResponse();
$objResponse->script("clearOption('categories');");
/*** get the categories from the database ***/
$OL=array();
$OL=getCategoryData();
/*** a return value ***/
$ret = '';
/*** loop of the database results ***/
foreach ($OL as $value => $text)
{
/*** set the option names and values ***/
$ret .= '<option value="'.$value.'">'.$text.'</option>';
}
/*** assign the response to the categories div ***/
$objResponse->assign('categories', 'innerHTML', $ret);
/*** return the object response ***/
return $objResponse;
}
/**
*
* @fetch the products into a response object
*
* @param INT $category_id
*
* @return object
*
*/
function getProducts($category_id)
{
/*** a new xajax response object ***/
$objResponse=new xajaxResponse();
$objResponse->script("clearOption('products');");
/*** get the products data ***/
$OL=array();
$OL=getProductData($category_id);
/*** get the products into a value ***/
$ret = '';
foreach ($OL as $value => $text)
{
/*** the product dropdown options ***/
$ret .= '<option value="'.$value.'">'.$text.'</option>';
}
/*** assign the options to the products div ***/
$objResponse->assign('products', 'innerHTML', $ret);
/*** and return the response ***/
return $objResponse;
}
/**
*
* @add item to cart
*
* @param array $FormValues
*
* @return object
*
*/
function addToCart($FormValues)
{
/*** a new xajax response ***/
$objResponse=new xajaxResponse();
/*** assign the category id to its div ***/
$objResponse->assign("cart_category_id", "innerHTML", $FormValues["categories"]);
/*** assign the product id to its div ***/
$objResponse->assign("cart_product_id", "innerHTML", $FormValues["products"]);
/*** return the object response ***/
return $objResponse;
}
/*** a new xajax object ***/
$xajax = new xajax();
/*** register the PHP functions ***/
$xajax->registerFunction('getCategories');
$xajax->registerFunction('getProducts');
$xajax->registerFunction('addToCart');
$xajax->processRequest();
?>
<html>
<head>
<title>PHPRO Xajax Dropdowns</title>
<?php
/*** print the javasript ***/
$xajax->printJavascript('/xajax');
?>
</head>
<body>
<h2>XAJAX Cascading dropdowns</h2>
<p><a href="#" onClick="xajax_getCategories();">Show Categories</a></p>
<form name="cart_form" method="post" action="post" id="cart_form">
<select name="categories" id="categories" onChange="xajax_getProducts(this.value);">
<option value="-1">(No Selection)</option>
</select>
<select name="products" id="products" onChange="xajax_addToCart(xajax.getFormValues('cart_form'));">
<option value="-1">(No Selection)</option>
</select>
<p>Category ID:</p>
<div id="cart_category_id"></div>
<p>Product ID:</p>
<div id="cart_product_id"></div>
</form>
</body>
</html>