473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

PHP MySQL Queries

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>&nbsp;<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($query) 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_object($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_shots" value="<? echo
$row->home_shots; ?>"></td>
</tr>
<tr>
<td valign="top"><b><? echo $row->away_team; ?></b></td>
<td><input size="5" name="away_shots" 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><input 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($errorList) == 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($querymatches) or die ("Error in query:
$query. " . mysql_error());
$result2 = mysql_query($queryhome) or die ("Error in query: $query.
" . mysql_error());
$result3 = mysql_query($queryaway) or die ("Error in query: $query.
" . mysql_error());

// print result
echo "<font size=-1>Update successful. <a href=list.php>Go back to
the main menu</a>.</font>";

// close database connection
mysql_close($connection);
}
else
{
// errors occurred
// print as list
echo "<font size=-1>The following errors were encountered: <br>";
echo "<ul>";
for ($x=0; $x<sizeof($errorList); $x++)
{
echo "<li>$errorList[$x]";
} echo "</ul></font>";
}
}
?>

<!-- standard page footer begins -->
<p>
<table width="100%" cellspacing="0" cellpadding="5">
<tr>
<td align="center">&nbsp;</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;
Jul 17 '05 #1
0 1371

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary...
0
by: Philip Stoev | last post by:
Hi all, Please tell me if any of this makes sense. Any pointers to relevant projects/articles will be much appreciated. Philip Stoev http://www.stoev.org/pivot/manifest.htm ...
0
by: Plymouth Acclaim | last post by:
Hi guys, We have a problem with Dual AMD64 Opteron/MySQL 4.0.18/Mandrake 10 for a very high volume site. We are evaluating the performance on our new server AMD64 and it seems it's slow compared...
11
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time...
39
by: Mairhtin O'Feannag | last post by:
Hello, I have a client (customer) who asked the question : "Why would I buy and use UDB, when MySql is free?" I had to say I was stunned. I have no experience with MySql, so I was left sort...
7
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both...
1
by: PowerLifter1450 | last post by:
I've been having a very rough time installinig mySQL on Linux. I have been following the instructions form here: http://www.hostlibrary.com/installing_apache_mysql_php_on_linux Everytime I get to...
4
by: Federico | last post by:
Hi everybody, I'm evaluating the possibility of using MySQL 5.0 as a database backend for an application we are doing and I'd like to have hardware requirements rough estimates and/or real world...
13
by: Ciaran | last post by:
Hi All, Is it faster to have mySql look up as much data as possible in one complex query or to have php do all the complex processing and submit lots of simple queries to the mysql database? ...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.