Please Help! Trying to create a football league system

[aaarrrggh]

Hello there!

I'm pretty new to php, and have been learning the basics over the last few weeks. I'm now trying to build a site for a football (soccer) game that allows users to create their own league system once they have registered.

The problem I am having relates to the way I store information about the leagues in the MYSQL database. It's the first time I've really tried using MYSQL for anything slightly complex, and I'm struggling to get to grips with it at the moment.

At the moment, I've set it up so I've got the following tables:

CREATE TABLE assigned_leagues (
user_id INTEGER(10) UNSIGNED NULL,
league_id INTEGER UNSIGNED NULL,
INDEX assigned_leagues_index(user_id, league_id)
);

CREATE TABLE leagues (
league_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
league_admin_user_id INTEGER(10) UNSIGNED NOT NULL,
name VARCHAR(32) NULL,
created_on TIMESTAMP NULL,
capacity INTEGER UNSIGNED NULL,
active INTEGER UNSIGNED NULL,
PRIMARY KEY(league_id),
INDEX leagues_index(league_admin_user_id)
);

CREATE TABLE reg (
reg_id INTEGER(10) UNSIGNED NOT NULL,
des_username VARCHAR(20) NOT NULL,
pword VARCHAR(32) NOT NULL,
email VARCHAR(75) NOT NULL,
confirm VARCHAR(32) NULL,
PRIMARY KEY(reg_id)
);

CREATE TABLE super_league_1 (
league_id INTEGER UNSIGNED NOT NULL,
user_id INTEGER(10) UNSIGNED NOT NULL,
team VARCHAR(32) NULL,
played INTEGER UNSIGNED NULL,
won INTEGER UNSIGNED NULL,
drew INTEGER UNSIGNED NULL,
lost INTEGER UNSIGNED NULL,
goals_for INTEGER UNSIGNED NULL,
goals_against INTEGER UNSIGNED NULL,
goal_difference INTEGER UNSIGNED NULL,
points INTEGER UNSIGNED NULL,
season INTEGER UNSIGNED NULL,
PRIMARY KEY(league_id)
);

CREATE TABLE users (
user_id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
pword VARCHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL,
priv INTEGER(10) UNSIGNED NULL,
reg_date TIMESTAMP NULL,
PRIMARY KEY(user_id)
);

CREATE TABLE user_stats (
iduser_stats INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INTEGER(10) UNSIGNED NOT NULL,
overall_games_played INTEGER UNSIGNED NULL,
overall_games_won INTEGER UNSIGNED NULL,
overall_games_lost INTEGER UNSIGNED NULL,
overall_goals_scored INTEGER UNSIGNED NULL,
overall_goals_conceded INTEGER UNSIGNED NULL,
best_winning_run INTEGER UNSIGNED NULL,
worst_losing_run INTEGER UNSIGNED NULL,
biggest_win INTEGER UNSIGNED NULL,
biggest_defeat INTEGER UNSIGNED NULL,
leagues_won INTEGER UNSIGNED NULL,
cups_won INTEGER UNSIGNED NULL,
PRIMARY KEY(iduser_stats),
INDEX user_stats_index1557(user_id)
);

I'm currently trying to do this so that when a user selects 'create new league' inside the site, PHP will create a new table to represent that league inside MYSQL. The 'Super_league_1' is an example of what these tables will look like.

The main thing I want to work out at the moment is the way I should be sorting my information in mysql. I've got a feeling that I'm making things a lot harder for myself than I need to, but I'm not sure how to rectify this. Should I have to create a new table in mysql when users create a new league table, or should I be able to do this another way?

Any other advice would be really well appreciated. I'm new to PHP, but I've enjoyed doing this so far, and I'd love to learn any better techniques from people out there that are more experienced than I am.

You can view what I've done so far on the site at a temporary address here: www.itr.org.uk/home.php

The registration system is up and running now :)

Please help if you can!

[aaarrrggh]

I'm also going to have to have a fixture list, that records all games played, the results of each match, and makes sure that people dont play eachother more than two times in any given season.

Can anybody give me any advice?

[aaarrrggh]

Nobody able to help??

[redbullmarky]

