reverse sort

[globetrottingmike]

Is it possible to reverse sort results in a dynamic table?

 

I want to show the last 10 posts on a forum topic, so would normally create a recordset query with 'LIMIT 10 DESC' in it. When the results show in a table it will show the newest post at the top:

 

85

84

83

.. ..

76

74

 

however, I would like to show the last 10 posts, but with the most recent a the bottom of the table:

 

74

75

.. ..

83

74

85

 

(74-85 being the most recent 10 posts)

 

Thanks for any suggestions in advance

[Maq]

EDIT: Nvm.

[globetrottingmike]

EDIT: nevermind.

using ASC sounds logical, but would that just show the first 10 results?

[Maq]

I think you're going to have to use a subquery for this.

 

Something like:

 

SELECT * FROM table WHERE post_id IN(SELECT post_id FROM table ORDER BY date DESC LIMIT 10) ORDER BY date ASC;

[globetrottingmike]

I think you're going to have to use a subquery for this.

 

Something like:

 

SELECT * FROM table WHERE post_id IN(SELECT post_id FROM table ORDER BY date DESC LIMIT 10) ORDER BY date ASC;

Tried:

SELECT forum_posts.id_msg, forum_posts.date_msg, forum_posts.content_msg FROM forum_posts WHERE id_msg IN(SELECT id_msg FROM fourm_posts ORDER BY date_msg DESC LIMIT 10) ORDER BY forum_posts.id_msg ASC

And it showed error: 'This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' '

[premiso]

Subquerys only work in MySQL 4 and above. Chances are you are using MySQL 3.23 as that was the most popular version prior to 4+.

 

You will have to do 2 separate query's using the results to get this to work for you. Or use the array for sorting, although depending on what the date is stored as that might be tricky

[globetrottingmike]

You will have to do 2 separate query's using the results to get this to work for you.

 

Can you give me a little guidance on this. How would I go about linking the two queries?

[premiso]

<?php
// mysql_connect information here

$postSql = "SELECT post_id FROM table ORDER BY date DESC LIMIT 10";
$postResult = mysql_query($postSql) or die("SQL Was: {$postSql} <br />Error: " . mysql_error());

$postIds = array();
while ($row = mysql_fetch_assoc($postResult)) {
    $postIds[] = $row['post_id'];
}
$postIds = implode(", ", $postIds); 

$ascSql = "SELECT * FROM table WHERE post_id IN({$postIds}) ORDER BY date ASC";
$ascResult = mysql_query($ascSql) or die("SQL Was: {$ascSql} <br />Error: " . mysql_error());

while ($row = mysql_fetch_assoc($ascResult)) {
    $posts[] = $row; // or do your displaying information here.
}
?>

 

It is a bit more work then being able to do a subquery. But if you do not have that option, I do not know if there is another way. The above code will not just "work" you will have to modify the SQL table name and also provide the sql connection and manipulate the data in the asc portion to display/be stored how you want it to be.

[Maq]

Just to save you a step.  You can just give IN the array, as opposed to imploding it together.

[premiso]

Just to save you a step.  You can just give IN the array, as opposed to imploding it together.

 

....seriously? I never knew that...time for some experiments now

[fenway]

Just to save you a step.  You can just give IN the array, as opposed to imploding it together.

That's sounds like an accident.. I wouldn't rely on that.

[globetrottingmike]

OK, I have just tried:

<?php mysql_select_db($database_FCK2, $FCK2);
$query_rsort_desc = "SELECT numbers.id, numbers.`number` FROM numbers ORDER BY numbers.id DESC LIMIT 10";
$rsort_desc = mysql_query($query_rsort_desc, $FCK2) or die(mysql_error());
$row_rsort_desc = mysql_fetch_assoc($rsort_desc);
$totalRows_rsort_desc = mysql_num_rows($rsort_desc);

$Ids = array();
while ($row = mysql_fetch_assoc($idResult)) {
    $postIds[] = $row['id'];
}
$Ids = implode(", ", $Ids); 

mysql_select_db($database_FCK2, $FCK2);
$query_rsort_asc = "SELECT numbers.id, numbers.`number` FROM numbers WHERE id IN({$Ids}) ORDER BY numbers.id ASC";
$rsort_asc = mysql_query($query_rsort_asc, $FCK2) or die(mysql_error());
$row_rsort_asc = mysql_fetch_assoc($rsort_asc);
$totalRows_rsort_asc = mysql_num_rows($rsort_asc); ?>

 

However, returned:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/thefood/public_html/rsort2.php on line 43

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY numbers.id ASC' at line 1

 

Any ideas??