Reversing an ORDER BY statement

[MargateSteve]

This is a follow up from a post that I have already made in the PHP forum (http://www.phpfreaks.com/forums/php-coding-help/problem-with-array_reverse/) as a comment suggested that MySQL may be the way to go.

 

Basically, I am trying to get the most recent five records by date and then echo them in date ascending order. I can succesfully get the right records using

$tpall_games2 = mysql_query("
SELECT * ,  
DATE_FORMAT(`date`,'%W, %D %M %Y') AS showdate, 
HT.team_name as HomeTeam, 
VT.team_name as AwayTeam, 
COMP.comp_short_name as CP
FROM all_games
JOIN teams as HT ON all_games.home_team = HT.team_id
JOIN teams as VT ON all_games.away_team = VT.team_id 
JOIN competitions as COMP ON all_games.comp = COMP.comp_id 
WHERE 
$id IN (all_games.home_team, all_games.away_team) 
AND all_games.home_goals IS NOT NULL 
ORDER BY `date` DESC 
LIMIT 5
");
if (!$tpall_games2) {
    die("Query to show fields from All games Team Page table failed");
};

 

I have tried using array_reverse in the php output but the records still show in descending order.

 

I have also tried this suggestion from the other post

$query1 = "SELECT COUNT(`index_field`) FROM `table` WHERE whatever";
$result1 = mysql_query( $query );
$array1 = mysql_fetch_row($result);

$display_num = 5; // the number of records you want displayed
$start = $array1[0] - $display_num; // Total number of results, minus number to display

$query2 = "SELECT `records` FROM `table` ORDER BY `field` ASC LIMIT $start, $display_num";
// etc . . .

but no records are returned.

 

I have also tried to amend this example that I have seen elsewhere

SELECT * 
FROM (
  SELECT ... 
  FROM ... 
  ORDER BY ID ASC 
  LIMIT 3
) AS sq 
ORDER BY ID DESC

 

but that game me a 'Query to show fields failed' error.

 

Is there a way, via the query that I can reverse the ORDER BY to get the results to show in the right order? The "ORDER BY `date` DESC" is vital as I just want to get the most recent 5 results but then want to order those results by ASC.

 

Thanks in advance for any suggestions.

 

Steve

 

[MargateSteve]

Really sorry but the edit button has gone. If it is of any relevance, the database is running on MySQL 4.0.

[joel24]

why are you using a variable $id in this line?? the IN clause works like so

 WHERE column IN ('value1','value2',....)

 

is the first query returning results, though just not ordered correctly, or is it not returning any rows?

[MargateSteve]

why are you using a variable $id in this line?? the IN clause works like so

 WHERE column IN ('value1','value2',....)

 

That would be my naivety in MySQL! I thought that would be the correct way to return results when $id (which is a posted 'team.team_id') was in either of the named columns.

 

Would it be correct syntax to use

WHERE 
$id =  all_games.home_team AND all_games.home_goals IS NOT NULL 
OR $id = all_games.away_team AND all_games.home_goals IS NOT NULL

?

 

The first query gives the correct results (ie. the most recent 5 records by date) but would show

1st Jan 2011
1st Dec 2010
1st Nov 2010
1st Oct 2010
1st Sep 2010

when I need it to show

1st Sep 2010
1st Oct 2010
1st Nov 2010
1st Dec 2010
1st Jan 2011

 

Steve

 

[joel24]

maybe use some brackets

WHERE
($id =  all_games.home_team AND all_games.home_goals IS NOT NULL)
OR ($id = all_games.away_team AND all_games.home_goals IS NOT NULL)

 

and as for reversing the results, try

ORDER BY `date` ASC

and see what happens

[MargateSteve]

ORDER BY `date` ASC

and see what happens

That gives the first 5 records, by date. I actually want the last (most recent) 5 and then order just those 5 by ASC.

 

Steve

[joel24]

oh!

totally misunderstood what you were after

 

I dare say you'll have to pull those most recent 5 and then use some PHP trickery to order them unless someone more learned in MySQL can help?

 

If you're just echoing a simple table from it, add each row value to an array like

$values=array();
while ($row=mysql_fetch_array($sql)) 
{ $values[]= "<tr>
<td>column1: {$row['column1']}</td>
<td>value: {$row['value1']}</td></tr>";

$values=array_reverse($values);
foreach ($values AS $row) {
echo $row;
}

 

... though looking at that situation and how convoluted it has become, I dare say someone else could suggest a much simpler way

 

[joel24]

just realised I forgot to close the while loop

$values=array();
while ($row=mysql_fetch_array($sql)) 
{ $values[]= "<tr>
<td>column1: {$row['column1']}</td>
<td>value: {$row['value1']}</td></tr>";
}
$values=array_reverse($values);
foreach ($values AS $row) {
echo $row;
}

[MargateSteve]

I think I have found a way to do this using subqueries, but I will need to upgrade to MySql 4.1 so will come back to this ones I have done that.

 

Thanks for the help

 

Steve

[MargateSteve]

This turned out to be extremely simple in the end. All I had to do was wrap the original query in a separate set of brackets and then us another ORDER BY afterwards.

$l5all = mysql_query("(SELECT *, DATE_FORMAT(`date`,'%W, %D %M %Y') AS showdate, HT.team_name as HomeTeam, VT.team_name as AwayTeam, COMP.comp_short_name as CP
FROM all_games
JOIN teams as HT ON all_games.home_team = HT.team_id
JOIN teams as VT ON all_games.away_team = VT.team_id 
JOIN competitions as COMP ON all_games.comp = COMP.comp_id 
WHERE $id IN (all_games.home_team, all_games.away_team) AND all_games.home_goals IS NOT NULL 
ORDER BY date DESC LIMIT 5) ORDER BY date ASC
")

Steve