query w/i a query

Discussion in 'PHP' started by upside, Jun 25, 2006.

  1. #1
    This is really easy, but sadly that's not helping me make it work.

    I've got a database of products arranged in increasing degrees of specificity:
    category then subcategory then product_group. Category is passed in the url. I want to then iterate through unique subcats for that category (this part works). Then I want to also find the unique product_groups for each subcat. So if "clothes" was passed in the url I want the page to have subcat "pants, shirts, hats" with product_groups "long pants, short pants" and "yellow shirts, blue blue shirts, etc." Here's where I am:

    <?
    //get passed variable            
    $category = $_REQUEST['id'];
    
    //run first query
    $sql = "SELECT DISTINCT subcategory FROM my_cms WHERE category='$category'";
    $subcat_array = $DB->query($sql);
    
    $counter = 0;	
    foreach($subcat_array as $subcategory) {
    	$counter++;
    //run second query on the results of the first
    	$product_sql="SELECT DISTINCT product_group FROM my_cms WHERE subcategory='$subcategory'";
    	$group_array=$DB->query($product_sql);
    	foreach($group_array as $product_group)			
    	
    //iterate through each query post each result as a link - this also works, or //worked, for the first query
    	 {
    		echo "$counter. <a href='subcategory.php?id=".$subcategory["subcategory"]."'>".$subcategory["subcategory"]."</a>:: <BR><BR>";
    echo "$counter. <a href='subcategory.php?id=".$subcategory["subcategory"]."'>".$product_group["product_group"]."</a>:: <BR><BR>";
    	}
    
    }
          ?> 
    Code (markup):
    It's basically the exact same query again inside the foreach(), and since it works in the first I can't figure out why not in the second. I am guessing that I am not passing the variable correctly - but for the life of me I can't figure out how. If anyone has any thoughts, that would be swell.
     
    upside, Jun 25, 2006 IP
  2. FxAnd

    FxAnd Peon

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    $category = $_REQUEST['id']; <== may be this is the problem..
    you better specific with $_GET or $_POST method..
    i never use $_REQUEST anymore..
    very unsecure..
     
    FxAnd, Jun 26, 2006 IP
  3. ccoonen

    ccoonen Well-Known Member

    Messages:
    1,606
    Likes Received:
    71
    Best Answers:
    0
    Trophy Points:
    160
    #3
    Generally when dealing with re-iterating 1 query multiple ways on the same page, you can:
    create a db connection,
    run your query,
    use your query data to build a 2-dim array,
    close your db connection.

    Now you have a two dimensional array that you can shift, sort, modify, and display in any fashion you want as many times as you want in the page.
     
    ccoonen, Jun 28, 2006 IP