queries strings and time travel

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?

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

Heya, hinksta.

Simply declare $query1 after you declare $pts.

not sure that I can or understand.

query1 sorts out the id's and gets everything going.

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?

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

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....

$pts could be anything from -20 to over 100 but is generally 0 to about 100

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?

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.

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:

Yes I see now, the query would only get one figure.

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.

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.

It's as volectricity writes

each team is given 3 points for a win and 1 point for a draw

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?

A tiny bit, the subquery is your second select

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.

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.

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:

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:

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.

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]

found the syntax )");

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)

[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]

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.

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'

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?

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

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.

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:
Then you call asort() on your array, and it looks something like this:
Then when you are outputting your data, use this array to determine the order to output your team data.

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'

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.

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]

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`

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.

What? But they are in the same table! Just add them like pbmods said.

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]

What about the logic to determine whether this team was the home team or the away team?

Could you show me, I have not been able to get both hometeam and awayteam to = ct1.id

Heya, Hinksta.

Or you could just do the calculations in PHP... 0:)

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.

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.

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.

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.

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.

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.

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]

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

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.

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.