• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need help with subquery in model

#1
[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

#2
[eluser]TheFuzzy0ne[/eluser]
Perhaps it's just me, but I would have thought:
Code:
$prodNumbers = $row2['productNumber'];
should be:
Code:
$prodNumbers[] = $row2['productNumber'];

As you declared this variable as an array. Also, your variable is declared inside of the loop, and therefore is being overwritten with an empty array with each iteration.

#3
[eluser]tpiscotti[/eluser]
Thanks for you quick reply!

Actually, I had tried that as well for the same reason you mentioned...I initialized that variable as an array so it would stand to reason that I should assign the returned value as you pointed out. However, I end up not even getting numbers returned. Next to each category, I just get the word "Array":

Product Sub Category 1 - Array

Product Sub Category 2 - Array

Product Sub Category 3 - Array

etc.

Not sure what else I'm missing here.

Thanks again!
T

#4
[eluser]TheFuzzy0ne[/eluser]
From what I gather, the product data for the sub categories is stored in the array. You'd need to iterate through them if you want to access the product data inside them.

#5
[eluser]tpiscotti[/eluser]
I will work with it further based on your suggestion. If I end up stumbling upon the solution I will definitely post it.

Thanks again!!

Best,
T


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.