ORDER BY date problem when used with UNION

[vandelay]

I've been looking for a solution to this problem for a while now, but I can't find one. Hopefully somebody here can help me out.

 

I have the following statement:

 

$checkUpload_sql = "SELECT id, artist, song, nothing, date, count

FROM upload

WHERE artist = '$follow'

UNION SELECT null, username, null2, bulletin, date, null3

FROM bulletin

WHERE username = '$follow'

ORDER BY date DESC";

 

When I display the rows from my database it works, however they are in the wrong order. It isn't ordering it by date. I've run the same query in mysql and it displays and orders them correctly. It's just not working on when I display it on my webpage.

 

Any help would be appreciated.

[kickstart]

Hi

 

Think that should work. You can try forcing it by putting brackets around each SELECT statement, but it shouldn't make a difference:-

 

$checkUpload_sql = "(SELECT id, artist, song, nothing, date, count
         FROM upload
         WHERE artist = '$follow')
         UNION (SELECT null, username, null2, bulletin, date, null3
         FROM bulletin
         WHERE username = '$follow')
         ORDER BY date DESC";

 

However one thing that is possible would be if the date column is not a date type field. If it is just a char in dd/mm/ccyy format then the order would be as a character field rather than a date.

 

All the best

 

Keith

[vandelay]

Hey Keith, thanks for the reply.

 

Yeah, I've tried forcing it with brackets but nothing changed. However, I've not tried changing the date data type. I'll have to change my insert query on another page though because I use the date as a timestamp when a new record is added. I'll try it out now.

 

edit: Tried the second solution but it didn't work.

 

This is driving me crazy!

[Muddy_Funster]

It might be getting upset because date is a reserved word, try putting backticks around date and see how it gets on.

 

And, am I the only one who has found that UNION can be a tempremental bugger at the best of times?

[vandelay]

Tried using backticks around date but there was no change in the order.

 

Oh yeah, I hate using UNION too, but unfortunately I've got to use it in this instance.

[Muddy_Funster]

Clutching at straws here, but have you tried changing the date selection to

, DATE(`date`) AS `Date`,

[PFMaBiSmAd]

It would help if you showed what you are getting as a result. It is likely that your date is something that cannot be directly sorted.

[vandelay]

Yeah, I'll post the rest of the code:

 

//follow list
        $followList_sql = "SELECT * FROM follow
	WHERE username = '$user'";
	$getFollow = mysql_query($followList_sql);
	$getFollow_RecordCount = mysql_num_rows($getFollow);

	//display message is not following anybody
	if($getFollow_RecordCount == 0){
				echo 'You are not following anyone. You can explore tracks on the <a href="browse.php">browse page</a>.';
				echo '<br/>';
				echo "Most recent tracks";
				echo '<br/>';
				$recentTracks_sql = "SELECT * FROM upload
				WHERE artist != '$user'
				ORDER BY date DESC
				LIMIT 0, 5";
				$getRecentTracks = mysql_query($recentTracks_sql);
				$getRecentTracks_RecordCount = mysql_num_rows($getRecentTracks);
				while ($row = mysql_fetch_array($getRecentTracks)){
					$track = $row['song'];
					echo $track;
				}
			}
		else{
	while ($row = mysql_fetch_array($getFollow)){
		$follow = $row["follow"];	

		//check and display follow list
		$checkUpload_sql = "(SELECT id, artist, song, nothing, date, count
		FROM upload
		WHERE artist = '$follow')
		UNION (SELECT null, username, null2, bulletin, date, null3
		FROM bulletin
		WHERE username = '$follow')
		ORDER BY `date` DESC";
		$getUpload = mysql_query($checkUpload_sql);
		$getUpload_RecordCount = mysql_num_rows($getUpload);		

		while ($row = mysql_fetch_array($getUpload)){					
			$song = $row['song'];
			$bulletin = $row['nothing'];
			$id = $row['id'];
			$date = $row['date'];
			$count = $row['count'];
			if($song == NULL){ 
			?>
			<table id="post">
			<tr>
			<td>
				<?php echo $bulletin; ?>
				</td>
			</tr>
			<tr>
			<td>
				<?php echo agoDate($date) . " ago "; 
				echo '<a href="user.php?user='.$follow.'">'.$follow.'</a>'; ?> 
				</td>
			</tr>
			</table>
                <br/>
                <?php
			}
			else{
				?>
            <table id="post">
            <tr>
            <td class="song">
				<?php echo ''.$song.'';
				?>
                    </td>
                    <td class"playButton">
				<?php echo '<button onclick=javascript:play('.$id.') id="button" type="button">play</button>'; ?>
                    </td>
                    <td class="playCount">
				<?php echo ''.$count.' plays'; ?>
                    </td>
				</tr>
                    <tr>
                    <td>
				<?php echo agoDate($date) . " ago "; 
				echo '<a href="user.php?user='.$follow.'">'.$follow.'</a>';?>
                    </td>
                    
                </tr>
                </table>
                <br/>
                    <?php
			}

		}

[vandelay]

Also I'm using this function to get the time it was posted ago. e.g. 2 days ago:

 

function agoDate( $tDate ) 
{  
    $time_stamp = time() - strtotime( str_replace( "-", "/", $tDate ) );  
    if( $time_stamp > 31536000 ) 
    { 
        $ago = round( $time_stamp / 31536000, 0 ) . ' year'; 
    } 
    elseif( $time_stamp > 2419200 ) 
    { 
        $ago = round( $time_stamp / 2419200, 0 ) . ' month'; 
    } 
    elseif( $time_stamp > 604800 ) 
    { 
        $ago = round( $time_stamp / 604800, 0 ) . ' week'; 
    } 
    elseif( $time_stamp > 86400 ) 
    { 
        $ago = round( $time_stamp / 86400, 0 ) . ' day'; 
    } 
    elseif( $time_stamp > 3600 ) 
    { 
        $ago = round( $time_stamp / 3600, 0 ) . ' hour'; 
    } 
    elseif( $time_stamp > 60 ) 
    { 
        $ago = round( $time_stamp / 60, 0) . ' minute'; 
    } 
    else 
    { 
        $ago = $time_stamp . ' second'; 
    } 
     
    if( $ago > 1 ) 
    { 
        $ago .= 's'; 
    } 
    return $ago;  
} 

[fenway]

Use code tags next time.