Samalyse - Linked tables with Structures

The following example shows how to display two linked database tables with the Structures_DataGrid component. It uses the MySQL world database sample, and allows to click on a country name on the first grid, in order to see the corresponding cities on the second grid.

The example below is interactive. Click on a country name to display the associated cities. You can also sort the data and use the pagination.

To run this example you need PHP4 or PHP5 and the following PEAR packages:

Structures_DataGrid >= 0.8.0
Structures_DataGrid_Renderer_HTMLTable >= 0.1.3
Structures_DataGrid_DataSource_MDB2 >= 0.1.3
MDB2 >= 2.1.0
MDB2_Driver_mysql >= 1.1.0
PHP_Compat >= 1.5.0

Download: PHP source CSS stylesheet


<?php
/*
 * Example of linked tables with Structures_DataGrid
 * Author: Olivier Guilyardi - http://www.samalyse.com
 */

require_once 'PEAR.php';
require_once 'Structures/DataGrid.php';    
require_once 'HTML/Table.php';
require_once 'MDB2.php';
require_once 'PHP/Compat/Function/http_build_query.php';

PEAR::setErrorHandling(PEAR_ERROR_PRINT);

// Database connection:
// dsn.php contains one line: $DSN = "mysql://user:pass@host/database";
include "dsn.php";
$database =& MDB2::connect($DSN); 

// Formatter callback method, used to create the country links
function makeCountryLink($data)
{
    $country = $data['record']['name'];
    $request = $_GET;
    $request['country_code'] = $data['record']['code'];
    $request['country'] = $country;
    $query = htmlentities(http_build_query($request));
    return "<a href=\"?$query\">$country</a>";
}

// Set up the Countries DataGrid
$countries =& new Structures_DataGrid(10);
$countries->setDefaultSort(array('population' => 'DESC'));

$countries->bind("SELECT * FROM Country", array('dbc' => &$database));

$countries->generateColumns(array(
    'name'               => 'Country',
    'population'         => 'Population',
));

// Create the links in the countries grid
$column =& $countries->getColumnByField('name');
$column->setFormatter('makeCountryLink');

// Set up the Cities DataGrid
$cities =& new Structures_DataGrid(10);
$cities->setRequestPrefix('city_');
$cities->setDefaultSort(array('population' => 'DESC'));

$code = $database->escape(@$_GET['country_code']);
$where = $code ? "WHERE CountryCode = '$code'" : '';

$cities->bind("SELECT * FROM City $where", array('dbc' => &$database));

$cities->generateColumns(array(
    'name'          => 'City',            
    'population'    => 'Population',
));

// Build a reset link
$request = $_GET;
unset($request['country_code']);
unset($request['country']);
$query = htmlentities(http_build_query($request));
$resetLink = "<a href=\"?$query\">[X]</a>";

// Start output
?>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"/>
    <link rel="stylesheet" href="style.css" type="text/css">
</head>
<body>

<div class="grid">
<?php
// Render the Countries table
$table = new HTML_Table();

$countries->fill($table);

$table->setCaption("World");
$table->altRowAttributes(0, array(), array ('class' => 'odd'), true);

echo $table->toHTML();
echo "<p class=\"paging\">";
$countries->render('Pager', array('pagerOptions' => array('delta' => 0)));
echo "</p>";
?>
</div>

<div class="grid cities">
<?php
// Render the Cities table
$table = new HTML_Table();

$cities->fill($table);

$ofCountry = @$_GET['country'] ? "{$_GET['country']} $resetLink" : "Cities";
$table->setCaption("$ofCountry");
$table->altRowAttributes(0, array(), array ('class' => 'odd'), true);

echo $table->toHTML();
echo "<p class=\"paging\">";
$cities->render('Pager', array('pagerOptions' => array('delta' => 0)));
echo "</p>";
?>
</div>

</body>
</html>