Scenario
I want users to be able to search for their correct printer cartridge/toner based on brand, model or type.
I have an sql table called “printers” that has 8 columns:
ID, brand, model, oem, description, type, yield_mono, yield_colour
I wish to create 3 drop down lists in php for users to search on:
brand or
model or
type
I have created the code for “brand” but do not know how to add the other two. I have had a good fiddle around trying different options but to no avail.
I’d really appreciate any help. Here’s the code:
<?php
/*
- Testing configuration
*/
define(‘ENVIR’,0);
define(‘ENVIR_DEV’,0);
define(‘ENVIR_LIVE’,1);
/*
- Data base credentials (replace this with your db credentials)
*/
define(‘DB_USER’,‘xxx’);
define(‘DB_PWD’,‘xxx’);
define(‘DB_HOST’,‘localhost’);
define(‘DB_NAME’,‘xxx’);
/*
-
Connect to the database
*/
try {
$db = new PDO(‘mysql:host=’.DB_HOST.‘;dbname=’.DB_NAME,DB_USER,DB_PWD);
} catch(PDOExeption $e) {if(ENVIR == ENVIR_DEV) {
echo ‘<p>’,$e->getMessage(),‘</p>’;
}echo ‘<p>Unable to connect to database<p>’;
exit;
}
/*
- Template variables
*/
$tpl = array(
‘filter’=>array(
‘#action’ => $_SERVER[‘SCRIPT_NAME’]
,‘#method’ => ‘get’
,‘brand’ => array(
‘#values’=>array(
array(‘value’=>‘’,‘label’=>‘All’)
)
)
)
,‘grid’=>array(
‘printers’=>array()
)
);
$tpl = array(
‘filter’=>array(
‘#action’ => $_SERVER[‘SCRIPT_NAME’]
,‘#method’ => ‘get’
,‘model’ => array(
‘#values’=>array(
array(‘value’=>‘’,‘label’=>‘All’)
)
)
)
,‘grid’=>array(
‘printers’=>array()
)
);
/*
- Populate form filter brand options
*/
$stmt = $db->query(‘SELECT * FROM printers GROUP BY brand ORDER BY brand ASC’);
if($stmt === false) {
echo ‘<p>Unable to populate required data to build page.</p>’;
exit;
}
while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
$tpl[‘filter’][‘brand’][‘#values’] = array(
‘label’ => $row[‘brand’]
,‘value’ => $row[‘brand’]
,‘selected’ => isset($_GET[‘filter’],$_GET[‘filter’][‘brand’]) && $_GET[‘filter’][‘brand’] == $row[‘brand’]
);
}
/*
- Populate user grid - by brand
*/
$stmt = $db->prepare(sprintf(
‘SELECT * FROM printers %s’
, isset($_GET[‘filter’],$_GET[‘filter’][‘brand’]) && !empty($_GET[‘filter’][‘brand’])?‘WHERE brand = :brand’:‘’
));
if($stmt === false) {
echo ‘<p>Unable to populate required data to build page.</p>’;
exit;
}
$stmt->execute(isset($_GET[‘filter’],$_GET[‘filter’][‘brand’]) && !empty($_GET[‘filter’][‘brand’])?array(‘:brand’=>$_GET[‘filter’][‘brand’]):array());
while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
$tpl[‘grid’][‘printers’] = $row;
}
/*
- Start template output
*/
?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01//EN” “http://www.w3.org/TR/html4/strict.dtd”>
<html>
<head>
<meta http-equiv=“content-type” content=“text/html; charset=utf-8”>
<title></title>
</head>
<body>
<!-- user filter template –>
<form action=“<?php echo $tpl[‘filter’][‘#action’]; ?>” method=“<?php echo $tpl[‘filter’][‘#method’]; ?>”>
<fieldset>
<legend>Find Your Toner</legend>
<ul>
<label for="filter-brand">Brand</label>
<select name="filter[brand]" id="filter-brand">
<?php
foreach($tpl['filter']['brand']['#values'] as &$option) {
printf(
'<option value="%s"%s>%s</option>'
,htmlentities($option['value'])
,$option['selected']?' selected':''
,htmlentities($option['label'])
);
}
?>
</select>
<label for="filter-model">Model</label>
<select name="filter[model]" id="filter-model">
<?php
foreach($tpl['filter']['model']['#values'] as &$option) {
printf(
'<option value="%s"%s>%s</option>'
,htmlentities($option['value'])
,$option['selected']?' selected':''
,htmlentities($option['label'])
);
}
?>
</select>
<label for="filter-type">Type</label>
<select name="filter[type]" id="filter-type">
<?php
foreach($tpl['filter']['type']['#values'] as &$option) {
printf(
'<option value="%s"%s>%s</option>'
,htmlentities($option['value'])
,$option['selected']?' selected':''
,htmlentities($option['label'])
);
}
?>
</select>
<input type="submit" name="filter[submit]" value="Find">
</ul>
</fieldset>
</form>
<!-- data grid template –>
<table width=“100%”>
<caption>Results</caption>
<thead>
<tr>
<th>Brand</th>
<th>Model</th>
<th>OEM</th>
<th>Description</th>
<th>Type</th>
<th>Mono Yield</th>
<th>Colour Yield</th>
</tr>
</thead>
<tbody>
<?php
if(!empty($tpl[‘grid’][‘printers’])) {
foreach($tpl[‘grid’][‘printers’] as &$printers) {
printf(
‘<tr>
<td>%s</td>
<td>%s</td>
</tr>’
,htmlentities($printers[‘brand’])
,htmlentities($printers[‘model’])
,htmlentities($printers[‘oem’])
,htmlentities($printers[‘description’])
,htmlentities($printers[‘type’])
,htmlentities($printers[‘mono_yield’])
,htmlentities($printers[‘colour_yield’])
);
}
} else {
echo ‘<tr><td colspan=“2”>No names available</td></tr>’;
}
?>
</tbody>
</table>
</body>
</html>
