Display last record first.

[brosskgm]

I need some help how put the last record first and first record last.

 

Thanks

 

<?php
include("connect.php");

$query="SELECT * FROM MyNotes ";
$result=mysql_query($query);
$num = mysql_num_rows ($result);
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$username = mysql_result($result,$i,"username");
$notes = mysql_result($result,$i,"notes");
$id = mysql_result($result,$i,"id");

echo "<b>notes:</b> $notes<br>";
echo "<hr><BR>";

++$i; } } else { echo "The database is empty"; }?>

[schilly]

$query="SELECT * FROM MyNotes ORDER BY id DESC";

 

[brosskgm]

Thanks. I would have never stumbled on that.

 

Can you by chance explain how this tells it to do reverse so I understand?

 

Thanks so much.

Bob

 

[roopurt18]

http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

[brosskgm]

I'm real new at this.

 

I think I need to be taking classes, let me see if I'm getting 1/2 way there.

ORDER BY id DESC tells it to read last note posted by using DESC ?

 

Meaning, it looks at a time stamp of sorts when it was written to the database?

 

But how does it know to go reverse? all the id's are increased by one, and it's reading the largest 1st and smallest last.

 

Thanks

Bob

 

[roopurt18]

ORDER BY is a key-phrase that MySQL sees and says, "Ah, I'm going to sort the records."  MySQL then reads what comes next.

 

In your case: id

So MySQL says, "Ah!  I'm going to order by the values in column `id`."

 

This might be a profound thought to you, but you don't know what order the records are stored in the database.  You might be inclined to think, "Well they're stored in the order I inserted them, silly!"

 

You'd be wrong.

 

You see, your table is represented by a file on disk.  You might have 100 records in your table.  All of the records combined might require 4kb of disk space.  But if you were to look at the table's file on disk, it might be 2MB!  That's a lot larger than 4kb.

 

The reason is because MySQL assumes you will be storing lots of stuff in the table so it makes a file on disk larger than what it needs to actually store the data.  As you insert more and more records, the actual space required by the records grows closer to the file's actual size on disk.  When they approach close enough MySQL will make the file larger to accommodate many more records.

 

Now each record has a starting and ending location within the file.  When you delete a record, MySQL makes a note that the bytes in the file that correlate to that record are free to use for another record at a future time.

 

More simply put each table has an associated disk file where the records are put.  You have no idea where in that file a particular record might be.  Therefore a SELECT statement without an ORDER BY will return the records in an arbitrary and unpredictable order.

 

So even though your id is auto-incrementing.  Depending on how you insert and delete records over time, they might be in the file in this order:

1, 2, 3, 127, 56, 78, 9, 10, 11, 12, 14, 37...

 

So back to our ORDER BY.  MySQL sees ORDER BY `id` and says, "Ah!  I will take the records and sort them by `id`."

 

That turns:

1, 2, 3, 127, 56, 78, 9, 10, 11, 12, 14, 37...

into:

1, 2, 3, 9, 10, 11, 12, 14, 37, 56, 127

 

By default MySQL sorts in ASCending order.  When MySQL sees the DESC it says, "Ah, I must reverse the sort order!" and the list becomes:

127, 56, 37, 14, 12, 11, 10, 9, 3, 2, 1

 

MySQL doesn't look at any timestamp that I know of when sorting values unless you were to specify a timestamp column in the order by:

ORDER BY `my_column_that_has_a_timestamp_datatype`

[brosskgm]

Cool, thank you. That really helped. It was DESC that said read in reverse.

 

I was seeing DESC used in several examples in the link you sent me, but non explained to a point that you did. But the DESC reverse read is only for the id correct?

 

DESC used in any other table value is only for that value correct?

 

Thanks

Bob

 

[roopurt18]

Yes.  Let's say you had a `users` table and you wanted to sort by last name descending (z first, a last), first name ascending (a first, z last) and finally by age descending (100 before 1).

 

ORDER BY last_name desc, first_name, age desc

[Goafer]

Basically, its not sorting the records, its sorting the data within the records,

so if your id is 1,2,4,5 - then that is ASCENDing in order, whereas if you tell it to DESC then it would be 5,4,2,1.

 

Similarly, in the column 'surname' if you had:

Azjol, Bertram, Dexter - that would be ascending in its order

 

Ascending is the default, and will be performed unless you specify otherwise

 

[brosskgm]

OK, thanks. Helps trying to understand how it does what it does.