mySQL previous/next query

Question

I am trying to figure out a way to have a previous and next button to display more data for a given query-sorta like a pagination would do.

The query is this:

$query = "SELECT * 
        FROM wp_posts p
            LEFT OUTER JOIN wp_term_relationships r ON r.object_id = p.ID
            LEFT OUTER JOIN wp_term_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
            LEFT OUTER JOIN wp_terms t ON t.term_id = x.term_id
            LEFT JOIN wp_postmeta pm ON p.id = pm.post_id
        WHERE p.post_status = 'publish'
        AND pm.meta_key = 'xTraData'
        AND p.post_type = 'post'
        AND t.slug = 'press-coverage'
        ORDER BY p.post_date DESC LIMIT 0,6;";

How can i get the previous id and next id to use for the next/previous buttons for refreshing the query string?

Starx · Accepted Answer · 2013-03-03 22:38:34Z

1

You could get them using $_GET variables, which can be sent from URL. Process them to raise you limits.

A mysql limit defines how much rows to fetch and from where to fetch.

So, 0, 6 in your query say start from 0 (first) and select 6 rows.

$p_num = isset($_GET['pagenumber']) && (int)$_GET['pagenumber'] > 0 ? $_GET['pagenumber'] : 0;
$p_size = 6;

$query = "SELECT * 
        FROM wp_posts p
            LEFT OUTER JOIN wp_term_relationships r ON r.object_id = p.ID
            LEFT OUTER JOIN wp_term_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
            LEFT OUTER JOIN wp_terms t ON t.term_id = x.term_id
            LEFT JOIN wp_postmeta pm ON p.id = pm.post_id
        WHERE p.post_status = 'publish'
        AND pm.meta_key = 'xTraData'
        AND p.post_type = 'post'
        AND t.slug = 'press-coverage'
        ORDER BY p.post_date DESC LIMIT ".(($p_num-1) * $p_size).",".$p_size.";";

Now, send a request like: yourpage.php?pagenumber=2 and it will query the second page where a page will hold about 6 items.

edited Mar 3, 2013 at 22:38

answered Mar 3, 2013 at 22:18

Starx

79.3k50 gold badges187 silver badges266 bronze badges

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

5 Comments

StealthRT Over a year ago

so pagenumber=0 would product 0,6 but pagenumber=1 would produce 10,6.. shouldnt that be 5,6?

StealthRT Over a year ago

so if p_num is 0 then its -6 (0-1 * 6)?

Starx Over a year ago

@StealthRT, That is basic airthmetic. This is not a write a code for me community. Anyways check the update.

StealthRT Over a year ago

i was saying that $p_num-1 needs to be $p_num without the -1.

Starx Over a year ago

@StealthRT, Actually, when you query for second page (2), the limit should start from 5, not 11, so -1 is necessary. Try out my code.

aqua · Accepted Answer · 2013-03-03 22:17:42Z

0

You could pass in variables for the LIMIT bounds. In this instance previous id would be null and next id could be 7. If you always want X records at a time you could do LIMIT $next_ID, ($next_ID + $X) and modify your previous and next variables accordingly.

answered Mar 3, 2013 at 22:17

aqua

3,41532 silver badges42 bronze badges

Collectives™ on Stack Overflow

mySQL previous/next query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related