Order of posts when consulting the database of my CMS

Asked

Viewed 81 times

2

I am developing a Site with CMS. Everything works perfectly just that I would like the latest posts to be at the top of the page. What’s happening now is that the last posts go to the bottom of the page just wanted the order to be opposite.

PHP Class "Article"

<?php
class Article {
  public function fetch_all() {
    global $pdo;

    $query = $pdo->prepare("SELECT * FROM articles");
    $query->execute();

    return $query->fetchAll();
  }

  public function fetch_data($article_id) {
    global $pdo;

    $query = $pdo->prepare("SELECT * FROM articles WHERE article_id=?");
    $query->bindValue(1, $article_id);
    $query->execute();

    return $query->fetch();
  }
}
?>

index php.

<?php
include_once('includes/connection.php');
include_once('includes/article.php');

$article = new Article;
$articles = $article->fetch_all();
?>
<html>
<head>
    <title>CMS Tutorial</title>
    <link rel ="stylesheet" href="assets/style.css"/>
</head>
<body>
  <div class="container">
    <a href="index.php" id ="logo">CMS</a>
    <ol>
      <?php foreach ($articles as $article) { ?>
      <li>
      <?php echo $article['article_title']; ?>
        - <small>
            posted <?php echo date('l jS', $article['article_timestamp']); ?>
          </small>
      </li>
      <p><?php echo $article['article_content']; ?></p>
      <?php }?>         
    </ol>
    <br>
    <small><a href="admin">admin</a></small>
  </div>
</body>
</html>
php mysql cms classification

  • You either sort by date or by insertion id?

3 answers

3

You are using which CMS. As you are showing. Your query should look like this if you are using PHP PDO:

public function fetch_all() {
  $sql  = "SELECT * FROM mytable";
  $stmt = Connection::prepare( $sql );
  $stmt->execute();

  return $stmt->fetchAll();
}

Add in your SQL the following part for it to pick up the in descending order.

public function fetch_all() {
  $sql  = "SELECT * FROM articles ORDER BY article_id DESC";
  $stmt = Connection::prepare( $sql );
  $stmt->execute();

  return $stmt->fetchAll();
}
  • 1

    I edited the code on top tried to add the "ORDER BY...." to the '$query = $Pdo->prepare("SELECT * FROM Articles");' but everything disappeared

  • 1

    @Filipe.Fonseca No offense, but the issue did not solve the problem in this answer. The query presented in the function fetch_all does not match the question in the same function. Also, the query in this answer does not wait for a parameter for the article_id, which in itself is already wrong because we are saying that we just want a record, where as the name of the function itself indicates it, the idea is to pull everything.

  • They edited this Zull. I didn’t do it. When I wrote it, it looked for all the data in descending form.

  • Arrumado Zuul. :)

  • @Randsonjarthis Zuul not Zull :)

3


From what I understand of your question and based on the comments you have already left in the existing answers, your table articles contains a field with the name article_timestamp.

Assuming the field actually contains data of the type Unix timestamp, one datetime or date, and that the stored value relates to the date of your registration, you can perform the query as follows:

$query = $pdo->prepare("SELECT * FROM articles ORDER BY article_timestamp DESC");
/*                      └──┬───┘ └─────┬─────┘ └──────────────┬──────────────┘     */
/*                         ↓           ↓                      ↓                    */
/*                    Seleccionar   da tabela         ordenado pelo campo          */
/*                       tudo       articles      article_timestamp de forma       */
/*                                                       descendente               */

Where are you saying you intend to collect everything from the table articles but ordered by the field article_timestamp downright.

Unrelated

Not related to the problem in question, but I noticed that in the Markup HTML in your question, you have a <p></p> out of context, that is, after you close the li but before closing the ol are applying a paragraph.

Such a situation is not allowed, you should put the paragraph within the li, before the closure of the:

<ol>
  <?php
  foreach ($articles as $article) {
    echo '
    <li>
      '.$article["article_title"].'
      - <small> posted '.date("l jS", $article["article_timestamp"]).'</small>
      <p>'.$article["article_content"].'</p>
    </li>';
  }
  ?>         
</ol>

The way you have it. Although it works well on some browsers by the same ignore this lapse, does not work well at all, particularly in the oldest.

  • Exactly, for the 3rd-4th time you helped me and in a concise and very accessible way, although I explained myself badly (it was not clear why I wanted it to be posted, but it was by the date Obgado

  • I’ve mended too many obgado

  • By the way Zuul, how can I date in Portuguese? in which part? dsculpa for the inconvenience

  • @Miguel It’s no bother, but it’s off-topic in this question :) Ideally, you will ask a new question about the presentation of a full date in Portuguese via PHP. You give an example of the date you have and how you expect it to stay in English. Soon you will have the solution :)

  • OK I’ll take care of it, you’re right...

0

Add ORDER BY "id" DESC for example to your SQL query that takes the POSTS from the database.

  • I’m very new, '$query = $Pdo->prepare('INSERT INTO Articles(article_title, article_content, article_timestamp) Values (?, ?)')' Add to this?

  • This seems to be the code you enter into the database, not the one you’re looking for... It’s not the index.php file I put on top of it?

  • I added the class that gets the data from the base... I tried to implement what you told me there, but it’s not working, it’s disappearing

Browser other questions tagged php mysql cms classification

You are not signed in. Login or sign up in order to post.