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! Link to comment https://forums.phpfreaks.com/topic/254837-query-tables-help/ 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 Link to comment https://forums.phpfreaks.com/topic/254837-query-tables-help/#findComment-1306764 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 Link to comment https://forums.phpfreaks.com/topic/254837-query-tables-help/#findComment-1306769 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 Link to comment https://forums.phpfreaks.com/topic/254837-query-tables-help/#findComment-1306907 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. Link to comment https://forums.phpfreaks.com/topic/254837-query-tables-help/#findComment-1306921 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.