Chapter 4: Creating the Main Web Site
95
What all this means is some reworking of the getSubCategories() function. It will need to pull out a
random thumbnail from a product within the category being listed. This may seem like a very
complicated thing, but all that ’ s required is a subquery within the main query.
As long as you create and run this second query with a different object instantiation (say, $Q2 instead
of $Q ), then you ’ ll be fine. In the following code listing, the subquery is bold to bring attention to it:
function getSubCategories($catid){
$data = array();
$this- > db- > select(‘id,name,shortdesc’);
$this- > db- > where(‘parentid’, $catid);
$this- > db- > where(status’, ‘active’);
$this- > db- > orderby(‘name’,’asc’);
$Q = $this- > db- > get(‘categories’);
if ($Q- > num_rows() > 0){
foreach ($Q- > result_array() as $row){$Q2 = $this- > db- > query(“select thumbnail as src from products
where category_id=”.$row[‘id’]. “
order by rand() limit 1”);if($Q2- > num_rows() > 0){
$thumb = $Q2- > row_array();
$THUMB = $thumb[‘src’];
}else{
$THUMB = ‘’;
}$Q2- > free_result();$data[] = array(
‘id’ = > $row[‘id’],
‘name’ = > $row[‘name’],
‘shortdesc’ = > $row[‘shortdesc’],
‘thumbnail’ = > $THUMB
);
}
}$Q- > free_result();return $data;
}As the subquery runs, any thumbnail path retrieved in the process gets stored in a variable, then passed
down to the $data array that is being collated. What you end up with is a complete packet of
information for the view, one that contains category information and product thumbnails rolled into one.