Using my own custom [detailed] query – how to have paging?

  • Hi,

    Using the information here:

    https://codex.www.ads-software.com/Displaying_Posts_Using_a_Custom_Select_Query

    I’ve successfully created a detailed query that searches through all posts and returns them. Thing is, I’m using WP for a car listings site and there’s hundreds of cars on there initially (growing quickly to thousands).

    My query works perfectly but shows all x hundred results on the one page. I’m hoping there’s a simple way to re-introduce WP paging?

    Just for brevity I’m using the get_results to run my query as follows:

    $pageposts = $wpdb->get_results($querystr, OBJECT);

    Many thanks in advance.

Viewing 5 replies - 1 through 5 (of 5 total)
  • Thread Starter Zaphod

    (@zaphod)

    Oh,

    Just in case it’s helpful – here’s a sample query:

    SELECT wposts.*FROM cc_posts wposts
LEFT JOIN cc_postmeta cc_min_year ON (wposts.ID = cc_min_year.post_id)
LEFT JOIN cc_postmeta cc_max_year ON (wposts.ID = cc_max_year.post_id)
LEFT JOIN cc_postmeta cc_min_price ON (wposts.ID = cc_min_price.post_id)
LEFT JOIN cc_postmeta cc_max_price ON (wposts.ID = cc_max_price.post_id)WHERE 1=1
AND (cc_min_year.meta_key = 'year'
AND cc_min_year.meta_value >= 2001)
AND (cc_max_year.meta_key = 'year'
AND cc_max_year.meta_value <= 2005)
AND (cc_min_price.meta_key = 'price'
AND cc_min_price.meta_value >= 2000)
AND (cc_max_price.meta_key = 'price'
AND cc_max_price.meta_value <= 4000)
AND wposts.post_status = 'publish'
AND wposts.post_type = 'post'
AND wposts.post_date < NOW()ORDER BY wposts.post_date DESC LIMIT 0,10
    Thread Starter Zaphod

    (@zaphod)

    Anyone?! ?? I guess I’ll have a stab at writing it myself – shouldn’t be too hard, was just hoping that I wouldn’t need to.

    germanny

    (@germanny)

    Did you find a solution for this? I’m also working on the exact same issue.

    Thread Starter Zaphod

    (@zaphod)

    Germanny,

    Yes I did get a solution – sorry about the delay in replying. Basically I just add (e.g.) a ?page=x at the end of each url. Then I just change the LIMT 0,10 (see the last bit of the sql query above) to whatever suits the page.

    I.E. for https://www.mysite.com/page/?page=4

    I’d just change the limit to LIMIT 30,10

    page=4 so, limit (x-1)*10 – in this case, 30, [no of results to show] in this case ten… so

    limit 30,10

    if page = 1 or page not specified then limit 0,10

    The only other thing you need to do is know how many results you’re going to have so that you know how many pages of results there will be.
    E.G. if you had 95 results you’d need 10 pages – 9 of which will have ten results and the last one which has the final five.

    If you need any further pointers let us know.

    Thread Starter Zaphod

    (@zaphod)

    Sorry – just to be clear

    LIMIT 30,10

    Means start at row 30, and show 10 results. Not that clear from my above posting.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Using my own custom [detailed] query – how to have paging?’ is closed to new replies.