Forcing Pagination in Wordpress, when results are provided by an external resource in WordPress

I’m having a WordPress headache. I’m building a WordPress frontend that hooks up to a CRM I use to pull details on stock, which I then display in WordPress.

It’s all working ok except for 1 thing – pagination. I do WordPress pagination all the time passing the paged var and using various plugins, but my queries are hand coded PHP PDO requests that don’t utilise WordPress’ functions.

Does anyone know how to force pagination within WordPress, even if the results are provided by an external resource?

If it helps, my query is as follows (in functions.php):

$dbh = new PDO("mysql:host=$hostname;dbname=cl15-l3ase2015", $username, $password);
$stmt = $dbh->prepare("SELECT * FROM cars LIMIT 0,$limit");
$stmt->bindParam(1, $id, PDO::PARAM_INT);
$stmt->execute();
$total = $stmt->rowCount();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$data['total_results'] = $total;
return $data;

…and the template is controlled by the following:

            <div class="col-xs-12 col-sm-8 col-md-8 col-lg-8 mask-left mask-right">

                <div class="body-copy-alt">

                    <?php

                    $posts_per_page         = 2;
                    $post_count             = $total;

                    $vehicle_group          = get_latest_vehicles(10000);
                    $total                  = $vehicle_group['total_results'];

                    ?>

                    <div class="col-xs-12 col-sm-7 col-md-8 col-lg-8 no-padding-left">
                        <h4 class="top"><strong><span id="total-holder"><?php echo $total;?></span> vehicles found</strong>, drive away in five days</h4>
                    </div>
                    <div class="col-xs-12 col-sm-5 col-md-4 col-lg-4 no-padding-right mask-left">
                        <select name="refine" id="refine">
                            <option value="null" disabled>Select Lowest Deposit</option>
                            <option value="100">100</option>
                        </select>
                    </div>

                    <div class="clearfix"></div>


                    <?php

                    foreach($vehicle_group as $current_vehicle) {

                        $current_id                 = $current_vehicle['id'];

                        $vehicle_manufacturer       = $current_vehicle['carMake'];
                        $vehicle_model              = $current_vehicle['carModel'];
                        $vehicle_registration       = $current_vehicle['carReg'];
                        $vehicle_fuel               = get_specific('FUEL', $current_vehicle['fuel']);
                        $vehicle_transmission       = get_specific('TRANSMISSION', $current_vehicle['transmission']);
                        $vehicle_mileage            = $current_vehicle['mileage'];
                        $vehicle_mpg                = $current_vehicle['economy'];
                        $vehicle_year               = $current_vehicle['year'];

                        $finance_data               = get_finance_data($current_id);
                        $vehicle_deposit_min        = $finance_data['minDeposit'];
                        $vehicle_weekly_payments    = $finance_data['weeklyPayments'];

                        $gallery_data               = get_gallery_images($current_id);


                        if(isset($current_id)) { 

                    ?>

                    <!-- Vehicle result sample -->
                    <div class="search-result">

                        <img src="<?php bloginfo('template_directory');?>/assets/img/new-stock-ribbon.png" class="ribbon" />

                        <div class="col-xs-12 col-sm-4 col-md-4 col-lg-6 no-padding-left no-padding-right">
                            <form id="vehicle-<?php echo $current_id;?>" method="post" action="<?php bloginfo('url');?>/vehicle">
                                <input type="hidden" name="vehicle" value="<?php echo $current_id; ?>" />
                                <a href="#" onclick="$('#vehicle-<?php echo $current_id; ?>').submit();">
                                    <img src="http://79.170.40.161/lease2buy.co.uk/uploads/<?php echo $current_id;?>/<?php echo $gallery_data[0]['photo']; ?>" class="vehicle-thumb" />
                                </a>
                            </form>
                        </div>

                        <div class="col-xs-12 col-sm-8 col-md-8 col-lg-6">

                            <h4><?php echo $vehicle_manufacturer; ?></h4>
                            <i><?php echo $vehicle_model; ?></i>

                            <div class="clearfix"></div>

                            <table class="table-responsive" summary="Vehicle specifications">
                                <tbody>
                                    <tr>
                                        <td class="legend">Reg No</td>
                                        <td><?php echo strtoupper($vehicle_registration); ?></td>
                                        <td class="spacer"></td>
                                        <td class="legend">Year</td>
                                        <td><?php echo $vehicle_year; ?></td>
                                    </tr>
                                    <tr>
                                        <td class="legend">Mileage</td>
                                        <td><?php echo $vehicle_mileage; ?></td>
                                        <td class="spacer"></td>
                                        <td class="legend">Fuel Type</td>
                                        <td><?php echo $vehicle_fuel; ?></td>
                                    </tr>
                                    <tr>
                                        <td class="legend">Transmission</td>
                                        <td><?php echo $vehicle_transmission; ?></td>
                                        <td class="spacer"></td>
                                        <td class="legend">MPG</td>
                                        <td><?php echo $vehicle_mpg; ?></td>
                                    </tr>
                                </tbody>
                            </table>

                            <div class="col-xs-6 col-sm-6 col-md-5 col-lg-5 no-padding-left">
                                <form name="vehicle_interest" method="post" action="<?php bloginfo('url');?>/vehicle/">
                                    <input type="hidden" name="vehicle" value="<?php echo $current_id; ?>" />
                                    <input type="submit" class="button bg-lightblue" value="View Details" />
                                </form>
                            </div>
                            <div class="col-xs-6 col-sm-6 col-md-5 col-lg-5 no-padding-right">
                                <form name="vehicle-<?php echo $current_id; ?>" method="post" action="<?php bloginfo('url');?>/vehicle">
                                    <input type="hidden" name="vehicle" value="<?php echo $current_id; ?>" />
                                    <input type="submit" class="button bg-green" value="More Info" />
                                </form>
                            </div>
                            <div class="clearfix"></div>

                        </div>

                        <div class="clearfix"></div>
                    </div> <!-- /.search-result -->

                    <div class="clearfix"></div>

                    <div class="result-bottom">
                        <div class="col-xs-12 col-sm-4 col-md-4 col-lg-4 gallery-link">
                            <a href="#"><img src="<?php bloginfo('template_directory');?>/assets/img/icon-camera.png" /> <?php echo $gallery_data['total_results']; ?></a>
                        </div>

                        <div class="hidden-xs col-sm-8 col-md-8 col-lg-8">
                            <h5 class="text-white"><strong>Min Deposit</strong> &pound;<?php echo $vehicle_deposit_min; ?> | 
                            <strong>From</strong> &pound;<?php echo $vehicle_weekly_payments; ?> per week</h5>
                        </div>
                    </div>
                    <!-- END Search result sample -->

                    <?php } } ?>

                    <?php
                    global $wp_query;
                    $wp_query->max_num_pages = ceil( $post_count / $posts_per_page );

                    // pagination functions
                    next_posts_link( 'Older Entries' );
                    previous_posts_link( 'Newer Entries' );
                    ?>


                    ....