[!--quoteo(post=358931:date=Mar 27 2006, 05:32 PM:name=aaarrrggh)--][div class=\'quotetop\']QUOTE(aaarrrggh @ Mar 27 2006, 05:32 PM) [snapback]358931[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Nobody able to help??
[/quote]

to be honest mate, it's a hefty project. and whilst it may not be that hard to do, it's a mixture of god-knows-how-many different things that all need looking at seperately.

best bet? have a crack at it yourself and see where it takes you, as you're asking quite alot in one go...

good luck

Mark

[aaarrrggh]

Yeah, I suppose. I've taken on quite a big project really seeing as it's the first time I've ever done anything like this.

The main thing I really need to know is how I can allow users to create new league tables using php/mysql. I've thought about this, and I think it might be possible to have one table with information about what league people are in and the name of the leauge, and another table that holds all the points information etc.

I just don't know whether this is a good way of doing it or not, like, will I find the site slows down a lot when I get a larger number of people creating leagues on there?

I'm also going to have to work out the fixture list, which I can already see is going to cause me headaches...

[redbullmarky]

[!--quoteo(post=358953:date=Mar 27 2006, 06:02 PM:name=aaarrrggh)--][div class=\'quotetop\']QUOTE(aaarrrggh @ Mar 27 2006, 06:02 PM) [snapback]358953[/snapback][/div][div class=\'quotemain\'][!--quotec--]
The main thing I really need to know is how I can allow users to create new league tables using php/mysql. I've thought about this, and I think it might be possible to have one table with information about what league people are in and the name of the leauge, and another table that holds all the points information etc.
I just don't know whether this is a good way of doing it or not, like, will I find the site slows down a lot when I get a larger number of people creating leagues on there?
[/quote]

a couple of tutorials may help here. the only good way for a new starter is the way that works. by the time your site builds up its popularity, you'll have built up your expertise to be able to make the correct judgements and adjustments to your scripts.

[!--quoteo(post=358953:date=Mar 27 2006, 06:02 PM:name=aaarrrggh)--][div class=\'quotetop\']QUOTE(aaarrrggh @ Mar 27 2006, 06:02 PM) [snapback]358953[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I'm also going to have to work out the fixture list, which I can already see is going to cause me headaches...
[/quote]

this, believe it or not, will be the easy task. the first clue is that the working out has nothing to do with PHP, and the good news is that the code to work out the fixtures is (or should be) relatively easy compared to some of the other bits youre looking at.

[Samad]

I think the only way to do that is to create a table for each user,
you can specify table name to member's id so server would work faster and more reliable,also let user's to create just one table from a pc so you're dbs would be much,You can achieve this via storing member's ip address while creating a table for first time,and for next times check if this ip is stored on you database or not,if not let visitor to create a table,
[quote]
The main thing I really need to know is how I can allow users to create new league tables using php/mysql.
[/quote]
through a simple script,nothing much! grab informations and create a new table with leagues info.

Samad
For more info contact me:
SamadBox@gmail.com

[redbullmarky]

[!--quoteo(post=358966:date=Mar 27 2006, 06:32 PM:name=Samad)--][div class=\'quotetop\']QUOTE(Samad @ Mar 27 2006, 06:32 PM) [snapback]358966[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I think the only way to do that is to create a table for each user,
you can specify table name to member's id so server would work faster and more reliable,also let user's to create just one table from a pc so you're dbs would be much,You can achieve this via storing member's ip address while creating a table for first time,and for next times check if this ip is stored on you database or not,if not let visitor to create a table,

through a simple script,nothing much! grab informations and create a new table with leagues info.

Samad
For more info contact me:
SamadBox@gmail.com
[/quote]

?

with all due respect:
a) i think you've overcomplicated the problem
b) you've introduced things such as IP addresses which aren't necessary and just make it even more complicated.
c) what if aaarrrggh has a few hundred or a few thousand users? are you suggesting that he should have a complete new table for each user?
d) isn't posting your email address taking the "post a request for help/recieve help+suggestions" idea away from phpfreaks?

apologies to everyone if i seem a little narky and Samad if i've got you wrong, but surely keeping things as simple as possible is the best way?

[aaarrrggh]

Well I think I might have got it now. Not sure, but it's looking that this so far in the database designer program I'm using:

[img src=\"http://img147.imageshack.us/img147/5043/ploppy4hj.png\" border=\"0\" alt=\"IPB Image\" /]