How to implement pagination into my api (function) - WordPress site

Question

Im calling some data from mysql and Im creating my own api to display information in an android app. I tried this video on Youtube but I realized that the person is assuming that we have "page" active in our link, like: google.com/page=1. How can I activate this so I can pass any page number and any amount of items using Android. Again, the problem is not on Android.

I found this on Youtube:

$postData = new WP_Query( array(

    //if the user have passed a page number, it will load this page number
    'posts_per_page' => isset($data['posts']) ? $data['posts'] : 15,
    'paged' => isset($data['page']) ? $data['page'] : 1

));

And this is my function:

function get_all_coin()
{
  

    $servername = "";
    $username = "";
    $password = "";
    $dbname = "";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $query = "SELECT * FROM coins LIMIT 100";
    $result = mysqli_query($conn, $query);

    $json = array();

    if (mysqli_num_rows($result) > 0) {
        // output data of each rsow
        while ($row = mysqli_fetch_assoc($result)) {
            $json['id'] = (int) $row['id'];
            $json['name'] = $row['name'];
            // ...(More rows)

            $json_arr[] = $json;
        }
    } else {
        echo "0 results";
    }

    $json_ar = json_encode(['data' => $json_arr]);

    header('Content-type:application/json;charset=utf-8');

    $json_arrd['data'] = $json_arr;

    return $json_arrd;
}

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 23 '21 at 20:38

score 0 · Answer 1 · answered Aug 23 '21 at 20:50

0

If you were using WordPress to query WordPress posts then what the tutorial said about the page query parameter would be true. But it looks like you're using a non-WordPress database table, so without WordPress what you need to do is add an offset to your query:

$query = "SELECT * FROM coins LIMIT $limit OFFSET $offset";

The $offset will need to be equal to the number of items per page multiplied by whatever page you are on.

That will, however, give you something that is super easy to hack into. To stop this you need to either make sure that $limit and $offset are both integers, or even better, use prepared statements as Dharman explained.

answered Aug 23 '21 at 20:50

hostingutilities.com

8,894
3
41
51

I have a wordpress site..and I create a new table in the database...so, i have some coin infromation there and just want to display that info inside my android app..but load the data little by little – Maduro Aug 23 '21 at 21:06
That query will load a subset of data from your coin table. Since you're not using WordPress' custom post types to store the coin information (which in some circumstances is a good thing) and are instead using your own table, you can simply use PHP's normal MySQL features as you are doing to query the coin table for data. – hostingutilities.com Aug 25 '21 at 07:53
Although, I'm not even sure what role WordPress is playing in your project. It almost sounds like you don't need WordPress. – hostingutilities.com Aug 25 '21 at 07:55
Yes you right...I don't need wordpress...I was hoping wordpress make it easy to do this like the WP_Query() – Maduro Aug 25 '21 at 12:19
The `WP_Query` is for querying WordPress posts. But actually, if you created a new type of post called `coin` you wouldn't even need to use `WP_Query` as WordPress will automatically create API endpoints that you can use, as long as you have [show_in_rest](https://developer.wordpress.org/reference/functions/register_post_type/#show_in_rest) set to true. – hostingutilities.com Aug 25 '21 at 22:43
It would be easier to continue doing things the way you are doing them, but if you wanted to try to learn how to use WordPress, you'll want to lookup how to use a custom post type. – hostingutilities.com Aug 25 '21 at 22:46

How to implement pagination into my api (function) - WordPress site

1 Answers1