Mysql Problem

Scene

Apr 14 2008, 04:27 PM

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:

CODE

$sqlOverview	= mysql_query("
SELECT *  
FROM buildings b 
JOIN evolution e ON b.BuildingID = e.BuildingID
JOIN 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.

Comment/Reply (w/o sign-up)

faulty.lee

Apr 14 2008, 06:24 PM

QUOTE(Scene @ Apr 15 2008, 12:27 AM)

CODE

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

All you need to do is use "GROUP BY"

CODE

$sqlOverview	= mysql_query("
SELECT *  
FROM buildings b 
JOIN evolution e ON b.BuildingID = e.BuildingID
JOIN 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

Comment/Reply (w/o sign-up)

Mysql Problem - Linking tables

Mysql Problem - Linking tables