Jump to content

Query Tables Help


mortalkiller

Recommended Posts

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

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

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 :P ).

 

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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.