Home » Tutorials » PHP/MySQL » Creating JSON API with pagination

Creating JSON API with pagination

The goal of today’s tutorial is to create a REST API with pagination. The API will get several values from MySQL and then convert them to JSON format.

Demo

For the purpose of the tutorial we will get the post_title and the post_date from ftutorials’s MySQL. In your real API you can print whatever data you want.

Create a file api.php and paste the following code into it:

<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'your_username');
define('DB_PASSWORD', 'your_password');
define('DB_DATABASE', 'your_database');

// creating the database connection
$conn = mysql_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD) or die(mysql_error());
$db = mysql_select_db(DB_DATABASE) or die(mysql_error());

// lets find out how many rows are in the MySQL table
$sql = "SELECT COUNT(*) FROM wp_posts";
$result = mysql_query($sql, $conn) or die(mysql_error());
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;

// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
$currentpage = (int) $_GET['currentpage'];
} else {
$currentpage = 1;  // default page number
}

// if current page is greater than total pages
if ($currentpage > $totalpages) {
// set current page to last page
$currentpage = $totalpages;
}
// if current page is less than first page
if ($currentpage < 1) {
// set current page to first page
$currentpage = 1;
}

// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the MySQL database
$sql = "SELECT post_title, post_date FROM wp_posts ORDER BY ID DESC LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)){
$output[]=$row;
}
print(json_encode($output));


?>

Don’t forget to change your_username, your_password, your_database fields with your MySQL’s data.

Finally, you can test your API pagination by going to the following address:
http://yourapi.com/api.php?currentpage=4

The number at the end is the current page.

The goal of today's tutorial is to create a REST API with pagination. The API will get several values from MySQL and then convert them to JSON format. For the purpose of the tutorial we will get the post_title and the post_date from ftutorials's&hellip;

Review Overview

0%

User Rating: 4.31 ( 7 votes)
0

About Georgi Dyulgerov

Georgi Dyulgerov

Founder of fTutorials.com - the website aiming to keep you in touch with the latest technologies in the brand. Also a Freelance web developer.

3 comments

  1. I’m gone to convey my little brother, that he should also pay
    a visit this web site on regular basis to obtain updated from hottest gossip.

  2. I’m extremely prompted with your ability as a copywriter and as well using the layout in your blog. Is this a new paid for subject or perhaps does one change the idea on your own? Either way be within the awesome high quality crafting, it is exceptional to seem a great site just like it these days.

  3. I am extremely loved this site. Its an useful topic. It aid me quite much to remedy some troubles. Its opportunity are so fantastic and operating fashion so fast

Leave a Reply

Your email address will not be published. Required fields are marked *


Loading Facebook Comments ...