Forum Moderators: coopster
I have a database table of data that lists teams and their scores. I want to automatically produce a league table on my website. The database table is ordered and structured so there is a row for each match and a column listing the home team, home team score, away team and away team score. So for example team 'Las Gemelas' has a unique id which will appear in several different games (rows) but for some rows it will appear as a home team in the home team column and for other games (rows) it will appear in the away team column.
I want to add up all the scores by team irrespective of if they were scored home or away. So I need to bring data from different columns together somehow and then summarise it.
At the moment all I can seem to do is write php to get the home team scores and then a seperate list that gives the away scores (the php I have used is given at the bottom of this post). This results in a table where every team appears twice. I need to work out how to sum the result of these two queries to give me a simple league table in which each team only appears once.
If you go here <snip> you can see what I am trying to acheive. At the top there is a nicley formatted league table which I calculate manually at the moment and am trying to replace with automatice php code. The badly formatted list of teams that appears below the table is the current result of my php.
$query = '
SELECT hometeam, SUM(hometeam_score) AS points
FROM cms_module_tss_gameschedule_score
GROUP BY hometeam
';
$dbresult = $db->Execute($query);
while ($dbresult && !$dbresult->EOF)
{
$row = $dbresult->FetchRow();
$content = $row['hometeam'].': '.$row['points'].'
<br />';
echo $content;
}
$query1 = '
SELECT visitorteam, SUM(visitorteam_score) AS points
FROM cms_module_tss_gameschedule_score
GROUP BY visitorteam
';
$dbresult = $db->Execute($query1);
while ($dbresult && !$dbresult->EOF)
{
$row = $dbresult->FetchRow();
$content = $row['visitorteam'].': '.$row['points'].'
<br />';
echo $content;
}
[edited by: eelixduppy at 8:07 pm (utc) on Oct. 13, 2009]
[edit reason] no URLs, please [/edit]
dc
I created a new table - called LaLigawk. I then ran two INSERT queries to take data from the original table and slot it into my new table, one insert query for the home data and one for the away data. SQL assumes that you want to insert the data in the same order as you enter it. So in the create table query the second column I created was the team column. Therefore the second column I name in each insert query is automatically allocated to the team column.
Running the queries below gives a single table that lists all the scores by team.
CREATE TEMPORARY TABLE LaLigawk
(
id VARCHAR(5) NOT NULL ,
team VARCHAR(50) NOT NULL ,
score DECIMAL (12,0)
);
INSERT INTO LaLigawk
SELECT
hometeam_id, hometeam, hometeam_score
FROM cms_module_tss_gameschedule_score;
INSERT INTO LaLigawk
SELECT
visitorteam_id, visitorteam, visitorteam_score
FROM cms_module_tss_gameschedule_score;
Then to summarise this data so that there is only line per team and a single score representing the total points scored so far this season I do the following.
DROP TABLE LaLiga;
CREATE TABLE LaLiga
(
id VARCHAR(5) NOT NULL ,
team VARCHAR(50) NOT NULL ,
score DECIMAL (12,0)
);
INSERT INTO LaLiga
SELECT
id, team, SUM(score)
FROM LaLigawk
GROUP BY id
ORDER BY SUM(score) DESC;
Couple of things for newbies - the DROP TABLE bit makes sure that when I rerun the query each week the data is cleared out and then rerun from scratch. If I did not do this then every time I ran the query all the scores would be added on again effectivley doubling them.
The GROUP BY tells the table to have a single line for each unique team id.
The ORDER BY bit tells the table to sort the rows buy the summed score.
Note: I just ran this via my cpanel. Go into the database and choose the SQL tab and then just write your queries.
Hope this helps someone.
