469,626 Members | 1,438 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

queries strings and time travel

52
I have a query that uses ORDER BY
I would like it to order by a string that is made after some sub queries at the end of the query.
Is there a nice trick to do this or do I need to find a way to get the string made before the query?
Aug 5 '07 #1
51 2466
kovik
1,044 Expert 1GB
I have a query that uses ORDER BY
I would like it to order by a string that is made after some sub queries at the end of the query.
Is there a nice trick to do this or do I need to find a way to get the string made before the query?
A trick...? What does your query with the sub query look like?
Aug 5 '07 #2
hinksta
52
Thanks volectricity, this is a cutdown, I've removed some other sub's and html

[PHP]<?php
$query1 = ("SELECT DISTINCT(teamname),conference_team.id AS teamid
FROM conference_team
INNER JOIN conference_results
ON conference_results.hometeam = conference_team.id
WHERE season
LIKE '2005-06'
ORDER BY '$pts' ASC ");
$result1 = mysql_query($query1) or die(mysql_error());
while($row = mysql_fetch_array($result1))
{
$teamid = $row['teamid'];

$query3 = ("SELECT hometeam, count(homescore) as win
FROM conference_results
WHERE season
LIKE '2005-06'
AND hometeam = '$teamid'
AND homescore > awayscore
GROUP BY hometeam ");
$result3 = mysql_query($query3) or die(mysql_error());
while($row = mysql_fetch_array($result3))
{
$hw = ($row['win']);
}

$query8 = ("SELECT awayteam, count(awayscore) as win
FROM conference_results
WHERE season
LIKE '2005-06'
AND awayteam = '$teamid'
AND awayscore > homescore
GROUP BY awayteam ");
$result8 = mysql_query($query8) or die(mysql_error());
while($row = mysql_fetch_array($result8))
{
$aw = ($row['win']);
}

$query9 = ("SELECT awayteam, count(awayscore) as draw
FROM conference_results
WHERE season
LIKE '2005-06'
AND awayteam = '$teamid'
AND awayscore = homescore
GROUP BY awayteam ");
$result9 = mysql_query($query9) or die(mysql_error());
while($row = mysql_fetch_array($result9))
{
$ad = ($row['draw']);
}

$query4 = ("SELECT hometeam, count(homescore) as draw
FROM conference_results
WHERE season LIKE '2005-06'
AND hometeam = '$teamid'
AND homescore = awayscore
GROUP BY hometeam ");
$result4 = mysql_query($query4) or die(mysql_error());
while($row = mysql_fetch_array($result4))
{
$hd = ($row['draw']);
}

$tw = $hw + $aw;
$td = $hd + $ad;

$fwins = $tw * 3;
$fdraws = $td;
$pts = $fwins + $fdraws;
}[/PHP]

I would like query1 to be ordered by $pts
Aug 5 '07 #3
pbmods
5,821 Expert 4TB
Heya, hinksta.

Simply declare $query1 after you declare $pts.
Aug 5 '07 #4
hinksta
52
not sure that I can or understand.

query1 sorts out the id's and gets everything going.
Aug 5 '07 #5
kovik
1,044 Expert 1GB
I would like query1 to be ordered by $pts
It's not possible to order a query by a single value, as it'd just automatically be ordered by the primary key. I don't see a sub query at all.

Exactly what are you trying to order by?
Aug 5 '07 #6
hinksta
52
It's a football league table listing teams standings by points per game, should I post the whole thing + a bit of the db?

he he, I thought a sub query was a query in a query
Aug 5 '07 #7
pbmods
5,821 Expert 4TB
Ah. I see now.

What are some desirable values for $pts?

The ORDER BY clause is supposed to contain a field reference, not a number....
Aug 5 '07 #8
hinksta
52
$pts could be anything from -20 to over 100 but is generally 0 to about 100
Aug 5 '07 #9
pbmods
5,821 Expert 4TB
Heya, Hinksta.

$pts could be anything from -20 to over 100 but is generally 0 to about 100
Sounds like you might not be using ORDER BY properly.

How would you sort your results by -20? How would that be different than sorting your results by 50?
Aug 5 '07 #10
hinksta
52
How would you sort your results by -20? How would that be different than sorting your results by 50?
Not sure I understand the question.

The results for each team are calculated and present for each team as they are listed. I can order them by the teamname but I want to be able to have the team with the highest points ($pts) DESC at the top of the list.
Aug 5 '07 #11
pbmods
5,821 Expert 4TB
Heya, hinksta.

So what you're saying is that you want to order your results by score?

Look carefully at your query:
Expand|Select|Wrap|Line Numbers
  1. $query1 = ("SELECT DISTINCT(teamname),conference_team.id AS teamid
  2. FROM conference_team
  3. INNER JOIN conference_results
  4. ON conference_results.hometeam = conference_team.id
  5. WHERE season
  6. LIKE '2005-06'
  7. ORDER BY '$pts' ASC ");
  8.  
Note that if you substitute a sample value for $pts, your query now looks like this:
Expand|Select|Wrap|Line Numbers
  1. $quey1 = ("... ORDER BY '50' ASC ");
  2.  
Aug 5 '07 #12
hinksta
52
Yes I see now, the query would only get one figure.
Aug 5 '07 #13
pbmods
5,821 Expert 4TB
Heya, hinksta.

It seems like you should be ok in terms of the order of your queries.

The question really stands as to how to order the result set. I would presume that you want to order your results by the winning score.

This is a little tricky, but not difficult.

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         *,
  3.         GREATEST(`homescore`, `awayscore`) AS `winningscore`
  4.     .
  5.     .
  6.     .
  7.     ORDER BY `winningscore` DESC
  8.  
Aug 6 '07 #14
kovik
1,044 Expert 1GB
So, what you want to do is order the teams by the total amount of points they've made, both from the hometeam and awayteam tables?

Edit: Didn't see pbmods' post before I posted.
Aug 6 '07 #15
hinksta
52
It's as volectricity writes

each team is given 3 points for a win and 1 point for a draw
Aug 6 '07 #16
kovik
1,044 Expert 1GB
It's as volectricity writes

each team is given 3 points for a win and 1 point for a draw
That could be done with a subquery. Are you familiar with subqueries at all?

Expand|Select|Wrap|Line Numbers
  1. SELECT `foo` FROM `table` WHERE (SELECT `someValue` FROM `someOtherTable` WHERE `bar` = `foo`);
Aug 6 '07 #17
hinksta
52
A tiny bit, the subquery is your second select
Aug 6 '07 #18
kovik
1,044 Expert 1GB
A tiny bit, the subquery is your second select
Right. You can use the subquery to get a result that is dependent upon the rest of the query you have already ran, and will be separately checked for each result.
Aug 6 '07 #19
hinksta
52
I'm having a bit of trouble with syntax and a bit of confusion.

Some examples i've found put each subquery inside the previous subquery (looks very complicated) and others have them one after the other. I'm starting to think I should be doing the first.
Aug 6 '07 #20
kovik
1,044 Expert 1GB
I'm having a bit of trouble with syntax and a bit of confusion.

Some examples i've found put each subquery inside the previous subquery (looks very complicated) and others have them one after the other. I'm starting to think I should be doing the first.
The brief example that I gave you was meant to show you how it'd visually look.

Maybe this would help:

Expand|Select|Wrap|Line Numbers
  1. SELECT `data` FROM `table` WHERE `team` = 'foo' ORDER BY (SELECT `firstTable`.`score`, `secondTable`.`score` FROM `firstTable` LEFT JOIN `secondTable` ON (`firstTable`.`team` = `secondTable`.`team`) WHERE `team` = 'foo');
You'd just need to create the logic of 'wins' and 'draws,' which could easily be done using the IF() statement and comparing the results from the two tables. On second though, it may even be easier like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT `data`, `firstTable`.`score`, `secondTable`.`score` FROM `table` LEFT JOIN (`firstTable` CROSS JOIN `secondTable`) ON (`firstTable`.`team` = `table`.`team` AND `secondTable`.`team` = `table`.`team`) WHERE `team` = 'foo' ORDER BY ...
And you'd create the ORDER BY after you created the logic for building the score... I'm not sure if the "easy" way would work though, as it sounds as though you need to get totals of multiple records. If so, then you'll need subqueries.
Aug 6 '07 #21
hinksta
52
This is the first way i'm trying but cannot move forward because of a sytax error on the last line I just don't see. If I remove WHERE season = '2005-06' the error just moves to line 7

[PHP]$query1 = ("SELECT *
FROM conference_team
WHERE conference_team.id = conference_results.hometeam
ORDER BY (SELECT conference_results.homescore, conference_results.awayscore
FROM conference_team
LEFT JOIN conference_results
ON (conference_results.hometeam = conference_team.id)
WHERE season = '2005-06' ");[/PHP]
Aug 6 '07 #22
hinksta
52
found the syntax )");
Aug 6 '07 #23
hinksta
52
I'm just getting more confused with this and I wonder if in part it is because your examples show 2 score tables. I only have 2 tables total (below)

Expand|Select|Wrap|Line Numbers
  1.  CREATE TABLE conference_team (
  2.  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.  teamname VARCHAR(50) NOT NULL );
  4.  
  5. INSERT INTO conference_team (teamname) VALUES ('Accrington Stanley');
  6. INSERT INTO conference_team (teamname) VALUES ('Aldershot');
  7. INSERT INTO conference_team (teamname) VALUES ('Altrincham');
  8. INSERT INTO conference_team (teamname) VALUES ('Aylesbury United');
  9. INSERT INTO conference_team (teamname) VALUES ('Barnet');
  10. INSERT INTO conference_team (teamname) VALUES ('Barrow');
  11. INSERT INTO conference_team (teamname) VALUES ('Bath City');
  12. INSERT INTO conference_team (teamname) VALUES ('Boston United');
  13. INSERT INTO conference_team (teamname) VALUES ('Bromsgrove Rovers');
  14. INSERT INTO conference_team (teamname) VALUES ('Burton Albion');
  15. INSERT INTO conference_team (teamname) VALUES ('Cambridge United');
  16. INSERT INTO conference_team (teamname) VALUES ('Canvey Island');
  17. INSERT INTO conference_team (teamname) VALUES ('Carlisle United');
  18. INSERT INTO conference_team (teamname) VALUES ('Cheltenham Town');
  19. INSERT INTO conference_team (teamname) VALUES ('Chester City');
  20. INSERT INTO conference_team (teamname) VALUES ('Chorley');
  21.  
  22.  
  23. CREATE TABLE conference_results (
  24.  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  25.  season VARCHAR(50) NOT NULL,
  26.  gamedate DATE NOT NULL,
  27.  hometeam INT NOT NULL,
  28.  homescore INT NOT NULL,
  29.  awayscore INT NOT NULL,
  30.  awayteam INT NOT NULL);
  31.  
  32. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 3, 7, 1, 1);
  33. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 19, 48, 3, 3);
  34. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 24, 30, 1, 1);
  35. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 29, 44, 0, 2);
  36. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 38, 5, 0, 3);
  37. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 49, 54, 3, 0);
  38. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 52, 8, 3, 1);
  39. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 62, 64, 2, 2);
  40. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 65, 59, 0, 3);
  41. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 66, 14, 1, 3);
  42. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 67, 37, 2, 0);
  43. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-18", 19, 67, 2, 0);
  44. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-18", 38, 3, 3, 0);
  45. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 5, 66, 1, 1);
  46. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 52, 30, 4, 0);
  47. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 59, 49, 3, 1);
  48. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 62, 24, 0, 1);
  49. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 64, 14, 3, 1);
  50. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-20", 37, 7, 2, 0);
  51. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-20", 44, 65, 1, 0);
