php mysql order by not working as it should

Question

Given the following query that returns the expected results:

SELECT * FROM `articles` ORDER BY article_date DESC

but in PHP the results are still ordered ASC.

<?php
header("Content-Type:application/json");
require_once('connection.php');

$sql = "SELECT article_name, article_text, article_date FROM articles ORDER BY article_date DESC";
$result = mysqli_query($conn, $sql);

$result_array = array();
$rows = array();

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        $rows[] = $row;
    }
}

http_response_code(200);
$result_array['status'] = 200;
$result_array['error'] = "";
$result_array['result'] = $rows;

exit(json_encode($result_array));
?>

I've tried different approaches like DATE_FORMAT or changing the type of the field to timestamp type.

My articles table looks like this:

CREATE TABLE `articles` (
  `id` int(11) NOT NULL,
  `article_name` varchar(255) NOT NULL,
  `article_text` text NOT NULL,
  `article_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Does anyone have any idea why this happens?

Your observations are not consistent with the query you showed us. Are you sure this is the query and code which actually resulted in an ascending order result set? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 15, 2017 at 13:27
the problem is on json_encode see the answer here: Json_encode changing the order of my query — Atmahadli
– Atmahadli, Commented Jul 15, 2017 at 13:31
Interesting, thank you @Atmahadli I will dig about this more. — Marian Pavel
– Marian Pavel, Commented Jul 15, 2017 at 13:52

HoogleyBoogley · Accepted Answer · 2017-07-15 14:00:47Z

1

First off, I would assume that your ID would be auto-increment, meaning that it would be in chronological order by the ID because after every post, a the ID would increase by one. So what I would suggest is this:

$sql = "SELECT * FROM articles ORDER BY id DESC";
$result = mysqli_query($conn, $sql);

If that doesn't work, tell me and I will help you out.

answered Jul 15, 2017 at 14:00

HoogleyBoogley

3402 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ivo P Over a year ago

You now assume articles are entered in order of the date. But I could very well today write stories about all last week's races, not doing in order

HoogleyBoogley Over a year ago

@IvoP In his SQL code, you can see that he is inserting the current timestamp into his table, meaning it would be inputted by order of date and ID would work.

Collectives™ on Stack Overflow

php mysql order by not working as it should

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related