Mysql Problem Linking tables

[Scene 0]

Hello guys,

 

I'm making a PHP/MySQL site for a friend of mine who plays an online game where you can create towns, add buildings to them and upgrade them to a newer level.

 

I've offered to make him a small site where he can input his data into a database and keep track of his towns, buildings and levels.

 

I'll show you my database design and then explain what i want to do.

 

Table: towns

 

field 1: TownID (primary key, auto-increment)

field 2: TownName

field 3: TownType

field 4: Town Description

 

Table: buildings

 

field 1: BuildingID (primary key, auto-increment)

field 2: BuildingName

field 3: BuildingType

field 4: BuildingDescription

 

Table: evolution

 

field 1: EvolutionID (primary-key, auto-increment)

field 2: TownID (foreign key,) / link to TownID in towns table

field 3: BuildingID (foreign key) / link to BuildingID in buildings table

field 4: Level / current building level

 

In essence i want to use the evolution table to combine the previous tables, and generate an overview showing the buildings each town has and on what level these buildings are.

 

My current SQL query is:

 

$sqlOverview	= mysql_query("SELECT *  FROM buildings b JOIN evolution e ON b.BuildingID = e.BuildingIDJOIN towns t ON e.TownID = t.TownID");

Which gives me the following output:

 

 

As you can see it prints every building in a separate HTML table,

what i'd like is that each building that is linked to a town to be shown in the same table.

 

My guess is my SQL query is causing this but i can't figure out how i can resolve this..

 

Any help would be appreciated,

 

Kind regards,

Scene

 

PS. english is not my first language, if i dont make sense or additional explaining is required please let me know.

[faulty.lee 0]

$sqlOverview	= mysql_query("SELECT *  FROM buildings b JOIN evolution e ON b.BuildingID = e.BuildingIDJOIN towns t ON e.TownID = t.TownID");

All you need to do is use "GROUP BY"

$sqlOverview	= mysql_query("SELECT *  FROM buildings b JOIN evolution e ON b.BuildingID = e.BuildingIDJOIN towns t ON e.TownID = t.TownID")GROUP BY t.TownID;

I do have a bit of suggestion on your table structure. Since all your evolution are tied one-to-one to your building, you might as well just store the level at the building's table.

Table: buildings

field 1: BuildingID (primary key, auto-increment)
field 2: BuildingName
field 3: BuildingType
field 4: BuildingDescription
* Additional
field 5: TownID (foreign key,) / link to TownID in towns table
field 6: Level / current building level


Unless of cause you want to store every evolution that building has been through, then you need to fetch the latest evolution from your table. When you start using group by, not all the data that falls under the same group is shown. Normally group by are used for counting subtotal of each groups.

NOTE : I just notice that you want to show multiple tables of town, with buildings in each town's table. That you'll need to run your query for towns, and generate each table from there, while looping through, do another query to fetch buildings for that particular town, and display into the table.

-> Loop through each town
-> Loop through each building of that town

JOIN is to combine and tabulate everything in one table