Need help ! How Do I...

[srhino]

I am trying to create a database that has schools and players on their tennis teams. I want to match up the teams and their players. How can I take the variables and seperate them so I can match up players from one school against the other.

 

Here are my variables

$matchid = $row['matchid'];
$schoolid = $row['schoolid'];
$schoolname = $row['schoolname'];
$schoolabb = $row['schoolabb'];
$d1 = $row['d1'];
$d2 = $row['d2'];
$d3 = $row['d3'];
$s1 = $row['s1'];
$s2 = $row['s2'];
$s3 = $row['s3'];
$s4 = $row['s4'];
$s5 = $row['s5'];
$s6 = $row['s6'];

 

each of the "d" variables and the "s" variables are players names on the team. I would like to insert the team into a form so that d1 on one team  would play d1 on the other team.

 

Please help!

[nafetski]

Depends on your database schema.  Post that, and I'll write a query and explain what's going on

[srhino]

sorry, I am stupid! I am new at this and trying to learn as I go along...What do you mean by the database schema?

[srhino]

I attached a picture of how I have my database set up

 

[attachment deleted by admin]

[srhino]

Can anyone point me in the right Direction?

 

Thanks

[srhino]

Should I use a foreach loop ?

[andrewgauger]

Id like to see you separate your objects.

 

I would use a school table

create table school (schoolID INT NOT NULL AUTO_INCREMENT PRIMARY KEY , schoolName varchar(50));

 

A player table

 

create table player (playerID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, playerName varchar(50), schoolID int);

 

A match table

 

create table match (matchID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, playerHomeID int, playerVisitID int);

 

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.

[srhino]

andrewgauger,

    Thank you for your help. That is getting me in the right direction. This is for a tennis tournament. Each "match" consists of 9 inner matches.

d1 stands for doubles one and s1 stands for singles one....etc I have attached 2 pictures of the webpage output I am trying to construct. The display now is fine... but much like march madness it is a bracket tournament. Each day is different matchups and for now we have to input the players and the schools each time a new round starts. I am trying to make it more effecient so we only have to input the teams and the players once.

 

  So from what you put together...it would probably be better to create the teams seperate and the players seperate and give the players two id's a singles id and a doubles id ?

 

 

 

[attachment deleted by admin]

[andrewgauger]

Doubles can extend the match.  What you can do is have two match ids relate to a single match for doubles but that would require another table to keep track of the paired matches.  This is the way I'd go about it:

 

You wouldn't be loosing too much overhead to put a 5 column table together for matches.  I didn't realize the doubles contigency(shows how much I know about tennis)

 

Modify Match table and match object to have 4 players (matchID, homePlayer1, homePlayer2, vistPlayer1, visitPlayer2, tournamentID)

 

Put requirements to exist on homePlayer1& visitPlayer1 in the constructor. 

 

When you query the match, if there is no value in homePlayer2 or visitPlayer2 then you know that this is a single match--no need for a designator.

 

I also forgot to tell you to make a tournament table that keeps track of:

1. which teams/schools are playing each other

2. matches need a tournament ID so they know which event the took place in

 

Tournament=(tournamentID=AUTONUMBER, homeSchool=int, visitSchool=int)

 

So after you have put the application in a state of building a tournament (ie building a tournament entry with two schools and passing the data to another page) your original question (how to build a match visually) can be answered as follows, this example is in the tournament class:

function displayMatchBuilder(){
$homeSchoolPlayers=array();
$visitSchoolPlayers=array();

$sql="SELECT PlayerID FROM Player where SchoolID=$this->homeSchool";
$sqlquery=mysql_query($sql);

echo "<select name=\"Home\">";


$x=0;
while($data=mysql_fetch_array($sqlquery))
{
$homeSchoolPlayers[$x]=new Player($data[0]);
printPlayer($homeSchoolPlayers[$x]);
}
echo "</select>";

$sql="SELECT PlayerID FROM Player where SchoolID=$this->vistSchool";
$sqlquery=mysql_query($sql);

echo "<select name=\"Visit\">";

$x=0;
while($data=mysql_fetch_array($sqlquery))
{
$visitSchoolPlayers[$x++]=new Player($data[0]);
printPlayer($homeSchoolPlayers[$x]);
}

echo "</select>";

This php exists outside the scope of the class:

function printPlayer($player)
{
echo "<option value=\"$player->playerID\">$player->playerName";
}

 

For your recent question:

give the players two id's a singles id and a doubles id ?

 

There would be two matches:

1. match where the player played a singles match

2. match where the player played a doubles match

 

In this code there is no way for the selects to know if the player was already assigned but you can research how to modify the where with a join.