PDA

View Full Version : Ascending vs. Decending Order



Medyman
10-05-2007, 04:48 PM
Hey all...

I have a PHP page querying a MySQL database for a list of articles (and related information).

Here is that PHP:


<?
$id=$_REQUEST['id'];
$sql="select * from fiction where id=$id";
$result=mysql_query($sql);
while($rs=mysql_fetch_array($result))
{
?>

<div class="featured">
<span class="h7"><?=$rs['title'];?></span><br />
<img src="cms/upload/<?=$rs['image'];?>"><br />
<span class="h8"><?=$rs['content'];?></span>
</div>

<?
}
?>

It works fine...but not exactly how I'd like it to.
It displays the entries (made through a backend) in the order they were entered (i.e. the newest entry is at the bottom). I'd like to reverse this so the newest entry is at the top.

I know I have to change the SQL query but don't know enough PHP to know how to. Can someone help me out here?

boogyman
10-05-2007, 05:19 PM
$sql="select * from fiction where id=$id order by id desc";

so long as the ids are auto incremented, this will return the newest registered first because their id will be greater

and if you wanted to limit the results per page you could apply pagination techniques.


$sql="select * from fiction where id=$id limit 10 order by id desc";

rdutton
10-05-2007, 05:55 PM
Since your existing query says:
select * from fiction where id=$id
I assume that the 'id' field relates to some kind of category and that all the records returned have the same 'id' value.. if this is the case then boogyman's suggestion wont work for you however his method is correct.

Do you have an auto-incrementing primary key field for the 'fiction' table? If you do, then use boogyman's suggestion but rather than use the ID field in the order by clause, use the primary key of the fiction table.

If you do not have an auto-incrementing primary key already, I believe that if you create one, they records 'should' be assigned a key in the order of creation.

Alternatively you can add a timestamp or datetime column to your fiction table which you can use to store the date-created. I believe timestamp columns are automatically populated on creation of the record, so this could be handy in your case.