[eluser]tpiscotti[/eluser]
Hello!
I need a bit of help returning the results of a subquery that is run within the "foreach" loop of my main query...this is in one of my model functions.
First, here is my original function (actually a generalized version of my actual code) withOUT the added subquery just so you can see where I started from. This works just fine, by the way, and returns the results I need:
ORIGINAL QUERY W/O SUBQUERY:
Code:
// Get Sub Categories (level 3)
function getSubCategories() {
$result = array();
$sql = "select *
from subcategories
where maincategoryId = 4
and doNotDisplay = false
order by subcategoryName";
$qry = $this->db->query($sql);
if ($qry->num_rows() > 0) {
foreach ($qry->result_array() as $row) {
$result[] = $row;
}
}
$qry->free_result();
return $result;
}
Now, what I need to do is retrieve a comma separated list of product numbers from the products table to display in this manner (more or less...this is just a rough idea):
Product Sub Category 1 - prodnum1, prodnum2, prodnum3, prodnum4
Product Sub Category 2 - prodnum1, prodnum2
Product Sub Category 3 - prodnum1, prodnum2, prodnum3
NOTE: In case anyone's wondering, this isn't how I would normally output a subcategory section like this if there are a lot of products involved, but for this particular site there are only a handful of many items per subcategory and it's just how the client requested it be displayed.
Anyway...
So, to get the associated product numbers from each subcategory being returned (see original query above), I am
attempting this
(new code is in the first foreach loop):
NEW ATTEMPTED QUERY W/ SUBQUERY ADDED:
Code:
// Get Sub Categories (level 3)
function getSubCategories() {
$result = array();
$sql = "select *
from subcategories
where maincategoryId = 4
and doNotDisplay = false
order by subcategoryName";
$qry = $this->db->query($sql);
if ($qry->num_rows() > 0) {
foreach ($qry->result_array() as $row) {
// Subquery - get product numbers for each subcategory
$prodNumbers = array();
$sql2 = "select productNumber
from products
where subcategoryId = " . $row['subcategoryId'] . "
and doNotDisplay = false
order by productNumber";
$qry2 = $this->db->query($sql2);
if ($qry2->num_rows() > 0) {
foreach ($qry2->result_array() as $row2) {
$prodNumbers = $row2['productNumber'];
}
}
$result = array(
'subcategoryId' => $row['subcategoryId'],
'subcategoryName' => $row['subcategoryName'],
'subcategoryDescription' => $row['subcategoryDescription'],
// add in the product numbers to return:
'prodNumbers' = $prodNumbers
);
}
}
$qry->free_result();
return $result;
}
"So what, then, is getting displayed when you run this, Tony??"
Good question. Well, I don't get an error. What I DO get is each subcategory being displayed as intended. However, the corresponding product numbers are just one number being displayed per subcategory and it is the LAST returned number from the subquery. Each number is correct for that subcategory, but only the LAST number for each category is being displayed. So I'm getting something like:
Product Sub Category 1 - prodnum (last num of list of corresp. nums)
Product Sub Category 2 - prodnum (last num of list of corresp. nums)
Product Sub Category 3 - prodnum (last num of list of corresp. nums)
I did do a separate test to make sure that the subquery is returning the correct amount of records at the very least, and it is...that part I know.
I must be close. What am I missing??? I'm seasoned with other languages and would pull this off easily in something I know, but am getting back into PHP after being away from it awhile and am new to CodeIgniter (although am NOT new to MVC frameworks). So any suggestions would be very helpful. I'm sure I'll end up slapping my forehead once someone points out what little bit of code I'm missing.
Any assistance is GREATLY appreciated!!!!
Best,
Tony
