mortalkiller Posted January 11, 2012 Share Posted January 11, 2012 Hello to everybody! I'm developing a beach guid web site, and I need all beach spot have a Country, Region, SubRegion and Spot And I want display this in a <ul> hierarchy. I Create 4 tables TABLE coutry idCoutry name TABLE region idRegion idCoutry name TABLE subregion idSubRegion idRegion name TABLE beach idBeach idSubRegion name I would like display this values like that - Country - Region -SubRegion -Beach -Beach -Beach -Beach -Beach -SubRegion -Beach -Beach -Beach -Beach -Beach - Country - Region -SubRegion -Beach -Beach -Beach -Beach -Beach -SubRegion -Beach -Beach -Beach -Beach -Beach etc. I can do this with multi query but if I have 4000 Beachs the script made many query's If some can help me! Thanks! Quote Link to comment Share on other sites More sharing options...
xtremey_ytinasni Posted January 12, 2012 Share Posted January 12, 2012 The only problem for me to not be able to do this, is that you have a column in each table with the name (name). Thus, with LEFT JOIN, i don't think you could seperate the values for each one, or maybe I'm wrong... but if you changed it to say like: cName,rName,srName,and bName, it'd look something around this ( for the query, the html I'm not good with unordered lists, so shouldn't be too hard to fix any mistake ): <?php //Your connection stuff here $mysql = 'SELECT c.`idCountry`,c.`cName`,r.`idRegion`,r.`idCountry`,r.`rName`,sr.`idSubRegion`,sr.`idRegion`,sr.`srName`,b.`idBeach`,b.`idSubRegion`,b.`bName` '. 'FROM `country` c '. 'LEFT JOIN `region` r '. 'ON c.`idCountry`=r.`idCountry` '. 'LEFT JOIN `subregions` sr '. 'ON r.`idRegion`=sr.`idRegion` '. 'LEFT JOIN `beach` b '. 'ON sr.`idSubRegion`=b.`idSubRegion` '. 'GROUP BY c.`idCountry` '; //Multiple Left joins. Feel free to add an ORDER BY, if you'd like. $q = mysql_query($mysql,$conn_id); while($data = mysql_fetch_assoc($q)){ echo '<ul>'.$data['cName']; echo '<li><ul>'.$data['rName']; echo '<li><ul>'.$data['srName']; echo '<li><ul>'.$data['bName']; echo '</ul></li></ul></li></ul></li></ul>'; } ?> NOTE: This has not been tested, and I've never worked with more than 2 left joins, but I really do hope it works for you. The only thing I am worried about this is that it may not group everything correctly, but it should... If it works, awesome Quote Link to comment Share on other sites More sharing options...
xtremey_ytinasni Posted January 12, 2012 Share Posted January 12, 2012 I have no idea how to edit a post on this forum, but anyway, I just did a test on the little thing I wrote for you... Short: It works. I'm sure it could be advanced, but you asked for it, and I gave you the solution ( hopefully I understood ). EDIT: Wait... It's not displaying multiple beaches. One sec Quote Link to comment Share on other sites More sharing options...
mortalkiller Posted January 12, 2012 Author Share Posted January 12, 2012 Man feel free to change table columns if you think better, And forget the table country The solution that you gave me write <ul> inside <ul> my menu works like that, <ul> <li class="mega"> <h2><a href="#">REGION</a></h2> <div> <h3>SUBREGION</h3> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> // If 5 beach's displayed make a <br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a> <h3>SUBREGION</h3> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> // If 5 beach's displayed make a <br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a> // if end in 5 beach's not make a <br> </div> </li> <li class="mega"> <h2><a href="#">REGION</a></h2> <div> <h3>SUBREGION</h3> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> // If 5 beach's displayed make a <br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a> <h3>SUBREGION</h3> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> // If 5 beach's displayed make a <br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a> // if end in 5 beach's not make a <br> </div> </li> <li class="mega"> <h2><a href="#">REGION</a></h2> <div> <h3>SUBREGION</h3> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> // If 5 beach's displayed make a <br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a> <h3>SUBREGION</h3> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a><br> // If 5 beach's displayed make a <br> <a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a>,<a>Beach</a> // if end in 5 beach's not make a <br> </div> </li> </ul> Ist's some like that, if you could help, I would be eternally grateful! Thanks Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted January 12, 2012 Share Posted January 12, 2012 Drop the "group by" at the end of that query, you want to pull all the results out: but only display them in a specific way. You are going to need to code some conditional output into the while loop. Using if's to check if the countryid has changed, then if the sub region id has changed in oreder to format what you output to the screen. Have a bash at it and let us know if you have any problems. 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.