[SOLVED] Trigger Saviour or Still Can't Do Simple Task

[grrraaagh]

Hi,

 

Please forgive me for any laughable questions i pose, i am a relative newcomer to any sort of coding. Although i'm not totally ignorant of such things, as it only took me a couple of minutes of trial and error to work out how to have a formula do what i wished in Excel and Microsoft works.

 

However, i have spent hours of frustration trying to get php and mysql to do what i would consider is a relatively simple task for even the most basic of computer programmes. I was relieved recently when i - hopefully correctly - gathered that it was not actually possible until the recent addition of Triggers to attain my wish.

 

All i want to do, which took minutes to arrange in Excel, Microsoft Works and on a stand-alone PHP page is to be able to publish a sports league table whereby the points total for a team is automatically calculated. For example if you gain 3 points for a win and one for a draw a formula similar to pts = w*3 + d works on everything else i have tried.

 

I've spent ages again trying to get the new triggers to work on this and i can't. I don't know if it is because of basic syntax mistakes, or just as likely the wrong placement of it, or indeed whether it is yet another dead end for my hopes in that the code will not accomplish what i want to do. I'm now at such a confused state i can't even figure out what goes where within the scripts on the webpage that creates the table and within the database fields.

 

I've always praised and promoted both MySql and PHP, but am ready to abndon them altogether if i'm unable to achieve such a simple task. I don't know if it is my own denseness at being unable to grasp the terminology within the documentation as a newcomer that is the problem.

 

Please could someone help me with my simple aim of having the points column in a sports table automatically generate it's total from the fields of wins and draws. I don't have any problems with a PHP form page where i enter into it the played, won, drawn, and lost fields, and then upon pressing the submit button it returns with an additional column of the computed points total. Why can't i get PHP and MySql database to produce the same?.

 

I am using PHP 5.0.2 and MySql 5.0

 

[bubblegum.anarchy]

When do you want the calculation to occur?

[grrraaagh]

Sorry if this is a stupid answer to your question. I wish the table to appear when i go to the webpage that it is on.

 

This is the script that i am presently using which produces the table on the webpage. As i say, the only problem is that i have to manually calculate the points total for each team and then type them in within the database admin interface.

 

<?php
$conn=@mysql_connect("localhost", "user", "password") or die("could not connect");
$rs=@mysql_select_db("database", $conn) or die("could not connect to database");
?>


<tr>
<td valign=top  width="50%">
<h3><a name="all"><font color="#ff0000">ALL TEAMS</font></a></h3>

<?php
$sql=stripslashes ("select * from standings order by pts desc, w desc");
$rs=mysql_query($sql, $conn) or die("could not execute query because ".mysql_error());
$list="<table border=\"1\" cellpadding=\"2\">";
$list.="<tr><th>C</th>";
$list.="<th>LG</th>";
$list.="<th>TEAM</th>";
$list.="<th>P</th>";
$list.="<th>W</th>";
$list.="<th>L</th>";
$list.="<th>T</th>";
$list.="<th>OL</th>";
$list.="<th>GF</th>";
$list.="<th>GA</th>";
$list.="<th>PTS</th></tr>";
while($row=mysql_fetch_array($rs))
{
$list.="<tr>";
$list.="<td>".$row["c"]."</td>";
$list.="<td>".$row["lg"]."</td>";
$list.="<td>".$row["team"]."</td>";
$list.="<td>".$row["p"]."</td>";
$list.="<td>".$row["w"]."</td>";
$list.="<td>".$row["l"]."</td>";
$list.="<td>".$row["t"]."</td>";
$list.="<td>".$row["ol"]."</td>";
$list.="<td>".$row["gf"]."</td>";
$list.="<td>".$row["ga"]."</td>";
$list.="<td>".$row["pts"]."</td>";
$list.="</tr>";
}
$list.="</table>";
echo($list);
?>
</td>

 

[bubblegum.anarchy]

The calculation can be done in the select query as so:

SELECT standings.*, w*3 + pts AS calc FROM standings ORDER BY pts desc, w desc

[grrraaagh]

The calculation can be done in the select query as so:

SELECT standings.*, w*3 + pts AS calc FROM standings ORDER BY pts desc, w desc

 

I couldn't get that to work as it gave zero's again. I was about to leave it for now, but decided to persevere for a few minutes longer. I replaced the word 'calc' with the actual name of the column in the table 'pts'. Was i expected to do that as a matter of course? (i did warn you i have some massive gaps in my basic use of code and syntax). I added the stripslashes, and subsequently required, parentheses. Also, as i was testing it on old NHL tables i took the chance of inserting extra fields in the calculation.

 

The following worked to perfection,

 

$sql=stripslashes("SELECT standings.*, w*2 + t + ol AS pts FROM standings ORDER BY pts desc, w desc");

 

I was even more amazed to be able to add within it a 'where' clause. This enables me to present various subtables on the page. By that i mean i can have League, Conference and Division tables automatically churned out.

 

$sql=stripslashes("SELECT standings.*, w*2 + t + ol AS pts FROM standings where c like 'e' ORDER BY pts desc, w desc");

 

MANY, MANY THANKS for your help.

 

You can see the test results on this webpage All Tables At-A-Glance

 

Incidentally on that page, during the season, i have next to each of the current tables a 'projected' table. This is because in the NHL you can often have a wide difference in the number of matches a team has played at any given moment. So, i divide the amount of points gained by the number of games played, and then multiply by 82. This gives me the total number of points that a team would amass after all teams would have played the same amount of fixtures. I find it helps one to vizualise where a team is heading, as each year roughly the same points assure division titles and play-off berths.

 

Obviously my next task is to work out how to get that to happen within the framework of the code that you gave me for my original problem. I certainly look forward to trying to work it out now that i am on firmer ground. Thanks again.

 

Incidentally, when i was Googling for a solution before using the forum, i noticed many others requiring a solution to the problem i posed.