Lunarpages Web Hosting Forum

Author Topic: Reverse order of MySQL results w/o a field to ORDER BY  (Read 8930 times)

124256

  • Guest
Reverse order of MySQL results w/o a field to ORDER BY
« on: August 21, 2006, 08:41:36 PM »
I have a table whose results I need to display in reverse order of when each row was created. Since if I don't specify any ORDER BY, I get results starting with the first one I created, is there a way to start with the last one I created? There is currently no field specifying an ID or a date/time and I would rather not make one. Is there a something I can do with the SQL querry or in PHP that will simply reverse the order of the rows outputted?

Offline JimBrown

  • Knowing enough to be dangerous!
  • Jabba the Hutt
  • *****
  • Posts: 738
    • Radio Control Aircraft Gallery
Re: Reverse order of MySQL results w/o a field to ORDER BY
« Reply #1 on: August 22, 2006, 06:08:51 AM »
How are you populating the table now? Can you show the PHP code you are using?

...jim

Offline Nibbler

  • 21st century digital boy
  • Master Jedi
  • *****
  • Posts: 1168
    • Coppermine
Re: Reverse order of MySQL results w/o a field to ORDER BY
« Reply #2 on: August 22, 2006, 06:45:20 AM »
Put the results into an array and then reverse the array. Using an auto incremented field is the most reliable way.
Missing since 1983


Offline StephanieŽ

  • Master Jedi
  • *****
  • Posts: 1090
Re: Reverse order of MySQL results w/o a field to ORDER BY
« Reply #3 on: August 22, 2006, 06:48:46 AM »
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]

The [] means that the WHERE statement is optional.
If a WHERE clause exists, it comes before the ORDER BY clause. ASC will show the results in ascending order.
DESC will show the results in descending order. If neither is specified, the default is ASC.

Is there a specific reason you don't want to use ORDER BY ? Or does that help you?


124256

  • Guest
Re: Reverse order of MySQL results w/o a field to ORDER BY
« Reply #4 on: August 22, 2006, 10:07:39 AM »
Ok first my querry looks something like this:
$query = "SELECT name, description, location FROM fum WHERE page='$page';";
$result = mysql_query ($query);


And the relavant part of the part that displays the data is here:
while ($get_info = mysql_fetch_row($result)){
   echo "<tr class='$row'>";
   echo "<td>";
   echo "<p><a href='forms/$get_info[2]'>$get_info[0]</a></p>";
   echo "</td>";
   echo "<td>";
   echo "<p>$get_info[1]</p>";
   echo "</td>";
}

Taking the "just reverse the array" idea, how would I accomplish this? Is there a way to just start fetching rows out of $result in reverse order?

Offline Nibbler

  • 21st century digital boy
  • Master Jedi
  • *****
  • Posts: 1168
    • Coppermine
Re: Reverse order of MySQL results w/o a field to ORDER BY
« Reply #5 on: August 22, 2006, 11:12:36 AM »
Code: [Select]

$rowset = array();

while ($get_info = mysql_fetch_row($result)) $rowset[]= $get_info;

$rowset = array_reverse($rowset);

foreach ($rowset as $get_info){
   echo "<tr class='$row'>";
   echo "<td>";
   echo "<p><a href='forms/$get_info[2]'>$get_info[0]</a></p>";
   echo "</td>";
   echo "<td>";
   echo "<p>$get_info[1]</p>";
   echo "</td>";
}
Missing since 1983


Offline JimBrown

  • Knowing enough to be dangerous!
  • Jabba the Hutt
  • *****
  • Posts: 738
    • Radio Control Aircraft Gallery
Re: Reverse order of MySQL results w/o a field to ORDER BY
« Reply #6 on: August 22, 2006, 11:38:24 AM »
There are probably better ways to do this, but here's something off the top of my head...

As Nibbler said, get all the results into an array, then reverse sort the array, then step through the array in your display loop.

You'll need two loops to do this. The first one gets all the results into an array. Then you reverse sort that array. The second loop displays the data from the reversed array.

I think this will work... :-)

Code: [Select]
$query = "SELECT name, description, location FROM fum WHERE page='$page';";
$result = mysql_query ($query);

$all_info = array();
while ($get_info = mysql_fetch_row($result)){
  $all_info[] = $get_info;
}

$all_info = array_reverse($all_info);

foreach($all_info as $get_info) {
   echo "<tr class='$row'>";
   echo "<td>";
   echo "<p><a href='forms/$get_info[2]'>$get_info[0]</a></p>";
   echo "</td>";
   echo "<td>";
   echo "<p>$get_info[1]</p>";
   echo "</td>";
}

You may have to tweak this a bit.

...jim

Edit: Nibbler beat me to it... I guess I'm a bit slow today.  :notme:

124256

  • Guest
Re: Reverse order of MySQL results w/o a field to ORDER BY
« Reply #7 on: August 22, 2006, 05:30:39 PM »
Thanks Jim and Nibbler. I'm gonna have to examine that script a bit and see how you accomplished so much in so few lines. Thanks again. It worked on my first test, something the internet doesn't usually grace me with :D