[PHP]echo "<table border=\"1\" align=\"center\" cellspacing=\"1\" cellpadding=\"4\">";
echo"<tr>";
//--------------------------------------------------------------------------------------
function increment(&$value, $amount = 1)
{
$value = $value +$amount;
}
$poss = 1;
//--------------------------------------------------------------------------------------
$query1 = ("SELECT DISTINCT(teamname),conference_team.id AS teamid FROM conference_team INNER JOIN conference_results ON conference_results.hometeam = conference_team.id WHERE season LIKE '2005-06'");
$result1 = mysql_query($query1) or die(mysql_error());
while($row = mysql_fetch_array($result1))
{
$teamid = $row['teamid'];
//--------------------------------------------------------------------------------------
echo"<td>";
echo $poss.'';
increment ($poss);
echo"</td>";
//--------------------------------------------------------------------------------------
echo"<td><div align=\"right\">";
$teamnames = $row['teamname'];
echo"$teamnames";
echo"</div></td>";
//--------------------------------------------------------------------------------------
echo"<td>";
$query2 = ("SELECT hometeam, count(hometeam) AS home , awayteam, count(awayteam) as away FROM conference_results WHERE season LIKE '2005-06' AND hometeam = '$teamid' GROUP BY hometeam ");
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2))
{
$home = ($row['home']);
$away = ($row['away']);
$teams = ($home + $away);
echo" $teams";}
echo"</td>";
//--------------------------------------------------------------------------------------
echo"<td>";
$query3 = ("SELECT hometeam, count(homescore) AS win FROM conference_results WHERE season LIKE '2005-06' AND hometeam = '$teamid' AND homescore > awayscore GROUP BY hometeam ");
$result3 = mysql_query($query3) or die(mysql_error());
while($row = mysql_fetch_array($result3))
{
$hw = ($row['win']);
echo" $hw";}
echo"</td>";
//--------------------------------------------------------------------------------------
echo"<td>";
$query4 = ("SELECT hometeam, count(homescore) AS draw FROM conference_results WHERE season LIKE '2005-06' AND hometeam = '$teamid' AND homescore = awayscore GROUP BY hometeam ");
$result4 = mysql_query($query4) or die(mysql_error());
while($row = mysql_fetch_array($result4))
{
$hd = ($row['draw']);
echo"$hd";}
echo"</td>";
//--------------------------------------------------------------------------------------
echo"<td>";
$query5 = ("SELECT hometeam, count(homescore) AS lose FROM conference_results WHERE season LIKE '2005-06' AND hometeam = '$teamid' AND homescore < awayscore GROUP BY hometeam ");
$result5 = mysql_query($query5) or die(mysql_error());
while($row = mysql_fetch_array($result5))
{
$hl = ($row['lose']);
echo"$hl";}
echo"</td>";
//--------------------------------------------------------------------------------------
// home goals
echo"<td>";
$query6 = ("SELECT sum(homescore) AS homescoresum FROM conference_results WHERE season LIKE '2005-06' AND hometeam = '$teamid' ");
$result6 = mysql_query($query6) or die(mysql_error());
while($row = mysql_fetch_array($result6))
{
$hgf = ($row['homescoresum']);
echo" $hgf";}
echo"</td>";
//--------------------------------------------------------------------------------------
// away goals
echo"<td>";
$query7 = ("SELECT sum(awayscore) AS awayscoresum FROM conference_results WHERE season LIKE '2005-06' AND hometeam = '$teamid' ");
$result7 = mysql_query($query7) or die(mysql_error());
while($row = mysql_fetch_array($result7))
{
$hga = ($row['awayscoresum']);
echo" $hga";}
echo"</td>";
//--------------------------------------------------------------------------------------
echo"<td>&nbsp;</td>";
//--------------------------------------------------------------------------------------
echo"<td>";
$query8 = ("SELECT awayteam, count(awayscore) AS win FROM conference_results WHERE season LIKE '2005-06' AND awayteam = '$teamid' AND awayscore > homescore GROUP BY awayteam ");
$result8 = mysql_query($query8) or die(mysql_error());
while($row = mysql_fetch_array($result8))
{
$aw = ($row['win']);
echo" $aw";}
echo"</td>";
//--------------------------------------------------------------------------------------
echo"<td>";
$query9 = ("SELECT awayteam, count(awayscore) AS draw FROM conference_results WHERE season LIKE '2005-06' AND awayteam = '$teamid' AND awayscore = homescore GROUP BY awayteam ");
$result9 = mysql_query($query9) or die(mysql_error());
while($row = mysql_fetch_array($result9))
{
$ad = ($row['draw']);
echo"$ad";}
echo"</td>";
//--------------------------------------------------------------------------------------
echo"<td>";
$query10 = ("SELECT awayteam, count(awayscore) AS lose FROM conference_results WHERE season LIKE '2005-06' AND awayteam = '$teamid' AND awayscore < homescore GROUP BY awayteam ");
$result10 = mysql_query($query10) or die(mysql_error());
while($row = mysql_fetch_array($result10))
{
$al = ($row['lose']);
echo"$al";}
echo"</td>";
//--------------------------------------------------------------------------------------
// home goals
echo"<td>";
$query11 = ("SELECT sum(awayscore) AS homecoresum FROM conference_results WHERE season LIKE '2005-06' AND awayteam = '$teamid' ");
$result11 = mysql_query($query11) or die(mysql_error());
while($row = mysql_fetch_array($result11))
{
$agf = ($row['homecoresum']);
echo" $agf";}
echo"</td>";
//--------------------------------------------------------------------------------------
// away goals
echo"<td>";
$query12 = ("SELECT sum(homescore) AS awayscoresum FROM conference_results WHERE season LIKE '2005-06' AND awayteam = '$teamid' ");
$result12 = mysql_query($query12) or die(mysql_error());
while($row = mysql_fetch_array($result12))
{
$aga = ($row['awayscoresum']);
echo" $aga";}
echo"</td>";
//--------------------------------------------------------------------------------------
echo"<td>&nbsp;</td>";
//--------------------------------------------------------------------------------------
$tw = $hw + $aw;
echo"<td>$tw</td>";
//--------------------------------------------------------------------------------------
$td = $hd + $ad;
echo"<td>$td</td>";
//--------------------------------------------------------------------------------------
$tl = $hl + $al;
echo"<td>$tl</td>";
//--------------------------------------------------------------------------------------
$tgf = $hgf + $agf;
echo"<td>$tgf</td>";
//--------------------------------------------------------------------------------------
$tga = $hga + $aga;
echo"<td>$tga</td>";
//--------------------------------------------------------------------------------------
echo"<td>&nbsp;</td>";
//--------------------------------------------------------------------------------------
$gd = $tgf - $tga;
echo"<td>$gd</td>";
//--------------------------------------------------------------------------------------
$fwins = $tw * 3;
$fdraws = $td;
$pts = $fwins + $fdraws;
echo"<td>$pts</td>";
//--------------------------------------------------------------------------------------
echo"</tr>";
}
echo"</table>";[/PHP]
Aug 6 '07 #24
kovik
1,044 Expert 1GB
If it's all in the same table, then why are you having difficulty...? Explain exactly what you want to do and I'll see if I can help.
Aug 7 '07 #25
hinksta
52
The last echo is a total of points acumalated over a season
I would like to order the list of teams by that number.
ORDER BY '$pts'
Aug 7 '07 #26
pbmods
5,821 Expert 4TB
Heya, hinksta.

