CODEWITHSUNDEEP
phpmysqldate
Irfan Gondal
Irfan Gondal

Reputation: 161

select records from sql database order by date DESC, but I want show records in table in date ASC

I have 7 records in a table but I want select only last 5 records sorting by date DESC. And I want to show these records in my table sorting by date ASC.

My Code:

$select_record = mysqli_query($con, "select * from table order by date DESC limit 5");
while($row=mysqli_fetch_array($select_record)){

$date = $row['date'];

echo "<tr><td>$date<br></td></tr>";

Records in My Table like this

Date
2014-05-15
2014-04-15
2014-06-15
2014-02-15
2014-07-15
2014-01-15
2014-03-15

My code give me result like this

Date
2014-07-15
2014-06-15
2014-05-15
2014-04-15
2014-03-15

But I want result like this

Date
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15

Upvotes: 3

Views: 5771

Answers (6)

mitkosoft
mitkosoft

Reputation: 5316

Do second SELECT on top of first one: 

SELECT t.* FROM 
   (SELECT * FROM `table` ORDER BY `date` DESC LIMIT 5) t 
ORDER BY t.`date` ASC

short version:

(SELECT * FROM `table` ORDER BY `date` DESC LIMIT 5) ORDER BY `date` ASC

Upvotes: 5

Bastian
Bastian

Reputation: 261

You can also save your result in array. Then you can use sort($array);

PHP function Sort

Upvotes: 0

pmakholm
pmakholm

Reputation: 1548

The simple solution would be to extract the rows and the reverse the list in PHP land. This would be my solution in your simple case based on the result size.

If you really want to do it without having to store the full result in PHP land you could do it with a sub-select. This would mean replacing your query expression with:

$select_record = mysqli_query($con, "SELECT * FROM (select * from table order by date DESC limit 5) AS result ORDER BY date");

But I would think this to be less maintainable than just reversing the result in PHP.

Upvotes: 0

CiprianD
CiprianD

Reputation: 512

Try this:

$select_record = mysqli_query($con, "select * from table order by date DESC limit 5");

// Sort in chronological order.
usort($select_record, function($a, $b) {
  return strcmp($a['db'], $b['db']);
});

while($row=mysqli_fetch_array($select_record)){

$date = $row['date'];

echo "<tr><td>$date<br></td></tr>";

Upvotes: 0

Hamza Zafeer
Hamza Zafeer

Reputation: 2436

Store Your dates in Array and then use array_reverse() PHP Function to get the result in reverse order.

array_reverse — Return an array with elements in reverse order

Reverse array values while keeping keys

Upvotes: 0

The Guest
The Guest

Reputation: 708

Edit your while loop:

Instead of doing this:

while($row=mysqli_fetch_array($select_record)){

$date = $row['date'];

echo "<tr><td>$date<br></td></tr>";

}

Try doing this:

    create an array that can store your date values retrieved from query 
    while($row=mysqli_fetch_array($select_record)){

    $date = $row['date'];
    Add $date to the array you created

    }
    Iterate the array from the END to START and print dates .
    {
       echo "<tr><td>$date<br></td></tr>";
    }

Upvotes: 0

Related Questions