Problem with array_reverse

MargateSteve · January 26, 2011

I am trying to reverse the results of a query in the output but cannot quite get it working. Basically, I am trying to get the most recent five records by date and then echo them in date ascending order.

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

My query is

$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");
};

and the html is

if(!mysql_num_rows($tpall_games2)) {
  echo "<tr><td style=\"text-align:left\" COLSPAN=\"8\">No records returned</td></tr>";
} 
  else 
        {
while ($row_tpall_gamesrev = array_reverse(mysql_fetch_array($tpall_games2, true))){
       if ($row_tpall_gamesrev['home_team']==$id)
	    {
	        if ($row_tpall_gamesrev['date']=="")
	                  echo  '<tr style="font-weight:bold"><td class="lftplcell" >TBA</td>';
  	                else		
  	                  echo '<tr style="font-weight:bold">
	                <td class="lftplcell" >'.$row_tpall_gamesrev['showdate'].'</td>';
                  echo' <td class="lftplcell" >.............

I have looked up a lot of array_reverse samples but none of them reversed the results and also tried to do it in a different way using a select subquery but I could not make that work at all.

Is there something simple that I am missing (again!)?

Steve

Pikachu2000 · January 26, 2011

Each iteration of the while() loop only holds the query result of one record, not the entire record set. For that reason, array_reverse won't do what you're attempting to have it do. Your best bet would be to alter the query string to produce the records in the proper order.

MargateSteve · January 26, 2011

How would I go about that?

Obviously I can get the right results buy using ORDER BY date DESC LIMIT 5 but i want to print it as ORDER BY date ASC.

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.

Steve

Pikachu2000 · January 27, 2011

I'm by no means any good with JOIN queries, and I can't test this without knowing your DB structure, but try this and see what you get with it.

$tpall_games2 = mysql_query("
SELECT * FROM (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) AS z 
ORDER BY `date` ASC");

MargateSteve · January 27, 2011

Query to show fields from All games Team Page table failed

It is really puzzling as if I just use the subquery part it works fine but once the outer select is put in, it just does not work. I cannot see any reason why it wont work.

The tables, with some sample data are

CREATE TABLE `all_games` (
  `all_games_id` int(11) NOT NULL auto_increment,
  `date` date default NULL,
  `time` time default NULL,
  `comp` int(11) NOT NULL default '1',
  `round` tinyint(4) default NULL,
  `replay` char(1) default NULL,
  `home_team` int(11) NOT NULL default '1',
  `away_team` int(11) NOT NULL default '1',
  `home_goals` int(11) default NULL,
  `away_goals` int(11) default NULL,
  `home_ht` int(11) default NULL,
  `away_ht` int(11) default NULL,
  `attendance` int(11) default NULL,
  PRIMARY KEY  (`all_games_id`)
) TYPE=MyISAM AUTO_INCREMENT=732 ;

INSERT INTO `all_games` VALUES (1, '2009-08-15', '15:00:00', 1, NULL, '0', 19, 42, 4, 0, 0, 0, 508);
INSERT INTO `all_games` VALUES (2, '2009-08-18', '19:45:00', 1, NULL, '0', 42, 29, 0, 4, 0, 0, 653);
INSERT INTO `all_games` VALUES (3, '2009-08-22', '15:00:00', 1, NULL, '0', 42, 30, 2, 1, 0, 0, 345);
INSERT INTO `all_games` VALUES (4, '2009-08-24', '19:45:00', 1, NULL, '0', 1, 42, 0, 3, 0, 0, 243);
INSERT INTO `all_games` VALUES (5, '2009-08-29', '15:00:00', 1, NULL, '0', 11, 42, 3, 0, 0, 0, 156);
INSERT INTO `all_games` VALUES (6, '2009-08-31', '15:00:00', 1, NULL, '0', 42, 7, 0, 1, 0, 0, 423);
INSERT INTO `all_games` VALUES (7, '2009-09-05', '15:00:00', 1, NULL, '0', 31, 42, 2, 2, 0, 0, 120);
INSERT INTO `all_games` VALUES (8, '2009-09-12', '15:00:00', 2, 2, '0', 42, 19, 2, 2, 0, 0, 402);
INSERT INTO `all_games` VALUES (9, '2009-09-15', '19:45:00', 2, 2, 'r', 19, 42, 3, 2, 0, 0, 301);
INSERT INTO `all_games` VALUES (10, '2009-09-19', '15:00:00', 1, NULL, '0', 42, 15, 1, 3, 0, 0, 356);
INSERT INTO `all_games` VALUES (11, '2009-09-22', '19:45:00', 1, NULL, '0', 13, 42, 1, 2, 0, 0, 159);



CREATE TABLE `competitions` (
  `comp_id` int(11) NOT NULL auto_increment,
  `comp_name` varchar(45) default NULL,
  `comp_short_name` varchar(4) default NULL,
  `logo` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`comp_id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

INSERT INTO `competitions` VALUES (1, 'Isthmian League Premier Division', 'ILP', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/tRymanLeague.png');
INSERT INTO `competitions` VALUES (2, 'FA Cup', 'FAC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/facup.png');
INSERT INTO `competitions` VALUES (3, 'FA Trophy', 'FAT', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/fattophy.png');
INSERT INTO `competitions` VALUES (4, 'Kent Senior Cup', 'KSC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/kentseniorcup.png');
INSERT INTO `competitions` VALUES (5, 'Isthmian League Cup', 'ILC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/isthmianleaguecup.png');
INSERT INTO `competitions` VALUES (6, 'Friendly', 'FR', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/friendly.png');
INSERT INTO `competitions` VALUES (7, 'Kent Messenger Cup', 'KMC', '');

CREATE TABLE `teams` (
  `team_id` int(11) NOT NULL auto_increment,
  `team_name` varchar(45) default NULL,
  `short_name` varchar(5) default NULL,
  `badge` varchar(255) default NULL,
  `year_formed` int(11) default NULL,
  `nickname` varchar(255) default NULL,
  `previous_names` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  `twitter` varchar(255) default NULL,
  `facebook` varchar(255) default NULL,
  `telephone_number` varchar(255) default NULL,
  `chairman` varchar(255) default NULL,
  `manager` varchar(255) default NULL,
  `home_colours` varchar(255) default NULL,
  `away_colours` varchar(255) default NULL,
  `stadium` int(11) default NULL,
  `2009/10` varchar(255) default NULL,
  PRIMARY KEY  (`team_id`)
) TYPE=MyISAM AUTO_INCREMENT=74 ;

INSERT INTO `teams` VALUES (1, 'Aveley', 'Ave', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/aveley.png', 1927, 'The Millers', 'None', 'http://www.aveleyfc.net/', NULL, NULL, '01708865940', 'Graham Gennings', 'Alan Kimble', 'Black & Blue/Blue/Blue', 'Black & White/White/White', 3, 'Isthmian League Premier Division, 3rd');
INSERT INTO `teams` VALUES (2, 'Billericay Town', 'Bill', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/billericaytown.png', 1880, 'Ricay, The Blues', 'None', 'http://www.billericaytownfc.co.uk/', 'http://twitter.com/BTFC', 'http://www.facebook.com/billericaytownfc', '01277652188', 'Steve Kent', 'Craig Edwards', 'Blue/White/Blue', 'White/Black/White', 2, 'Isthmian Premier, 13th');
INSERT INTO `teams` VALUES (3, 'Bury Town', 'Bur', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/burytown.png', 1872, 'The Blues', 'Bury St Edmunds, Bury United', 'http://www.burytownfc.co.uk', NULL, NULL, '01277999999', 'Russell Ward', 'Richard Wilkins', 'Blue & Red/Blue/Blue', 'White/Black/White', 10, 'Southern League Division One Midlands, 1st');
INSERT INTO `teams` VALUES (4, 'Canvey Island', 'Can', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/canveyisland.png', 1926, 'The Gulls', 'None', 'http://www.canveyislandfc.com', 'http://twitter.com/CIFC', NULL, NULL, 'George Frost', 'John Batch', 'Yellow/Blue/White', 'White/White/White', 11, 'Isthmian League Premier Division, 16th');
INSERT INTO `teams` VALUES (5, 'Carshalton Athletic', 'Car', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/carshaltonathletic.png', 1905, 'The Robins', 'Mill Lane Misson', 'http://www.carshaltonathletic.co.uk/', 'http://twitter.com/carshaltonath', NULL, '02086428658', 'Harry Driver', 'Mark Butler', 'Red/Red/Red', 'Maroon/White/Maroon', 9, 'Isthmian League Premier Division, 17th');
INSERT INTO `teams` VALUES (6, 'Concord Rangers', 'Con', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/concordrangers.png', 1967, 'Beach Boys', 'None', 'http://www.concordrangers.co.uk/', 'http://twitter.com/ConcordRangers', NULL, '01268691780', 'Antony Smith', 'Danny Scopes / Danny Cowley', 'Yellow/Blue/Blue', 'TBA', 12, NULL);
INSERT INTO `teams` VALUES (7, 'Cray Wanderers', 'Cray', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/craywanderers.png', 1860, 'The Wands', 'None', 'http://www.craywands.co.uk', 'http://twitter.com/craywanderers', NULL, '02084605291', 'Gary Hillman', 'Ian Jenkins', 'Amber/Black/Amber', 'Blue/White/White', 13, 'Isthmian League Premier Division, 15th');
INSERT INTO `teams` VALUES (8, 'Croydon Athletic', 'Croy', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/croydonathletic.png', 1986, 'The Rams', 'Wandsworth & Norwood', 'http://croydonathletic.net', NULL, NULL, '0208664834', 'TBA', 'Dave Garland & Bob Langford', 'Maroon & White/Maroon/Maroon', 'Yellow/Blue/Blue', 14, 'Isthmian League Division One South, 1st');
INSERT INTO `teams` VALUES (9, 'Folkestone Invicta', 'Fol', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/folkestoneinvicta.png', 1936, 'Invicta, The Seasiders', 'None', 'http://www.folkestoneinvicta.co.uk/', 'http://twitter.com/FIFC', NULL, '01303257461', 'TBA', 'Neil Cugley', 'Amber/Black/Amber', 'Red/White/Red', 15, 'Isthmian League Division One South, 2nd');
INSERT INTO `teams` VALUES (10, 'Grays Athletic', 'Gra', 'N/A', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `teams` VALUES (11, 'Harrow Borough', 'Har', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/harrowborough.png', 1933, 'The Boro, The Reds', 'Roxoinian, Harrow Town', 'http://www.harrowboro.com/', 'http://twitter.com/harrowboro_fc', NULL, '08706091959', 'Peter Rogers', 'David Howell', 'Red/Red/Red', 'White/White/Black', 16, 'Isthmian League Premier Division, 14th');
INSERT INTO `teams` VALUES (12, 'Hastings United', 'Has', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/hastingsunited.png', 1894, 'The Arrows, The U''s', 'Hastings Town, Hastings & St. Leonards', 'http://www.hastingsunitedfc.co.uk/', 'http://twitter.com/hastingsufc', NULL, '01424444635', 'David Walter', 'Tony Dolby', 'Maroon/Maroon/Maroon', 'White/White/White', 17, 'Isthmian League Premier Division, 7th');
INSERT INTO `teams` VALUES (13, 'Hendon', 'Hen', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/hendon.png', 1908, 'The Greens, The Dons', 'Christ Church Hampstead, Hampstead Town, Hampstead, Golders Green', 'http://www.hendonfc.net/', 'http://twitter.com/HendonFC', NULL, '02089083553', 'Simon Lawrence', 'Gary McCann', 'Green/Green/Green', 'Blue/Blue/Blue', 18, 'Isthmian League Premier Division, 10th');
INSERT INTO `teams` VALUES (14, 'AFC Hornchurch', 'AFC', 'http://www.margate-fc.com/edit/news/data/upimages/images/Badges/afchornchurch.png', 1923, 'The Urchins', 'Upminster Wanderers, Hornchurch & Upminster', 'http://www.afchornchurch.com/', 'http://twitter.com/AFCHornchurch', 'http://www.facebook.com/AFCHornchurch', '01708220080', 'TBA', 'Colin McBride', 'Red & White/Black/Red', 'Blue & White/Blue/Blue', 1, 'Isthmian League Premier Division, 9th');

The site, if it is relevant, is running PHP 4.4.9 and MySQL 4.0

Steve

MargateSteve · January 30, 2011

I have tried everything over the last few days to try to get this to work but to no avail. I have even looked into using MAX to see if there was a way of selecting the MAX 5 dates then sorting them in ascending order but it seems as though MAX is restricted to just one record.

I have seen this done on several sites so unless they are using a different language there must be a way to do it. It does seem a simple thing in principle but I just cannot get it to work!

Steve

Pikachu2000 · January 30, 2011

The only other thing I can think of is to get an accurate count of the records that will be returned by the query, then use that result to LIMIT the query to only the last 5 records, much like you'd do for pagination. (I still think it can be done with one query, but someone better with SQL syntax than me will need to look at it.)

pseudo:

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

MargateSteve · February 1, 2011

Had a bit of a play with that but am still firmly stuck. I have gone for this on the query side (with new query names)

$l5nr = "SELECT count(all_games_id),  
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 AND all_games.date IS NOT NULL
ORDER BY `date` DESC 

";
$result = mysql_query($l5nr);
$array = mysql_fetch_array($result);

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

$l5r = mysql_query("
SELECT all_games_id ,  
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 AND all_games.date IS NOT NULL
ORDER BY `date` asc LIMIT $start, $display_num
");
if (!$l5r) {
    die("Query to show fields from All games Team Page table failed");
};

and this on the html side

if(!mysql_num_rows($l5r)) {
  echo "<tr><td style=\"text-align:left\" COLSPAN=\"8\">No records returned</td></tr>";
} 
  else 
    {
while ($row_l5r = mysql_fetch_assoc($l5r)){..............

As things stand, it returns 'No Records Returned'.

If I remove the 'LIMIT $start, $display_num' it shows the earliest 5 results as it is sorting it by the requested 'date ASC' but without 'LIMIT $start, $display_num' it just goes to the first five, not the last 5 as I need.

One other strange thing (and I will post this code right at the very end) is that the results returned give the right games, but none of the conditions in the html part work correctly, even though they are exactly the same as a very similar part elsewhere on the page.

Thanks for all the help and patience.

Steve

The full html which is ignoring requests

if(!mysql_num_rows($l5r)) {
  echo "<tr><td style=\"text-align:left\" COLSPAN=\"8\">No records returned</td></tr>";
} 
  else 
    {
while ($row_l5r = mysql_fetch_assoc($l5r)){ 
        if ($row_l5r['home_team']==$id)
	   {
	    if ($row_l5r['date']=="")
	          echo  '<tr style="font-weight:bold"><td class="lftplcell" >TBA</td>';
  	        else		
  	          echo '<tr style="font-weight:bold">
	    <td class="lftplcell" >'.$row_l5r['showdate'].'</td>	
<td class="lftplcell" >'.$row_l5r['CP'].'</td>	
<td class="lftplcell" > H </td>	
<td class="lftplcell" >'.$row_l5r['AwayTeam']. '</td>
</td><td class="lftplcell" >';
	if ($row_l5r['home_goals'] > $row_l5r['away_goals'])
	{echo 'W';}
	elseif ($row_l5r['away_goals'] > $row_l5r['home_goals'])
	{echo 'L';}
	elseif ($row_l5r['away_goals'] == '0')
	{echo 'd';}
	else 
	{echo 'D';}
		;
	echo'<td class="lftplcell" >'.$row_l5r['home_goals']. ' - '.$row_l5r['away_goals']. '</td>

	<td class="rtplcell" >'.$row_l5r['attendance']. '</td>
	</tr>'; }
else
	{
		  if ($row_l5r['date']=="")
	   echo  '<td class="lftplcell" >TBA</td>';
  	   else		
  	   echo '
	<td class="lftplcell" >'.$row_l5r['showdate'].'</td>';
   echo'	
	<td class="lftplcell" >'.$row_l5r['CP'].'</td>	
	<td class="lftplcell" > A </td>	
	 <td class="lftplcell" >'.$row_l5r['HomeTeam']. '</td>';
	echo '</td><td class="lftplcell" >';
	if ($row_l5r['away_goals'] > $row_l5r['home_goals'])
	{echo 'W';}
	elseif ($row_l5r['home_goals'] > $row_l5r['away_goals'])
	{echo 'L';}
	elseif ($row_l5r['away_goals'] == '0')
	{echo 'd';}
	else 
	{echo 'D';}
		;
	echo'<td class="lftplcell" >'.$row_l5r['away_goals']. ' - '.$row_l5r['home_goals']. '</td>


	<td class="rtplcell" >'.$row_l5r['attendance']. '</td>
	</tr>';
       }}}

This part shows up as 'TBA' whether there is a date is entered or not.

 if ($row_l5r['date']=="")
	          echo  '<tr style="font-weight:bold"><td class="lftplcell" >TBA</td>';
  	        else		
  	          echo '<tr style="font-weight:bold">
	    <td class="lftplcell" >'.$row_l5r['showdate'].'</td>

MargateSteve · February 2, 2011

Following your comment about 'someone better with SQL syntax' Pikachu2000, I am going to post this in the MySQL forum.

I hope this is not seen as double posting and if I get a solution in either MySQL or PHP I will mark both solved and post a relevant link in either one.

Steve

MargateSteve · February 24, 2011

This turned out to be extremely simple in the end just using SQL. 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

Sign In

Problem with array_reverse

Recommended Posts

MargateSteve

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information