Post Views: 3

All that the WordPress next_posts_link and previous_posts_link do are create links in the correct format to get the next page and previous page. Since you are not using posts or pages, these links will not be the right format.

You can accomplish the same using your custom CRM if you introduce an additional parameter for DB starting position. The details will depend a bit on exactly how your application works, but the basics are:

You modify your code to introduce some way of knowing where to start in the database. For example:

$dbh = new PDO("mysql:host=$hostname;dbname=cl15-l3ase2015", $username, $password);

$start = isset($_GET['start']) ? $_GET['start'] : 0;
$stmt = $dbh->prepare("SELECT * FROM cars LIMIT $start,$limit");
$stmt->bindParam(1, $id, PDO::PARAM_INT);
$stmt->execute();
$total = $stmt->rowCount();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$data['total_results'] = $total;
return $data;

You can create your own functions that accomplish the same thing.

For example, if $start is the starting position of the current page:

function my_next_link($start, $limit) {
    $new_start = $start + $limit;
    if ($new_start <= number of records in the CRM) {
        echo "<a href="http://example.com/mypage?start=$start">Next</a>";
    }
}

function my_previous_link($start, $limit) {
    if ($start > 0) {
        $new_start = max(0, $start - $limit);
        echo "<a href="http://example.com/mypage?start=$start">Previous</a>";
}

3 comments

I don’t exactly understand what’s not working, but you are preparing the statement in the wrong way. instead of

$stmt = $dbh->prepare("SELECT * FROM cars LIMIT 0,$limit");

it should be

$stmt = $dbh->prepare("SELECT * FROM cars LIMIT 0, ?");

and then you can limit to 1

$stmt->bindParam(1, $id, PDO::PARAM_INT);

EDIT – if you want to do pagination the query must keep count of the page.

So let’s start with the query

$stmt = $dbh->prepare('SELECT * FROM cars LIMIT :lower, :upper ');

$per_page = 5;
$page = 1;
if ( isset( $_GET['page'] ) ) {
  $page = $_GET['page'];
}
$upper =  $per_page * $page;
$lower = $upper - $per_page;
$stmt->bindParam(':lower', $lower);
$stmt->bindParam(':upper ', $upper );
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $data;

And then you must create Next/Prev links using a parameter which is ‘page’

Anonymous says:

April 5, 2023 at 9:45 pm

At first, think, how pagination works whatever worpdress or any other cms. The page list belongs the number i.e. 2,3,4…7,8. When someone click on any page link it provides the $_GET value. Then we use this value in query limit. There is another way that is retrieve all the data from the database, split into page. However, you should try the first way. There are many pagination classes and tutorials, pick one of them and check how that works and implement as you require. I think this link will help you link.

Log in to Reply

Leave a Reply Cancel reply

3 comments

Social Network

Related posts

Leave a Reply Cancel reply

3 comments

Social Network