Query Tables Help

[mortalkiller]

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!           

[xtremey_ytinasni]

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

[xtremey_ytinasni]

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

[mortalkiller]

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

[Muddy_Funster]

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.