Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Timo Bontenbal
Timo Bontenbal
9,582 Points
Posted by Timo Bontenbal
Timo Bontenbal
9,582 Points

PHP Database structure.

Hi,

I need to build a quite simple php site. The site needs this kind of functionality.

Admin area: add Teams(only the name) and points for teams. Each team can get multiple points. Example: Team1 -> Points 4/10, 7/10 Total score 11/20.

Regular view: If you visit the site people can see how many points each team has gotten.

My question: What kind of database structure should i build? On the admin area an admin can choose a team and then add points to it. Points will be added every week and the site visitors need to see them.

How do you recommend me to start building this? I know this is quite simple. but i cant wrap my head around the database structure.

I am using PHP MyAdmin

Code examples are also welcome :)

Simon Woodard
Simon Woodard
6,545 Points
Simon Woodard
Simon Woodard
6,545 Points

Do you want old database entries to be kept? or do you only want the latest scores listed in the database?

Timo Bontenbal
Timo Bontenbal
9,582 Points
Timo Bontenbal
Timo Bontenbal
9,582 Points

Simon Woodard The old entries need also to been seen

Chris Malcolm
Chris Malcolm
2,909 Points
Chris Malcolm
Chris Malcolm
2,909 Points

if your asking about the columns and tables would probably do:

team table

id, name -string, timestamp -timestamp

points table

id, points, user_id -relation, team_id -relation

user table

id, name -string ,password -encrypted string, email -string, admin -bool, timestamp

Timo Bontenbal
Timo Bontenbal
9,582 Points
Timo Bontenbal
Timo Bontenbal
9,582 Points

Chris Malcolm I was researching today a little of relational tables. But i did not find a clear answer on how they work.

Chris Malcolm
Chris Malcolm
2,909 Points
Chris Malcolm
Chris Malcolm
2,909 Points

in essence your relation is just the id of user or team and so you have a means of grabbing the team or user in your sql query. You can also join table data together, using JOIN http://www.w3schools.com/sql/sql_join.asp

Timo Bontenbal
Timo Bontenbal
9,582 Points
Timo Bontenbal
Timo Bontenbal
9,582 Points

Chris Malcolm Does the id need to be a primary A.I integer?

Chris Malcolm
Chris Malcolm
2,909 Points
Chris Malcolm
Chris Malcolm
2,909 Points

hi timo, yes id should be that.

2 Answers

Simon Woodard
Simon Woodard
6,545 Points
Simon Woodard
Simon Woodard
6,545 Points

You could get away with 2 simple table's in that case. One that lists all the team name with these columns (id - unique identifyer) (team - name of the team)

and then a second table that list all the scores (id - unique identifyer) (team_id - the team this entry corresponds to) (score - the score for this entry) (timestamp - the date of entry)

With this two table database you can call any score based on the date and go back as far as you want, and with unique id's you can display multiple scores for that day and then the total of them as well.

Timo Bontenbal
Timo Bontenbal
9,582 Points
Timo Bontenbal
Timo Bontenbal
9,582 Points

Simon Woodard Are those relational tables hard? How would i set records to them?

Simon Woodard
Simon Woodard
6,545 Points
Simon Woodard
Simon Woodard
6,545 Points

Nope they're not hard at all, when you enter a score into the table you would simply use the id of the team as the relationship, since this would be the common denominator in both tables.