astypulk Posted October 17, 2013 Share Posted October 17, 2013 I am trying to create dynamic categories nav for a gallery page. The structure of the nav would be: <ul><li><a href="#">Category1</a><ul><li><a href="#">SubCategory1</a></li><li><a href="#">SubCategory2</a></li><li><a href="#">SubCategory3</a></li></ul></li><li><a href="#">Category2</a><ul><li><a href="#">SubCategory1</a></li><li><a href="#">SubCategory2</a></li><li><a href="#">SubCategory3</a></li></ul></li></ul> The current tables I created in my database are: categories idCat Category subcategories idSub subCategory cat_sub idCat idSub And what I have so far in PHP and MySQL statements is: <?php$con = mysql_connect("localhost", "xray", "password") or die('Could not connect to server');mysql_select_db("xray", $con) or die('Sorry, could not connect to the database');$query = "SELECT categories.Category, categories.idCat, subcategories.subCategory, subcategories.idSubFROM categories JOIN cat_sub ON categories.idCat = cat_sub.idCatJOIN subcategories ON subcategories.idSub = cat_sub.idSub";$result = mysql_query($query) or die('Error');while($row = mysql_fetch_array($result, MYSQL_ASSOC)){}?> I think I am probably using the wrong join, as its returning each Category several times. And I am not sure how to structure the while loop to achieve the above mentioned structure. Any advice would be much appreciated. Thanks, Aleks Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 17, 2013 Share Posted October 17, 2013 (edited) Yes the join query will return the same category each time, but the sub category should be different? To get the result you want you'll want loop through the results and store the categories in an array // store categories in array $categories = array(); while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $category_id = $row['idCat']; // create category if(!isset($categories[ $category_id ])) { $categories[ $category_id ] = array( 'title' => $row['Category'], 'sub_categories' => array() // store sub categories as an array ); } // add sub categories $categories[ $category_id ]['sub_categories'][] = array( 'id' => $row['idSub'], 'title' => $row['subCategory'] ); } To output the category list // output category list echo "<ul>\n"; foreach($categories as $cat_id => $category) { echo "\t<li><a href=\"#\">" . $category['title'] . "</a>\n"; if(is_array($category['sub_categories'])) { echo "\t<ul>\n"; foreach ($category['sub_categories'] as $sub_id => $sub_category) { echo "\t\t<li><a href=\"#\">" . $sub_category['title'] . "</a></li>\n"; } echo "\n\t</ul>\n</li>\n"; } } echo '</ul>'; Edited October 17, 2013 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
astypulk Posted October 17, 2013 Author Share Posted October 17, 2013 Fantastic!!! Can't believe that was that easy. Worked like a charm. Thank you so much Ch0cu3r!!! For future users, the code above had a few small parse errors (marked them in red): // create categoryif(!isset($categories[ $category_id ])){$categories[ $category_id ] = array('title' => $row['Category'],'sub_categories' => array(), // store sub categories as an array);}// add sub categorieselse{$categories[ $category_id ]['sub_categories'][] = array('id' => $row['idSub'],'title' => $row['subCategory'],);}} Quote Link to comment Share on other sites More sharing options...
astypulk Posted October 17, 2013 Author Share Posted October 17, 2013 Hmmm. Actually noticed small problem. The subcategories start from one item down. ex. skips subcategory1 and starts from subcategory2 Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted October 17, 2013 Share Posted October 17, 2013 (edited) Hmmm. Actually noticed small problem. The subcategories start from one item down. ex. skips subcategory1 and starts from subcategory2 Sorry about that. Look at my code again. I did edit it to fix that (I removed the else) Edited October 17, 2013 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
astypulk Posted October 17, 2013 Author Share Posted October 17, 2013 Sweet!!! That definably fixed it! Thank you so much again. Now, I just got to make sure I can replicate it in the future. Cheers! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.