I've included more of the code this time to give more of a clue as to where it may be going wrong. The setting of $id to 8 is NOT what I want to do, but I get a syntax error without it, so I've left it in to eradicate the syntax error. This code is adapted from some working code which picks out a club and lists all its results. With this code I want to loop through each of 238 clubs, and calculate a points allocation based on each if its results, update the database and print one line per club showing those new points. Although it appears to loop through all I get is 238 "blank and zeros" lines so it's not using $id to pick off the club and results in the way I want it to.
Code: Select all
<table border=1 width=90% align=center valign=top>
<tr>
<th>Club (Country)</th>
<th>ID</th>
<th>Balls</th>
<th>Comp</th>
<th>TotDefPts</th>
<th>TotMidPts</th>
<th>TotAttPts</th>
</tr>
<?php
$id=8;
/* Show the club in an HTML table */
function displayClubs($ClubQuery,$cl_id)
{
global $id;
/* Display Club Details */
/* Fetch rows */
while ( $ClubRow = @ mysql_fetch_array($ClubQuery) )
{
echo "\n\t
<tr>
<td align=left><font size=1>$ClubRow[1] ($ClubRow[2])</font></td>
<td align=center><font size=1><b>$ClubRow[0]</b></font></td>
<td align=center><font size=1><b>$ClubRow[3]</b></font></td>
<td align=center><font size=1><b>$ClubRow[7]</b></font></td>";
}
}
/* Show the clubs in an HTML table */
function getResults($ResultQuery,$cl_id)
{
global $totdefpts;
global $totmidpts;
global $totattpts;
$totdefpts = 0;
$totmidpts = 0;
$totattpts = 0;
while ( $ResultRow = @ mysql_fetch_array($ResultQuery) )
{
/* Calculate the points */
/* Defpts */
if ($ResultRow[8] == $cl_id)
{
/* Home defpts */
if ($ResultRow[4] == 0) $defpts = 6;
else ($defpts = (($ResultRow[4]-1) * -2));
/* end Home defpts */
}
else
{
/* Away defpts */
if ($ResultRow[3] == 0) $defpts = 6;
else ($defpts = (($ResultRow[3]-1) * -2));
/* end Away defpts */
}
/* Midpts */
if ($ResultRow[8] == $cl_id)
{
/* Home midpts */
if ($ResultRow[4] == 0) $midptsa = 3;
else ($midptsa = (($ResultRow[4]) * -1));
if ($ResultRow[3] == 0) $midptsf = -1;
else ($midptsf = (($ResultRow[3]) * 2));
$midpts = $midptsf + $midptsa;
/* end Home midpts */
}
else
{
/* Away midpts */
if ($ResultRow[3] == 0) $midptsa = 3;
else ($midptsa = (($ResultRow[3]) * -1));
if ($ResultRow[4] == 0) $midptsf = -1;
else ($midptsf = (($ResultRow[4]) * 2));
$midpts = $midptsf + $midptsa;
/* end Away midpts */
}
/* Attpts */
if ($ResultRow[8] == $cl_id)
{
/* Home attpts */
if ($ResultRow[3] == 0) $attpts = -2;
else ($attpts = (($ResultRow[3]) * 3));
/* end Home attpts */
}
else
{
/* Away attpts */
if ($ResultRow[4] == 0) $attpts = -2;
else ($attpts = (($ResultRow[4]) * 3));
/* end Away attpts */
}
$totdefpts = $totdefpts + $defpts;
$totmidpts = $totmidpts + $midpts;
$totattpts = $totattpts + $attpts;
}
echo " <td align=center><font size=1>$totdefpts</b></td>
<td align=center><font size=1>$totmidpts</b></td>
<td align=center><font size=1>$totattpts</b></td>
</tr>";
}
/* define sql error handler */
function SqlErrorHandler()
{
die ( "Error " . mysql_errno() . " : " . mysql_error() );
}
/* Run the club query */
if ( ! ( $ClubQuery = @ mysql_query ( "
SELECT cl.clubid,
cl.clubname,
cy.country_name,
cl.balls,
cl.defpts,
cl.midpts,
cl.attpts,
co.competition
FROM club cl,
country cy,
comps co
WHERE
cl.clubid = $id
and cl.country = cy.country
and cl.eurocomp = co.compid
" ,
$conn ) ) )
SqlErrorHandler();
/* Run the club results query */
if ( ! ( $ResultQuery = @ mysql_query ( "
SELECT res.date,
cl1.clubname,
co1.country_name,
res.score1,
res.score2,
cl2.clubname,
co2.country_name,
com.competition,
cl1.clubid
FROM club cl1,
club cl2,
results res,
country co1,
country co2,
comps com
where
(
cl1.clubid = $id
and res.clubid1 = cl1.clubid
and res.clubid2 = cl2.clubid
and co1.country = cl1.country
and co2.country = cl2.country
and res.compid = com.compid
)
or
(
cl2.clubid = $id
and res.clubid1 = cl1.clubid
and res.clubid2 = cl2.clubid
and co1.country = cl1.country
and co2.country = cl2.country
and res.compid = com.compid
)
order by res.date
" ,
$conn ) ) )
SqlErrorHandler();
for($id=8; $id<9; $id++)
{
print("id = " . $id);
displayClubs($ClubQuery,$id);
getResults($ResultQuery,$id);
$u = "UPDATE club
SET
defpts = " . $totdefpts . ",
midpts = " . $totmidpts . ",
attpts = " . $totattpts . "
WHERE clubid = " . $id
;
if(!(@ mysql_query ($u, $conn)))
SqlErrorHandler();
}
/* Close the connection to MySQL */
if ( ! ( mysql_close ( $conn ) ) )
SqlErrorHandler();
?>