Solved

Paginating MySQL query results in descending order

Posted on 2006-10-19
15
422 Views
Last Modified: 2006-12-04
I don't know where to post this, but its mostly PHP at this point so I will post in the PHP section.

I've got a MySQL DB that this PHP script is using.

I am selecting data ordered by post_id in descending order, but my PHP script starts with post_id 1 on the first page. I want it to start with the newest post, rather than the first. Heres my code so far:

<?php # view_posts2.php

require_once ('./includes/config.inc.php');

$page_title = 'I';

include ('./includes/header.html');

require_once ('./includes/mysql_connect.php'); // Connect to the database.

$display = 10;

if (isset($_GET['np'])) {
      $num_pages = $_GET['np'];
} else {

$query = "SELECT COUNT(*) FROM seenya ORDER BY post_id DESC";
$result = mysql_query ($query);
$column = mysql_fetch_array ($result, MYSQL_NUM);
$num_records = $column[0];

if ($num_records > $display) {
      $num_pages = ceil ($num_records/$display);
} else {
      $num_pages = 1;
      }
}

if (isset($_GET['s'])) {
      $start = $_GET['s'];
} else {
      $start = 0;
}

      $query = "SELECT content, post_date, DATE_FORMAT(post_date, '%M %d, %Y') AS pd, post_id FROM seenya ORDER BY post_id DESC LIMIT $start, $display";
      $result = mysql_query ($query);
      

while ($column = mysql_fetch_array ($result, MYSQL_ASSOC)) {
      print "{$column['content']}<br /><p>
      <a href=\"registerhybrid.php?id={$column['post_id']}\">Reply</a></p>\n";
      }

echo '</table>';

mysql_free_result ($result);

mysql_close();      
      
if ($num_pages > 1) {
      echo '<br /><p>';
      $current_page = ($start/$display) + 1;
      if ($current_page != 1) {
            echo '<a href="view_posts2.php?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';
}

for ($i = 1; $i <= $num_pages; $i++) {
      if ($i != $current_page) {
            echo '<a href="view_posts2.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';
      } else {
            echo $i . ' ';
      }
}

if ($current_page != $num_pages) {
      echo '<a href="view_posts2.php?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a>';
}

echo '</p>';
}

include ('./includes/footer.html');
?>

0
Comment
Question by:bwsycom
  • 7
  • 4
15 Comments
 
LVL 1

Author Comment

by:bwsycom
ID: 17768305
I know that $start = 0 is the determining point, but I don't know how to set that variable to the newest (highest) post_id
0
 
LVL 1

Author Comment

by:bwsycom
ID: 17768911
Tried to do

$start = 'post_id DESC';

Doesn't seem to works
0
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 17769554
use
ORDER BY post_date DESC

change
$query = "SELECT content, post_date, DATE_FORMAT(post_date, '%M %d, %Y') AS pd, post_id FROM seenya ORDER BY post_id DESC LIMIT $start, $display";

to

$query = "SELECT content, post_date, DATE_FORMAT(post_date, '%M %d, %Y') AS pd, post_id FROM seenya ORDER BY post_date DESC LIMIT $start, $display";


0
 
LVL 1

Author Comment

by:bwsycom
ID: 17770114
I was originally using post_date, but decided not to becuase the datestamp wasn't appearing. Thats when I decided that post_id should be the determining factor for what descends.

I tried the switch that you posted and it is not working, it is still listing content in ascending order.
0
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 17770214
I'm assuming the "newest post" as you mention is the last post in your database, so it sounds like you want it in Ascending order:

"SELECT content, post_date, DATE_FORMAT(post_date, '%M %d, %Y') AS pd, post_id FROM seenya ORDER BY post_id ASC LIMIT $start, $display";
0
 
LVL 1

Author Comment

by:bwsycom
ID: 17770317
CtrlAltDl: Yes, the newest post would be the last record in the database. The post_id is auto-incremented. I tried your solution but it didn't work.

This page is the display page. AFAIK, it is counting the post_id column, dividing by 10 (how many I want displayed per page), and then displaying 10 records at a time with a Next and Previous link. However, it is displaying by post_id in ascending order, whereas I want the newest "post" to be at the top of the first page, and then descending.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 17775061
echo your query and paste it here.  It's not making sense to me why it isn't working.
0
 
LVL 1

Author Comment

by:bwsycom
ID: 17776519
mysql> SELECT post_id, content FROM seenya ORDER BY post_id DESC;
+---------+---------------------------------------------------------------------                                             --------------------------------------------------------------------------------                                             --------------------------------------------------------------------------------                                             --------------------------------------------------------------------------+
| post_id | content                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+---------+---------------------------------------------------------------------                                             --------------------------------------------------------------------------------                                             --------------------------------------------------------------------------------                                             --------------------------------------------------------------------------+
|      12 | numba 12 1st page lets hope                                                                                                                                                                                                                                                                                                                                                                                                                          |
|      11 | lets see if pagination happen                                                                                                                                                                                                                                                                                                                                                                                                           |
|      10 | fggfgfgffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff    
|       9 | numba 9                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|       8 | hopefullt this is number 10 gee i hope so                                                                                                                                                                                                                                                                                                                                                                                                            |
|       7 | hfdjfhjklsfh df  yayaya huffffffffffffffffffffffffffffffffffffffffff                                             fffffffffffeerrrrr                                                                                                                                                                                                                                                                                                                  |
|       6 | fhdjfhdslkjfhdlksjh fljhfdsjlkfhdjklhfdjlkhf dl kfhsdlkftewyiurtewyi                                             utrqiuyt eqvbcnmxbv,nmxbnv.xz[;a;ldfkj  ;lfjdsl;dsfd                                                                                                                                                                                                                                                                                |
|       5 | yayayayayayayayayayayayayayyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy                                             yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyayayayayayayaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa                                             ayayayayayayyyyyyyyyyyyy fdhsfjklhfldsjkfhdslf dlskfh dlkfh llkdjfhdjklfs                                                                                                                              |
|       4 | moar moar moar moar moar                                                                                                                                                                                                                                                                                                                                                                                                                             |
|       3 | heres a really really really longggggggggggggggggg   weeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee |
|       2 |  testestestestesttetstetstets                                                                                                                                                                                                                                                                                                                         |
|       1 | hfghgfhgfh                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+---------+---------------------------------------------------------------------                                             --------------------------------------------------------------------------------                                             --------------------------------------------------------------------------------                                             --------------------------------------------------------------------------+
12 rows in set (0.00 sec)
0
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 17776565
Is that the correct order?

Actually from your script above could you run it like this:

    $query = "SELECT content, post_date, DATE_FORMAT(post_date, '%M %d, %Y') AS pd, post_id FROM seenya ORDER BY post_id DESC LIMIT $start, $display";

echo $query; exit();
0
 
LVL 1

Author Comment

by:bwsycom
ID: 17776631
Yes, it is the correct order.

But when plugged into the script the script is still returning the lowest post_id first.

Do you think that it could have anything to do with the pagination? Or is there something I should be doing after I change the query?

0
 
LVL 14

Accepted Solution

by:
CtrlAltDl earned 500 total points
ID: 17776864
I think it is in the pagination.  I think the $start and $display are not what we'd expect it to be.  That is why I'd like to see the query that is using.

Try the code in my last post.
0
 
LVL 1

Author Comment

by:bwsycom
ID: 17791072
Sorry it took so long to reply.

Upon replacing the query per your above post:

SELECT content, post_date, DATE_FORMAT(post_date, '%M %d, %Y') AS pd, post_id FROM seenya ORDER BY post_id DESC LIMIT 0, 10
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now