Pts deduction from league table

[BlueDutch]

I have created a football league table using php and sql and all is fine, except one thing. I need to deduct points from teams who have incurred a points deduction. Would I have to create extra tables in the db or would it be possible to add to the query, deducting points from the query results into the points column?
I hope there is someone out there that can help 😕

[bradgrafelman]

Without showing us any of your code, there's no way anyone can give specific advice, so I'll start off with a general query suggestion...

// assuming $tid = id of team being deducted
// assuming $pts = # of pts to deduct

$query = "UPDATE teams SET points = points - $pts WHERE id = $tid";

[BlueDutch]

Sorry, but stupid forgetting to include the code. I have 2 db´s, one with the teams and one with the results. I tried adding 2 columns to the teams db (qty(tinyint)) for deductions but when adding a "1" to the column it deducted 46 pts as 46 is the number of games played in a season. I can´t reason as to why it picked up on 46 at all. Thank you for your suggestion, I will go give it a whirl. 😉

SELECT

tname AS Team,Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts

FROM(
SELECT

hteam Team,

1 P,
IF(hscore > ascore,1,0) W,
IF(hscore = ascore,1,0) D,
IF(hscore < ascore,1,0) L,
hscore F,
ascore A,
hscore-ascore GD,
CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END PTS
FROM games
UNION ALL
SELECT

ateam,
1,
IF(hscore < ascore,1,0),
IF(hscore = ascore,1,0),
IF(hscore > ascore,1,0),
ascore,
hscore,
ascore-hscore GD,
CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 ELSE 0 END
FROM games
) as tot
JOIN teams t ON tot.Team=t.id

GROUP BY Team
ORDER BY SUM(Pts) DESC, SUM(GD) DESC
LIMIT 0 , 24";
$result = mysql_query($sql);
echo "<table border='0' width=45% align=center>
<tr>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
</tr>";
while ($row = mysql_fetch_array($result)) {
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<tr>";
echo "<td width='165' bgcolor='$row_color' nowrap align='left'><b>" . $row["Team"] . "</b></td>";
echo "<td width='30' bgcolor='$row_color' nowrap align='center'><b>" . $row["P"] . "</b></td>";
echo "<td width='30' bgcolor='$row_color' nowrap align='center'><b>" . $row["W"] . "</b></td>";
echo "<td width='30' bgcolor='$row_color' nowrap align='center'><b>" . $row["D"] . "</b></td>";
echo "<td width='30' bgcolor='$row_color' nowrap align='center'><b>" . $row["L"] . "</b></td>";
echo "<td width='30' bgcolor='$row_color' nowrap align='center'><b>" . $row["F"] . "</b></td>";
echo "<td width='30' bgcolor='$row_color' nowrap align='center'><b>" . $row["A"] . "</b></td>";
echo "<td width='30' bgcolor='$row_color' nowrap align='center'><b>" . $row["Pts"] . "</b></td>";
echo "<td width='30' bgcolor='$row_color' nowrap align='right'><b>" . $row["GD"] . "</b></td>";
$row_count++;
}

echo "</tr>";
echo "</table>";
mysql_close($db);
?>

[BlueDutch]

So far no luck. I haven´t been able to deduct the correct amount with a query. Anyone any suggestions please?