I think you're trying to do something other than what you are describing.

You can't ORDER BY a number. You have to ORDER BY a field name.

Note also that PHP and MySQL can't communicate by sending variables back and forth.

When you ORDER BY '$pts', you are literally sending the value of $pts, not the variable.

For example, if the value of $pts is 60, then this is what your script would be sending to MySQL:
ORDER BY '60'

Are you trying to sort the teams based on how many points each one scored during the season?
Aug 7 '07 #27
hinksta
52
I understand now that the way I have it is not possible but I can’t help but think there must be a way to achieve it. As suggested earlier the subqueries way looked a good bet but i'm not able to find many examples of subqueries in fact I have not found 1 example of a multi table subquery.

It works like this 2 teams play a game, if 1 team wins the game (by 4-1 , 6-2 etc) they get 3points, if it’s a draw (0-0 1-1 etc) they get 1 point, if they lose no points. At the end of the season the team with the most points wins
Aug 7 '07 #28
hinksta
52
This may help you see a bit better, if I added the teams points to the database it would look like this with the home and away team pionts on the end.

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE conference_results (
  2.  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.  season VARCHAR(50) NOT NULL,
  4.  gamedate DATE NOT NULL,
  5.  hometeam INT NOT NULL,
  6.  awayteam INT NOT NULL,
  7.  homescore INT NOT NULL,
  8.  awayscore INT NOT NULL,
  9.  hometeampoints INT NOT NULL,
  10.  awayteampoints INT NOT NULL);
  11.  
  12. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 3, 7, 1, 1, 1, 1);
  13. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 19, 48, 3, 3, 1, 1);
  14. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 24, 30, 1, 1, 1, 1);
  15. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 29, 44, 0, 2, 0, 3);
  16. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 38, 5, 0, 3, 0, 3);
  17. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 49, 54, 3, 0, 3, 0);
  18. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 52, 8, 3, 1, 3, 0);
  19. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 62, 64, 2, 2, 1, 1);
  20. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 65, 59, 0, 3, 0, 3);
  21. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 66, 14, 1, 3, 0, 3);
  22. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-16", 67, 37, 2, 0, 3, 0);
  23. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-18", 19, 67, 2, 0, 3, 0);
  24. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-18", 38, 3, 3, 0, 3, 0);
  25. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 5, 66, 1, 1, 1,1);
  26. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 52, 30, 4, 0, 3,0);
  27. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 59, 49, 3, 1, 3,0);
  28. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 62, 24, 0, 1, 0,3);
  29. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-19", 64, 14, 3, 1, 3,0);
  30. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-20", 37, 7, 2, 0, 3,0);
  31. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-20", 44, 65, 1, 0, 3,0);
  32. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-20", 48, 29, 1, 1, 1,1);
  33. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-20", 54, 8, 0, 0, 1,1);
  34. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-23", 7, 66, 1, 1, 1,1);
  35. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-23", 8, 19, 1, 0, 3,0);
  36. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-23", 14, 5, 1, 2, 0,3);
  37. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-23", 24, 29, 0, 0, 1,1);
  38. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-23", 37, 3, 0, 2, 0,3);
  39. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-23", 44, 67, 3, 1, 3,0);
  40. INSERT INTO conference_results (season, gamedate, hometeam, awayteam, homescore, awayscore) VALUES ("1986-87", "1986-08-23", 48, 38, 1, 2, 0,3);
