Page 1 of 1

Nested loops or Array???

Posted: Thu Nov 13, 2008 10:18 am
by psurrena
Question is: Say I want to display three levels of information from my database via loops. I currently, and probably inefficiently, use three nested while() loops. Is there away to build a three-dimensional array and run one loop?

Example, you want to display a list of toys in an organized fashion:
1) The store
2) The brand(s) available in the store
3) The toys of that brand.

I would have five tables:
1) Store
2) Brand
3) Toy
4) brand_toy
5) brand_store

I would write three queries:
1) Select all stores
2) Select all the brands in that store using the brand_store table
3) Select all the toys by the brands using the brand_toy table.

It would work something like this:

Code: Select all

while($storeQuery){
    while($brandQuery($storeId)){
        while($toyQuery($brandId)){
        }
    }
}
The final list would be:

Kiddie City:
Tyco
-- Truck
-- Car

Toys R' Us
Nintendo
-- Mario Brothers
Lego
-- LEGO Set 1
-- Lego Set 2

Re: Nested loops or Array???

Posted: Thu Nov 13, 2008 11:44 am
by infolock
Why not just write one query that gets all the data at once for you? If you show us the 3 queries maybe we can make that happen, but it also depends a lot on your database being a relational db design.

secondly, your while statements don't exactly make sense as it looks like you're storing a function name into a variable and then calling that function with the variable. I understand you're probably just making a point with an example, but sometimes things like that can get in the way with help if not properly explained ;)

Re: Nested loops or Array???

Posted: Thu Nov 13, 2008 1:18 pm
by psurrena
I guess the problem then is I don't know how to write the query that would return that kind of list. How would the structure look?

You're right about the while, I was just showing what I use in the query to reference the previous query.

Re: Nested loops or Array???

Posted: Thu Nov 13, 2008 2:57 pm
by requinix
Since you're selecting all of everything you don't need three queries.

Just one - the one about toys. Adjust your query so that it gets all the information that the other two would get as well (if toys are associated to store, brand by some number, use a JOIN to get the store/brand names as well). Add an ORDER BY (store), (brand), (toy) and you're ready.

Code: Select all

stores
------
storeid int primary key
name varchar
 
brands
------
brandid int primary key
storeid int
name varchar
 
toys
----
toyid int primary key
brandid int
storeid int
name varchar
Before:

Code: Select all

SELECT * FROM stores ORDER BY name
SELECT * FROM brands WHERE storeid=$storeid ORDER BY name
SELECT * FROM toys WHERE brandid=$brandid AND storeid=$storeid ORDER BY name
After:

Code: Select all

SELECT t.toyid AS toyid, t.name AS toyname,
    b.brandid AS brandid, b.name AS brandname,
    s.storeid AS storeid, s.name AS storename
FROM toys t
JOIN brands b ON t.brandid=b.brandid
JOIN stores s ON t.storeid=s.storeid AND b.storeid=s.storeid
ORDER BY s.name, b.name, t.name
When you start looping through, keep track of the previous store name and brand name. If anything changes you'll need to print new headers.

Code: Select all

$prevstore = "";
$prevbrand = "";
while ($line = mysql_fetch_array($query)) {
    if ($line["storename"] != $prevstore) {
        echo "<b>$line[storename]</b>\n"; $prevstore = $line["storename"];
        echo "<u>$line[brandname]</u>\n"; $prevbrand = $line["brandname"];
    } else if ($line["brandname"] != $prevbrand) {
        echo "<u>$line[brandname]</u>\n"; $prevbrand = $line["brandname"];
    }
    echo "-- <i>$line[toyname]</i>\n";
}

Re: Nested loops or Array???

Posted: Thu Nov 13, 2008 3:09 pm
by chopsmith
One way to accomplish what you want:

Code: Select all

 
Table stores:
| store_id | store_name | other store fields |
 
Table brands:
| brand_id | brand_name | other brand fields |
 
Table products:
| product_id | product_name | brand_id | other product fields |
 
Table stores_products:
| store_id | product_id |
 
To generate a result object with all the information you need to generate the list you want, execute:

Code: Select all

 
SELECT * from stores_products 
inner join stores on stores.store_id = stores_products.store_id 
inner join products on products.product_id = stores_products.product_id 
inner join brands on brands.brand_id = products.brand_id
 
This will give you a result object whose rows look like this:

Code: Select all

 
store_id    product_id  store_id    store_name  product_id  product_name    brand_id    brand_id    brand_name
1           1           1           Walmart             1           Nike Shoes          1           1           Nike
1           2           1           Walmart             2           NB Shoes            2           2           New Balance
2           2           2           Target          2           NB Shoes            2           2           New Balance
 
Then, I'd build a nice array like this:

Code: Select all

 
$overallArray = array();
while ($row = mysql_fetch_assoc($result)) {
  $store = $row['store_name'];
  $brand = $row['brand_name'];
  $product = $row['product_name'];
  if (!in_array($store,$overallArray)) {
    $overallArray[$store] = array($brand => array($product));
  }
  elseif (!in_array($brand,$overallArray[$store])) {
    $overallArray[$store][] = array($brand => array($product));
  }
  else {
    $overallArray[$store][$brand][] = $product;
  }
}
 
