Hi GHicks,
My suggestion is a little different direction and my code will not work 100% as I don't completely understand your desired result set, but this idea takes advantage of binding parameters and reusing the same prepared statement which is efficient. You use the database to get your result sets by individual queries based on the category array and then builds and array that groups these categories in their own arrays. You can then decide how you want to get at the $key and $value pieces of data.
This uses a DbFactory class that I wrote. Copy it to it's own php file and then name it DbFactory.php.
PHP Code:
<?php
class DbFactory{
private static $factory;
public static function getFactory(){
if (!self::$factory){
self::$factory = new DbFactory();
return self::$factory;
} else {
throw new exception('DbFactory could not return database instance.');
}
}
private $db;
public function getConnection(){
if (!$db)
$db = new PDO("mysql:host=localhost;dbname=your_db", "your_user", "your_password");
return $db;
}
}
Then in you Main PHP page do something like:
PHP Code:
<?php
require_once('DbFactory.php');
$o_Db = DbFactory::getFactory()->getConnection(); //Get PDO instance
# Prime the categories array
$categories = array(
'Accessories-Ash_Trays'
, 'Accessories-Coat_Hooks'
, 'Accessories-Light_Switch'
);
$html = '';
# call select function passing the db instance and the category array
$categories = selectProductCategories($o_Db, $categories );
# loop through the multi-dimensional array and extract the formated html in a single variable to
# output to the browser when complete.
foreach($categories as $category){
foreach($category as $key => $value){
$html .= "<div id='CatTitleDiv'>$key</div><div id='description'>$value</div>";
}
}
# echo formatted html;
echo $html;
function selectProductCategories($o_Db, array $categories){
$sql = "
SELECT
tbl_catnav.category_set
, tbl_products.part_number
FROM
tbl_products
LEFT JOIN tbl_catnav
ON tbl_products.cat_id = tbl_catnav.lastcatid
WHERE catnav.category_set = ':category_name'
ORDER BY
tbl_catnav.category_set
, tbl_products.part_number;";
$stmt = $o_Db->prepare($sql);
$cats = array();
foreach($cateories as $category){
$stmt->bindParam(':category_name', $category):
$stmt->execute();
$rows = $stmt->fetchAll(PDO_FETCH_ASSOC);
$cats[$category] = $rows;
}
return $cats;
}
?>
I don't know if this works for you but it is an idea anyway.
Regards,
Steve
/a><br />";
Reply With Quote
Bookmarks