Aug 7 '07 #29
pbmods
5,821 Expert 4TB
Heya, Hinksta.

It will be... difficult at best to do this in MySQL.

You have a couple of options. Probably the easiest one is to create a separate array with keys as team ID numbers, and the values as the total number of 'win-points' for each team.

For example, if you had the following game data:
  • Team 5 vs. Team 8: Team 5 wins.
  • Team 3 vs. Team 12: Team 12 wins.
  • Team 5 vs. Team 7: Tie
  • Team 8 vs. Team 12: Team 8 wins.

Then you would create an array that looks like this:
Expand|Select|Wrap|Line Numbers
  1. array(
  2.     '5' => 4,  // 1 win + 1 tie
  3.     '8' => 3,  // 1 loss + 1 win
  4.     '3' => 0,  // 1 loss
  5.     '12' => 3, // 1 win + 1 loss
  6.     '7' => 1  // 1 tie
  7. )
  8.  
Then you call asort() on your array, and it looks something like this:
Expand|Select|Wrap|Line Numbers
  1. array(
  2.     '5' => 4,
  3.     '8' => 3,
  4.     '12' => 3,
  5.     '7' => 1,
  6.     '3' => 0
  7. )
  8.  
Then when you are outputting your data, use this array to determine the order to output your team data.
Aug 7 '07 #30
hinksta
52
Hey pbmods, just before I start thinking about that.