The array would look something like this:

Code: Select all

 
Array (
         'Walmart' => Array (
                                  'Nike' => Array (
                                                      'Nike Shoes'
                                                       )
                                  'New Balance' => Array (
                                                                  'NB Shoes'
                                                                  )
                                   )
        'Target' => Array (
                                'New Balance' => Array (
                                                                'NB Shoes'
                                                                )
                                )
        )
 
Now that you've got that array, simply go:

Code: Select all

 
foreach ($overallArray as $store => $brandArray) {
  echo "<b>{$store}</b><br />";
  foreach ($brandArray as $brand => $productArray) {
    echo "<u>{$brand}</u><br />";
    foreach ($productArray as $product) {
      echo "<i>-- {$product}</i><br />";
    }
  }
  echo "<br />";
}
 
There you have it. I haven't tested it or anything, but I'm pretty sure it'll do the trick.

Re: Nested loops or Array???

Posted: Thu Nov 13, 2008 3:13 pm
by chopsmith
tasairis:

The only thing I don't like about your solution is that it requires the toys table to have brand_id and store_id. Because each toy has a unique brand, I prefer just to put the brand_id in the toy table, then do what I write above.

Just my thoughts. Glad to contribute my first solution.

Re: Nested loops or Array???

Posted: Thu Nov 13, 2008 3:14 pm
by infolock
psurrena wrote:I guess the problem then is I don't know how to write the query that would return that kind of list. How would the structure look?
You're right about the while, I was just showing what I use in the query to reference the previous query.
Ah well, I was just being picky about the while, but I thought that's what you mean ;) Anyways, post the 3 queries you are executing and we can put it together.

Or, you can follow tasairis' perfect example and try to build it on your own as he has pretty much shown you exactly what I'm talking about anyways with using inner joins to combine all 3 into one query.

chopsmith wrote:tasairis:

The only thing I don't like about your solution is that it requires the toys table to have brand_id and store_id. Because each toy has a unique brand, I prefer just to put the brand_id in the toy table, then do what I write above.

Just my thoughts. Glad to contribute my first solution.
It's just an example ;)

Re: Nested loops or Array???

Posted: Thu Nov 13, 2008 3:33 pm
by requinix
chopsmith wrote:The only thing I don't like about your solution is that it requires the toys table to have brand_id and store_id.
No, not really. I put it in there because it made sense: toys are put in stores. Maybe one of them is out-of-stock of a specific toy?

You can remove that field and the condition on that one JOIN and it should be fine.

Re: Nested loops or Array???

Posted: Thu Nov 13, 2008 3:47 pm
by chopsmith
tasairis wrote:
chopsmith wrote:The only thing I don't like about your solution is that it requires the toys table to have brand_id and store_id.
No, not really. I put it in there because it made sense: toys are put in stores. Maybe one of them is out-of-stock of a specific toy?

You can remove that field and the condition on that one JOIN and it should be fine.

Yes, stores carry toys, and toys have an associated brand. I'm just giving my input, not arguing. If we want to get into stock levels, neither of the solutions is complete (unless you are going to delete a product from your database whenever it goes out of stock). Under either solution, toys not sold in a store will not appear in that store's list of products. So I'm not really sure how "toys are put in stores" explains how "put[ting] it in there . . . made sense". Again, just trying to be helpful here.

Re: Nested loops or Array???

Posted: Mon Nov 17, 2008 8:32 am
by psurrena
My three queries are as follow:

Category Query
[sql]SELECT category_id, category_name FROM work_categoryORDER BY category_name ASC[/sql]

Subcategory Query
[sql]SELECT S.subcategory_name, S.subcategory_idFROM work_subcategory S, work_category_subcategory CSWHERE S.subcategory_id=CS.subcategory_idAND CS.category_id='$cid'ORDER BY S.subcategory_id ASC[/sql]

Project Query
[sql]SELECT P.project_id, P.project_nameFROM work_project P, work_project_subcategory PSWHERE P.project_id=PS.project_idAND PS.subcategory_id='$sid'ORDER BY P.project_id [/sql]

Re: Nested loops or Array???

Posted: Mon Nov 17, 2008 10:10 pm
by infolock
Try this query..

Code: Select all

 
select work_category.category_id, work_category.category_name, 
       work_subcategory.subcategory_name, work_subcategory.subcategory_id,
       work_project.project_id, work_project.project_name
FROM work_category
  INNER JOIN work_category_subcategory ON work_category.category_id = work_category_subcategory.category_id
  INNER JOIN work_subcategory ON work_category_subcategory.subcategory_id = work_subcategory.subcategory_id
  INNER JOIN work_project_subcategory.subcategory_id on work_subcategory.subcategory_id = work_project_subcategory.subcategory_id
  INNER JOIN work_project ON work_project_subcategory.project_id = work_project.project_id
WHERE work_category.category_id = '$cid' 
  AND work_subcategory.subcategory_id = '$sid' 
  ORDER BY category_name ASC, subcateogry_id ASC