Id like to see you separate your objects.
I would use a school table
[sql]
create table school (schoolID INT NOT NULL AUTO_INCREMENT PRIMARY KEY , schoolName varchar(50));
[/sql]
A player table
[sql]
create table player (playerID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, playerName varchar(50), schoolID int);
[/sql]
A match table
[sql]
create table match (matchID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, playerHomeID int, playerVisitID int);
[/sql]
also please update match table to have a timestamp or other date feature.
Then you should create objects for each of the tables including constructor (for SELECT query) and destructor (for UPDATE query).
class School{
public $schoolID;
public $schoolName;
function __construct($Q_ID){
$sql="SELECT schoolID, schoolName FROM school where schoolID=$Q_ID";
$sqlQuery=mysql_query($sql);
$data=mysql_fetch_assoc($sqlQuery);
$this->schoolID=$data['schoolID'];
$this->schoolName=$data['schoolName'];
}
function __destruct(){
$sql="UPDATE school SET schoolName=$this->schoolName WHERE schoolID=$this->schoolID";
$sqlQuery=mysql_query($sql) or die($sql ."failed");
}
}
now you can create a new school object like this:
$home=new school(1);
You can use your new school object like this
print $home->schoolName;
also once you create your player objects:
$player1=new player(1);
$school1=new school($player1->schoolID);
//assuming you want to keep tabs on the ids of all the schools you create.
Next all you have to do is create objects for the players the same way demonstrated here. (again for this example it looks easier to keep track of the ids of schools on pen and paper rather than build the handling for them)
When you want to create a match, lets say you have this data in your player table and you have built your objects (including the match object):
//Note: these are quasi SQL definitions, that should translate to the php instances, but the naming conventions are more UML, so don't worry about syntax here, this doesnt get entered into any interpreter:
player1 = (id=1, playerName="bob", schoolID=1)
player2 = (id=2, playerName="joe", schoolID=2)
how would you create a row for these two players to compete?
What you want is this right?
match1=(id=AUTOASSIGNED, playerHomeID=1, playerVisitID=2)
so create a function that return the new match object, after executing sql command to create the record. This function exists outside the scope of the classes
function generateMatch($player1, $player2){
$sql= "INSERT INTO matchID (playerHomeID, playerVisitID) VALUES ($player1->playerID, $player2->playerID)";
$sqlResult=mysql_query($sql) or die($sql." failed");
return new Match(mysql_insert_id());
}
So lets recap:
1. you have 3 tables: school, player, match
2. you have 3 objects: school, player, match
3. you have a function for generating matches that returns a match object.
Remember to build the player and match objects in php the same way as the example for the school.
Please post any additional errors you may encounter.
