Is this possible?
I have a form which asks the user to input the result of a soccer
match.
I want to then use a choice of three sets of queries (Home Win, Away
Win, Draw) to update two tables, Matches (the master list of all
matches and there results) and Teams (a list of all teams with total
numbers of matches played, won, lost drawn etc?)
So far I have managed to update the table matches but nothing happens
to teams. The page appears to run correctly so I am at a loss to
understand what is happening.
PHP AS IT STANDS
<?
// scoreinput.php - input a match score when match selected from list
?>
<html>
<head>
<basefont face="Verdana">
</head>
<body>
<!-- standard page header begins -->
<p> <p>
<table width="100%" cellspacing="0" cellpadding="5" >
<tr>
<td></td>
</tr>
<tr>
<td bgcolor="Red">< font size="-1" color="White">< b>Cornwall County
Short Mat Bowling Association : Administration :
League Results : Edit</b></font></td>
</tr>
</table>
<!-- standard page header ends -->
<p>
<?
// includes
include("../conf.php");
include("../functions.php") ;
// form not yet submitted
// display initial form with values pre-filled
if (!$submit)
{
// open database connection
$connection = mysql_connect($ host, $user, $pass) or die ("Unable to
connect!");
// select database
mysql_select_db ($db) or die ("Unable to select database!");
// generate and execute query
$query = "SELECT * FROM matches WHERE id = '$id'";
$result = mysql_query($qu ery) or die ("Error in query: $query. " .
mysql_error());
// if a result is returned
if (mysql_num_rows ($result) > 0)
{
// turn it into an object
$row = mysql_fetch_obj ect($result);
// print form with values pre-filled
?>
<table cellspacing="5" cellpadding="5" >
<form action="<? echo $PHP_SELF; ?>" method="POST">
<input type="hidden" name="id" value="<? echo $row->id; ?>">
<tr>
<td valign="top"><b >Date<b></td>
<td><? echo $row->date; ?></td>
</tr>
<tr>
<td valign="top"><b ><? echo $row->home_team; ?></b></td>
<td><input size="5" name="home_shot s" value="<? echo
$row->home_shots; ?>"></td>
</tr>
<tr>
<td valign="top"><b ><? echo $row->away_team; ?></b></td>
<td><input size="5" name="away_shot s" value="<? echo
$row->away_shots; ?>"></td>
</tr>
<tr>
<td valign="top"><b >Match Completed</b></td>
<td><input type="checkbox" name="completed " value="Y"></td>
</tr>
<tr>
<td colspan=2><inpu t type="Submit" name="submit" value="Update"> </td>
</tr>
</form>
</table>
<?
}
// no result returned
// print graceful error message
else
{
echo "<font size=-1>That match could not be located in our
database.</font>";
}
}
// form submitted
// start processing it
else
{
// set up error list array
$errorList = array();
$count = 0;
// check for errors
// if none found...
if (sizeof($errorL ist) == 0)
{
// open database connection
$connection = mysql_connect($ host, $user, $pass) or die ("Unable to
connect!");
// select database
mysql_select_db ($db) or die ("Unable to select database!");
// generate and execute query
if ($home_shots > $away_shots) {
$querymatches = "UPDATE matches
SET home_shots = '$home_shots',
away_shots = '$away_shots',
completed = '$completed' WHERE id = '$id'";
$queryhome = "UPDATE teams
SET played = played + 1,
won = won + 1,
sf = sf + '$home_shots',
sa = sa + '$away_shots',
points = points + 2 WHERE team = '$home_team'";
$queryaway = "UPDATE teams
SET played = played + 1,
lost = lost + 1,
sa = sa + '$home_shots',
sf = sf + '$away_shots' WHERE team = '$away_team'";
} elseif ($home_shots < $away_shots) {
$querymatches = "UPDATE matches
SET home_shots = '$home_shots',
away_shots = '$away_shots',
completed = '$completed' WHERE id = '$id'";
$queryaway = "UPDATE teams
SET played = played + 1,
won = won + 1,
sa = sa + '$home_shots',
sf = sf + '$away_shots',
points = points + 2 WHERE team = '$away_team'";
$queryhome = "UPDATE teams
SET played = played + 1,
lost = lost + 1,
sf = sf + '$home_shots',
sa = sa + '$away_shots' WHERE team = '$home_team'";
} else {
$querymatches = "UPDATE matches
SET home_shots = '$home_shots',
away_shots = '$away_shots',
completed = '$completed' WHERE id = '$id'";
$queryhome = "UPDATE teams
SET played = played + 1,
drawn = drawn + 1,
sf = sf + '$home_shots',
sa = sa + '$away_shots',
points = points + 1 WHERE team = '$home_team'";
$queryaway = "UPDATE teams
SET played = played + 1,
drawn = drawn + 1,
sa = sa + '$home_shots',
sf = sf + '$away_shots',
points = points + 1 WHERE team = '$away_team'";
}
$result1 = mysql_query($qu erymatches) or die ("Error in query:
$query. " . mysql_error());
$result2 = mysql_query($qu eryhome) or die ("Error in query: $query.
" . mysql_error());
$result3 = mysql_query($qu eryaway) or die ("Error in query: $query.
" . mysql_error());
// print result
echo "<font size=-1>Update successful. <a href=list.php>G o back to
the main menu</a>.</font>";
// close database connection
mysql_close($co nnection);
}
else
{
// errors occurred
// print as list
echo "<font size=-1>The following errors were encountered: <br>";
echo "<ul>";
for ($x=0; $x<sizeof($erro rList); $x++)
{
echo "<li>$error List[$x]";
} echo "</ul></font>";
}
}
?>
<!-- standard page footer begins -->
<p>
<table width="100%" cellspacing="0" cellpadding="5" >
<tr>
<td align="center"> </td>
</tr>
</table>
<!-- standard page footer ends -->
</body>
</html>
TABLE STRUCTURE
CREATE TABLE `matches` (
`id` int(11) NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`home_team` text NOT NULL,
`away_team` text NOT NULL,
`home_shots` int(11) NOT NULL default '0',
`away_shots` int(11) NOT NULL default '0',
`league` text NOT NULL,
`completed` text,
PRIMARY KEY (`id`)
) TYPE=MyISAM;
CREATE TABLE `teams` (
`id` int(11) NOT NULL auto_increment,
`league` text NOT NULL,
`team` text NOT NULL,
`played` int(11) NOT NULL default '0',
`won` int(11) NOT NULL default '0',
`drawn` int(11) NOT NULL default '0',
`lost` int(11) NOT NULL default '0',
`sf` int(11) NOT NULL default '0',
`sa` int(11) NOT NULL default '0',
`bonus` int(11) NOT NULL default '0',
`points` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;