What if my database was as the last one I posted? (it's easy to update)
Would I be able to do something like

sum(hometeampoints + awayteampoints) as pts ORDER BY 'pts'
Aug 7 '07 #31
pbmods
5,821 Expert 4TB
Heya, hinksta.

Hey pbmods, just before I start thinking about that.

What if my database was as above? (it's easy to update)
Would I be able to do something like

sum(hometeampoints + awayteampoints) as pts ORDER BY 'pts'
Get rid of the 'sum' (e.g., "SELECT ... (`hometeampoints` + `awayteampoints`) AS `pts` ...").

I don't know if this will have the effect that you are looking for, but it is valid SQL. This query will sort your results by the total amount of points scored per game.
Aug 7 '07 #32
hinksta
52
OK I've updated a database and query1,

its getting closer but as it is now the result is a single game
(homepts + awaypts) = 3 instead of the whole season (homepts + awaypts) = 88

[PHP]
$query1 = ("SELECT DISTINCT(teamname), conference_team.id AS teamid, (homepts + awaypts) AS pts FROM conference_team INNER JOIN conference_results_pts ON conference_results_pts.hometeam = conference_team.id WHERE season LIKE '2005-06' ORDER BY 'pts' DESC");
[/PHP]
Aug 7 '07 #33
kovik
1,044 Expert 1GB
OK I've updated a database and query1,

its getting closer but as it is now the result is a single game
(homepts + awaypts) = 3 instead of the whole season (homepts + awaypts) = 88

[PHP]
$query1 = ("SELECT DISTINCT(teamname), conference_team.id AS teamid, (homepts + awaypts) AS pts FROM conference_team INNER JOIN conference_results_pts ON conference_results_pts.hometeam = conference_team.id WHERE season LIKE '2005-06' ORDER BY 'pts' DESC");
[/PHP]
... Now, you are ordering by a constant string instead of a constant number.

Single quotes (') and backticks (`) mean totally different things in SQL.

'pts' is not `pts`
Aug 7 '07 #34
hinksta
52
This gives me "total home pionts" ordered by home point `pts`

[PHP]
$query1 = ("SELECT DISTINCT(teamname), crp1.season, conference_team.id as `teamid` , sum(crp1.homepts) AS `pts` FROM conference_team
LEFT JOIN conference_results_pts AS `crp1` ON crp1.hometeam = conference_team.id
WHERE crp1.season = '2005-06' GROUP BY teamname ORDER BY pts DESC");
[/PHP]

This gives me "total away pionts" ordered by away point `pts`

[PHP]
$query1 = ("SELECT DISTINCT(teamname), crp2.season, conference_team.id as `teamid` , sum(crp2.awaypts) AS `pts` FROM conference_team
LEFT JOIN conference_results_pts AS `crp2` ON crp2.awayteam = conference_team.id
WHERE crp2.season = '2005-06' GROUP BY teamname ORDER BY pts DESC");
[/PHP]

Is it possible to put them together so I can order by the sum of both home `pts and away `pts`
I've tried it with a second left join and with a subquery in the left join, maybe my inexperience is a problem.

The more I read about this the more I think sql can only do a few simple things and that this is not possible.
Aug 8 '07 #35
kovik
1,044 Expert 1GB
What? But they are in the same table! Just add them like pbmods said.
Aug 8 '07 #36
hinksta
52
What? But they are in the same table! Just add them like pbmods said.
As I said before, if I do that all I get is the wrong result for 1 single game

If the game is won the result gives me 3
If the game is lost the result gives me 3
If the game is a tie the result gives me 2
this is wrong

[PHP]
$query1 = ("SELECT DISTINCT(teamname), crp1.season, ct1.id as `teamid` , (`homepts` + `awaypts`) AS `pts`
FROM conference_team AS ct1
LEFT JOIN conference_results_pts AS `crp1`
ON ct1.id = crp1.hometeam
WHERE crp1.season = '2005-06' GROUP BY teamname ORDER BY `pts` DESC");
[/PHP]
Aug 8 '07 #37
kovik
1,044 Expert 1GB
What about the logic to determine whether this team was the home team or the away team?
Aug 8 '07 #38
hinksta
52
Could you show me, I have not been able to get both hometeam and awayteam to = ct1.id
Aug 8 '07 #39
pbmods
5,821 Expert 4TB
Heya, Hinksta.

Or you could just do the calculations in PHP... 0:)
Aug 8 '07 #40
kovik
1,044 Expert 1GB
Could you show me, I have not been able to get both hometeam and awayteam to = ct1.id
Maybe an IF() statement could be useful.
Aug 9 '07 #41
hinksta
52
Volectricity, I must say I find your participation In this tread infuriating, you make cryptic suggestions regardless of there ability to help.
This thread has been going for several days now and I still don’t think you have fully understood the question.
I came here for help, not a list of words from a reference manual.
Aug 9 '07 #42
pbmods
5,821 Expert 4TB
Heya, Hinksta.

I think the problem with trying to do the calculations in MySQL is that you have too much to keep track of.

You have to compare the two scores for each game, and then you have to set a value for one or both teams that participated depending on the result of this comparison.

At best, you'd need to write up a stored procedure to handle this. At worst, you'd need to create a whole bunch of variables or else a temporary table, and MySQL is not very good at keeping variables organized.

Correct me if I'm wrong, but it doesn't seem like the order of the records is important until you're actually displaying them. My recommendation would be to forget about ordering the results on the MySQL side and focus on retrieving all your data, then going back and sorting your results on the PHP side before displaying them.
Aug 9 '07 #43
hinksta
52
Thanks pbmods, I've been searching google for other football type database's and found one that has the points set out similar to you suggested before.

It has

0 = tie
1 = home win
2 = away win

Is that the type of result you are thinking of getting with a stored procedure?
Do you think it would help to add this in my database? As before it's easy to add.

Also I hope you don't mind but my experience is very limited, I guess I already have the required data from my code above but I'm unsure what I need to do next. If you can help further or point me to some good reading for this type of list, that would be great.
Aug 9 '07 #44
pbmods
5,821 Expert 4TB
Heya, Hinksta.

The general flow of the stored procedure would be as follows:
  • Create a temporary table, and populate it with every team_id.
  • Run through `conference_results_pts` and compare home and away scores.
  • If home > away, add 3 to the home team's entry in the temporary table.
  • Else if away > home, add 3 to the away team's entry in the temporary table.
  • Else add 1 to each of the home and away team's entry in the temporary table.

Once the stored procedure finishes running, then you can run your SELECT, joined with the temporary table and ORDER BY'ed the temporary table's score field.
Aug 9 '07 #45
kovik
1,044 Expert 1GB
Volectricity, I must say I find your participation In this tread infuriating, you make cryptic suggestions regardless of there ability to help.
This thread has been going for several days now and I still don’t think you have fully understood the question.
I came here for help, not a list of words from a reference manual.
Well, I'm not here to hold your hand. It's *your* job to learn the languages. It's not my job to teach it, nor my job to spend all of my time trying to "fully understand" your question. I've no interest in studying your table structure for you. The result is up to you, and has absolutely no effect on me.

Your response was completely unnecessary. Maybe asking for some clarification, or asking for a pseudo implementation of some sort, but instead your resorted to rudeness. If it's really that big of a deal, then I'll leave this thread to pbmods.
Aug 10 '07 #46
hinksta
52
volectricity, In the heat of the moment I was probably a bit over the top, I apologies.

pbmods, I'm having a problem populating the temp table with the generated points (getting 0 in the results),
I made a test table so I could see what was going on.
It is getting populated but the teams are all getting 0 points with the actual points being added after all team names have been listed,

example

1, Accrington Stanley, 0
2, Aldershot, 0
3, Altrincham, 0
4, Aylesbury United, 0
63, , 3
64, , 1
65, , 3

[PHP]
//--------------------------------------------------------------------------------------
// Compare home and away scores
$query = ("SELECT homescore, awayscore, season
FROM conference_results_pts
LEFT JOIN testpoints
ON conference_results_pts.hometeam = testpoints.id WHERE season = '2005-06' ");
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$homescore1 = $row['homescore'];
$awayscore1 = $row['awayscore'];
if ($homescore1 > $awayscore1)
{$addteam_points = 3;
//--------------------------------------------------------------------------------------
// Populate Temporary Table Points
mysql_query("INSERT INTO testpoints (team_points) Values (".$addteam_points.")");
//--------------------------------------------------------------------------------------
[/PHP]
Aug 10 '07 #47
hinksta
52
I've half fixed it with

[PHP]//--------------------------------------------------------------------------------------
// Compare home and away scores
$query = ("SELECT homescore, awayscore, season, confpoints.id, hometeam
FROM confpoints
LEFT JOIN conference_results_pts
ON conference_results_pts.hometeam = confpoints.id WHERE conference_results_pts.season = '2005-06' ");
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$homescore1 = $row['homescore'];
$awayscore1 = $row['awayscore'];
if ($homescore1 > $awayscore1)
{$addteam_points = 3;
//--------------------------------------------------------------------------------------
// Populate Temporary Table Points
mysql_query("UPDATE `confpoints` SET `team_points` = `team_points` + ".$addteam_points."");
//-------------------------------------------------------------------------------------- [/PHP]
but all teams have the same points, looks like a sum of all points
Aug 10 '07 #48
pbmods
5,821 Expert 4TB
Heya, Hinksta.

At this point, before your script continues to get needlessly complicated, allow me to recommend storing your temporary points into an array on the PHP side instead of going through all the overhead of creating a new table in MySQL.

See Post #30.
Aug 10 '07 #49
hinksta
52
I now have an array with the data you suggested in post 30 but I could also have that data in the database, would that be better?

Team 5 vs. Team 8: Team 5 wins.
Team 3 vs. Team 12: Team 12 wins.
Team 5 vs. Team 7: Tie
Team 8 vs. Team 12: Team 8 wins.

I'm a little unsure what I need to do next with the array, I have been reading a lot about arrays but haven’t seen the light as to how to cross reference the data for each team.
Aug 13 '07 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

26 posts views Thread by William Park | last post: by
5 posts views Thread by Martin Lacoste | last post: by
7 posts views Thread by Zlatko Matić | last post: by
11 posts views Thread by Eliot Coweye | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.