How do I setup MySQL queries to receive Backbone pagination requests?

Question

I've setup my API using Slim PHP. Here's a basic GET request to get all events. I would like to paginate this using Backbone Paginator. My main question is...how do I modify my SELECT statement to accept the parameters from the paginated collection in Backbone? There seems to be nothing out there detailing how you setup your API to receive these requests from the paginator. This post is the closest I could come, but says nothing about what you actually request from the database. Is it just a basic "if this parameter exists, add it to the MySQL statement?" Seems there should be a better way.

$app->get('/events', 'getEvents');


function getEvents() {
    // what do I do with the $sort variable?
    $sort = $app->request()->params('sort');
    $sql = "SELECT * FROM events";

    try {
        $db = getConnection();
        $stmt = $db->query($sql);  
        $events = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($events);
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}'; 
    }
}

score 1 · Accepted Answer · edited May 23 '17 at 12:05

I'm no PHP developer, but you do need to implement the paging in your query.

See MySQL Data - Best way to implement paging? for information on this.

As for the post you linked, you can follow the submitter's method for defining your query, which includes the limiting:

// where $results is the number of results to return
$sql = "select * FROM events ORDER BY " . $sort . " LIMIT " . $page . " , $results";

As for parameters that exist/don't exist, you could always set defaults. So if no page was requested, serve the first, and if no sort was requested, order by whatever makes sense for your app. If extra parameters are present, you could discard them.

If you want a very strict API, you can always return an error if the request doesn't match the endpoint specification

Thanks, I guess I'll do something like: `if($page) {...} else {$page = 0}`. I guess I wasn't sure if there was some kind of shortcut or helper function to do all that, but it sounds like it's good 'ol SQL and PHP if statements. Cool. Thanks. — bentedder, Jun 28 '13 at 15:07

How do I setup MySQL queries to receive Backbone pagination requests?

1 Answers1