Next and previous links in PHP, MySQL

If you see a WordPress site, you will notice that at the bottom of the blog listing page, there will be links to next and previous pages.

They are useful to move the user to next or previous page.

If user is at the last page, then the next button will not be displayed.

Similarly, if user is at the first page, then the previous button will be hidden.

We are achieve this in PHP and MySQL. Following is the code to do that:

<?php

// Create a new PDO connection to the MySQL database
$pdo = new PDO("mysql:host=localhost; dbname=test", "root", "root", [
    PDO::ATTR_PERSISTENT => true // Enables persistent connection for better performance
]);

// Define the number of records to show per page
$per_page = 2;

// Get the current page number from the query string, default to 1 if not set
$page = $_GET["page"] ?? 1;

// Calculate the offset for the SQL query
$skip = ($page - 1) * $per_page;

// Fetch the users for the current page, ordered by ID in descending order
$sql = "SELECT * FROM users ORDER BY id DESC LIMIT $skip, $per_page";
$stmt = $pdo->prepare($sql); // Prepare the SQL statement
$stmt->execute([]); // Execute the query
$users = $stmt->fetchAll(PDO::FETCH_OBJ); // Fetch all results as objects

// Query to get the total number of users in the database
$sql = "SELECT COUNT(*) AS total_count FROM users";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_OBJ);
$total_count = $row->total_count ?? 0; // Get the total count or default to 0

// Calculate the total number of pages required
$total_pages = ceil($total_count / $per_page);

// Determine if there are more pages
$has_more_pages = ($page * $per_page) < $total_count;

// Initialize previous and next page variables
$previous_page = $next_page = null;

// Determine the previous page number
if ($page > 1)
{
    $previous_page = $page - 1;
}

// Determine the next page number
if ($page < $total_pages)
{
    $next_page = $page + 1;
}

?>

<!-- Loop through and display each user -->
<?php foreach ($users as $user): ?>
    <p><?php echo $user->name; ?></p>
<?php endforeach; ?>

<!-- Pagination controls -->
<?php if ($next_page > 0): ?>
    <a href="?page=<?php echo $next_page; ?>">Previous</a>
<?php endif; ?>

<?php if ($previous_page > 0): ?>
    <a href="?page=<?php echo $previous_page; ?>">Next</a>
<?php endif; ?>

Comments has been added with each line for explanation.

Even if you are working in any other language like Node.js and MongoDB, you can still achieve this because now you have the algorithm to do that.

We implemented this technique in one of our project: Multi-purpose platform in Node.js and MongoDB

From there, you will get the idea how you can render next and previous links if you are working in Node.js and MongoDB as backend and React as frontend.

Case-sensitive search in MySQL

Learn how to do case-sensitive search in MySQL. By default, MySQL search text-based column values without case sensitivity. For example, if your database looks like this:

Table: users

idemail
1support@adnan-tech.com
2support@Adnan-tech.com

And if you run the following query:

SELECT * FROM users WHERE email = "support@Adnan-tech.com";

It will return both records. However, it should have return only the 2nd record. The reason why, is because, usually the collation of text-based columns in MySQL is utf8_general_ci which is case insensitive.

So you just need to change the collation of “email” column in “users” table by running the following command:

ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_bin;

If you run the query again, you will only see the 2nd row. Even though the 1st row value is also same, it is in lower-case and the query is in upper case “A”.

This is because utf8mb4_bin, it ensures that the search and sort on this column should be case sensitive.

That is how you can do case-sensitive search in MySQL using utf8mb4_bin collation. It is really helpful specially if you are searching on column that has UUID values.

Pagination in React, Node.js and MongoDB

In this tutorial, we will show you, how you can do pagination in your React app having Node.js and MongoDB as backend. Even if you have any other backend technology, you can still get the algorithm and apply it in your language.

React

Following is our React component that will call an AJAX request and fetch the data from API. After fetching the data from API, it will render the data along with the pagination links. Whenever any of the pagination link is clicked, it will refetch the data based on the clicked page number.

<script src="js/babel.min.js"></script>
<script src="js/react.development.js"></script>
<script src="js/react-dom.development.js"></script> 

<div id="app"></div>

<script type="text/babel">

    function App() {

        // Set current page
        const [page, setPage] = React.useState(1);

        // Number of pages on pagination
        const [pages, setPages] = React.useState(0);

        // Whenever it's value is updated, the data will be re-fetched
        const [refetch, setRefetch] = React.useState(0);

        // Data array
        const [users, setUsers] = React.useState([]);

        // Check if there are more pages
        const [hasMorePages, setHasMorePages] = React.useState(false);

        async function loadMore() {
            // Creating a built-in AJAX object
            var ajax = new XMLHttpRequest();

            // Tell the method, URL of request and make is asynchronous
            ajax.open("POST", "http://localhost:3000/fetch-users", true);

            // Detecting request state change
            ajax.onreadystatechange = function () {

                // Called when the response is successfully received
                if (this.readyState == 4) {

                    if (this.status == 200) {
                        // For debugging purpose only
                        // console.log(this.responseText);

                        // Converting JSON string to Javasript array
                        var response = JSON.parse(this.responseText);

                        // Update state variable
                        setUsers(response.users);

                        // Set if there are more pages
                        setHasMorePages(response.hasMorePages);

                        // Set pagination
                        setPages(response.pages);
                    }
                }
            };

            // Create form data object
            const formData = new FormData();

            // Attach current page number
            formData.append("page", page);

            // Send the request
            ajax.send(formData);
        }

        // Run function when page value updates
        React.useEffect(function () {
            loadMore();
        }, [refetch]);

        return (
            <>
                {/* Display data */}

                { users.map(function (user) {
                    return (
                        <p key={ `user-${ user._id }` }>{ user.name }</p>
                    );
                }) }

                {/* Show pagination if required */}
                
                { pages > 1 && (
                    <nav>
                        <ul>
                            { Array.from({ length: pages }, function (_, index) { return index + 1 } ).map(function (p) {
                                return (
                                    <li key={ `pagination-${ p }` } className={ `page-item ${ p == page ? "active" : "" }` }>
                                        <a href="#" onClick={ function (event) {
                                            event.preventDefault();
                                            setPage(p);
                                            setRefetch(refetch + 1);
                                        } }>{ p }</a>
                                    </li>
                                );
                            }) }
                        </ul>
                    </nav>
                ) }
            </>
        );

    }

    ReactDOM.createRoot(
        document.getElementById("app")
    ).render(<App />);

</script>

Node.js and MongoDB

Now in our Node.js server, we will create an API that will return all the records based on the current page from client side. It will also return the number of pagination links.

const express = require("express");
const app = express();
const http = require("http").createServer(app);

const mongodb = require("mongodb");
const ObjectId = mongodb.ObjectId;
const MongoClient = mongodb.MongoClient;

const cors = require("cors");
app.use(cors("http://localhost/pagination-in-react-node-js-and-mongodb"));

const expressFormidable = require("express-formidable");
app.use(expressFormidable({
    multiples: true
}));

const port = process.env.PORT || 3000;
const databaseName = "test"
const MONGO_URI = process.env.MONGO_URI || "mongodb://127.0.0.1:27017";

http.listen(port, async function () {
    console.log("Server started");

    const client = new MongoClient(MONGO_URI);
    try {
        await client.connect();
        const db = client.db(databaseName);
        console.log("Database connected.");

        app.post("/fetch-users", async function (request, result) {
            const limit = 2;
            const page = parseInt(request.fields.page || 1);
            const skip = (page - 1) * limit;

            const users = await db.collection("users")
                .find({})
                .sort({
                    _id: 1
                })
                .skip(skip)
                .limit(limit)
                .toArray();

            const usersArr = [];
            for (let a = 0; a < users.length; a++) {
                const obj = {
                    _id: users[a]._id || "",
                    name: users[a].name || ""
                };

                usersArr.push(obj);
            }

            const totalCount = await db.collection("users").countDocuments({});
            const hasMorePages = (page * limit) < totalCount;
			const pages = Math.ceil(totalCount / limit);

            result.json({
                status: "success",
                message: "Data has been fetched.",
                users: users,
                hasMorePages: hasMorePages,
                pages: pages
            });
        });

    } catch (exp) {
        console.log(exp.message);
    }
});

That’s how you can create pagination links in your React app with Node.js and MongoDB as backend. If you face any problem in following this, feel free to contact me.

Load more in React, Node.js & MongoDB

Previously, we wrote 2 articles on how to add a “load more” button. First article uses Node.js and MongoDB and EJS for frontend and second article uses PHP and MySQL. In this article, we will show you, how you can create a “load more” button in React with Node.js and MongoDB as your backend. This is the refined version of previous load more tutorial in Node.js and MongoDB.

First, let me show you all the code. Then I will explain everything.

React

function App() {

    // Set current page
    const [page, setPage] = React.useState(1);

    // Data array
    const [users, setUsers] = React.useState([]);

    // Check if there are more pages
    const [hasMorePages, setHasMorePages] = React.useState(false);

    async function loadMore() {
        // Creating a built-in AJAX object
        var ajax = new XMLHttpRequest();

        // Tell the method, URL of request and make is asynchronous
        ajax.open("POST", "http://localhost:3000/fetch-users", true);

        // Detecting request state change
        ajax.onreadystatechange = function () {

            // Called when the response is successfully received
            if (this.readyState == 4) {

                if (this.status == 200) {
                    // For debugging purpose only
                    // console.log(this.responseText);

                    // Converting JSON string to Javasript array
                    var response = JSON.parse(this.responseText);
            
                    // Get data from API
                    const newArr = response.users;

                    // ❌ Less efficient, more readable
                    // Get current data
                    /*const tempArr = [ ...users ];

                    // Loop through all new records
                    for (let a = 0; a < newArr.length; a++) {
                        // And add them in existing array
                        tempArr.push(newArr[a]);
                    }

                    // Update state variable
                    setUsers(tempArr);*/

                    // ✅ Efficient way
                    setUsers([...users, ...newArr]); 

                    // Set if there are more pages
                    setHasMorePages(response.hasMorePages);
                }
            }
        };

        // Create form data object
        const formData = new FormData();

        // Attach current page number
        formData.append("page", page);

        // Send the request
        ajax.send(formData);
    }

    // Run function when page value updates
    React.useEffect(function () {
        loadMore();
    }, [page]);

    return (
        <>
            {/* Display data */}

            { users.map(function (user) {
                return (
                    <p key={ `user-${ user._id }` }>{ user.name }</p>  
                );
            }) }

            {/* Show load more button if there are more pages */}

            { hasMorePages && (
                <button type="button"
                    onClick={ function () {
                        // Increment page value
                        // It will automatically call the loadMore function from useEffect hook
                        setPage(page + 1);
                    } }>Load more</button>
            ) }
        </>
    );
}

Here, we are displaying all the data we are getting from API. We are displaying the “load more” button only if there are more pages required. We created a React.useEffect hook and calling the loadMore function everytime the page value gets updated. It will automatically gets called first time when the page loads. After that, every time you click the “Load more” button, we are simply incrementing the page value by 1. It will trigger the React.useEffect hook, thus calling the loadMore function again.

From API, we will receive the data that will be an array, and a boolean variable that tells if there are more pages required. Based on the latter, we will show or hide the “Load more” button. If there are more pages required, you can keep pressing load more button. When there are no more records in the database, then the load more button will be removed.

When the response from API is received, we are appending the new data in our existing data array. The way to do that in React is different. First, we need to create a shallow copy of our existing array using spread operator: const tempArr = [ …users ]; Then we need to loop through all new data received from API, and push it in your shallow copy of array. Finally, we will update our state variable and will render the new data.

For rendering, we are using Javascript map function to iterate over an array. In each paragraph, we should have a unique key value for React to identify each element uniquely. In order to create a unique key, we are using user ID, and we are displaying user name in each paragraph. This is create a “load more” button in our React component. In next step, we will create it’s API.

Node.js & MongoDB

// Run command in your terminal:
// npm install express http mongodb cors express-formidable
const express = require("express");
const app = express();
const http = require("http").createServer(app);

const mongodb = require("mongodb");
const ObjectId = mongodb.ObjectId;
const MongoClient = mongodb.MongoClient;

const cors = require("cors");
app.use(cors("http://localhost/load-more.html"));

// Native way to allow CORS
/*// Add headers before the routes are defined
app.use(function (req, res, next) {
    // Website you wish to allow to connect
    res.setHeader("Access-Control-Allow-Origin", "*");
    // Request methods you wish to allow
    res.setHeader("Access-Control-Allow-Methods", "GET, POST, OPTIONS, PUT, PATCH, DELETE");
    // Request headers you wish to allow
    res.setHeader("Access-Control-Allow-Headers", "X-Requested-With,Content-Type,Authorization");
    // Set to true if you need the website to include cookies in the requests sent
    // to the API (e.g. in case you use sessions)
    res.setHeader("Access-Control-Allow-Credentials", true);
    // Pass to next layer of middleware
    next();
});*/

const expressFormidable = require("express-formidable");
app.use(expressFormidable({
    multiples: true
}));

const port = process.env.PORT || 3000;
const databaseName = "test"
const MONGO_URI = process.env.MONGO_URI || "mongodb://127.0.0.1:27017";

http.listen(port, async function () {
    console.log("Server started");
    
    const client = new MongoClient(MONGO_URI);
    try {
        await client.connect();
        const db = client.db(databaseName);
        console.log("Database connected.");

        // Seeding the data in database
        /*const usersCount = await db.collection("users").countDocuments({});
        if (usersCount == 0) {
            await db.collection("users")
                .insertMany([
                    { name: "Adnan" },
                    { name: "Afzal" },
                    { name: "Ahmad" },
                    { name: "Khalid" },
                    { name: "Tariq" },
                ]);
        }*/

        app.post("/fetch-users", async function (request, result) {
            const limit = 1;
			const page = parseInt(request.fields.page || 1);
			const skip = (page - 1) * limit;
			
			const users = await db.collection("users")
                .find({})
                .sort({
                    _id: 1
                })
                .skip(skip)
                .limit(limit)
                .toArray();

            const usersArr = [];
            for (let a = 0; a < users.length; a++) {
                const obj = {
                    _id: users[a]._id || "",
                    name: users[a].name || ""
                };

                usersArr.push(obj);
            }

			const totalCount = await db.collection("users").countDocuments({});
    		const hasMorePages = (page * limit) < totalCount;

			result.json({
				status: "success",
				message: "Data has been fetched.",
				users: users,
				hasMorePages: hasMorePages
			});
		});
    } catch (exp) {
        console.log(exp.message);
    }
});

Here, we are first starting our server and connecting with MongoDB database. We created an API that accepts the page number as parameter. If the page number is not provided, then the default value of it will be 1. We are setting the limit to 1 for testing. We subtract 1 from current page value and multiply it by the limit, it will give use the number of records we need to skip.

Then we are fetching the data from database using skip and limit. Your document might have a lot of fields, so we created an obj to only return those fields that are need for this API. Finally, in order to check if there is a need for more pages, we first need to find the total number of records in our collection. We can then check if it is greater than the product of limit and page variable. If it is greater, it means there are still more records in the database.

That’s how you can create a “load more” button in React as your frontend and Node.js and MongoDB as your backend. If you face any problem in following this, kindly do let me know.

Parse HTML entities in React

If you are working in React and are fetching data from an API that is returning some content with mixed HTML entities. You will notice that when you are rendering the HTML data in React, the HTML entities does not gets rendered properly. It is because React does not parse HTML entities by default.

Today, we will show you a way that let’s you parse HTML entities in React. First, let’s say we have a following React component that displays a text having an HTML entity.

function MyApp() {
	const [myVar, setMyVar] = React.useState("&copy;");

	return (
		<h1>Parse HTML entities in React: { myVar }</h1>
	)
}

Right now, it will output:

Parse HTML entities in React: &copy;

You can see that the &copy; HTML entity is not parsed properly. In order to parse it, we will use a library called html-react-parser. This library allows you to parse HTML to React. If you are developing your backend in Node.js, then this library can be used on both sides, frontend and backend.

You need to include the library in your project using it’s production link:

<script src="https://unpkg.com/html-react-parser@latest/dist/html-react-parser.min.js"></script>

You can also download this JS file and paste in your project and use relative path. Finally, you need to call the “HTMLReactParser(var string)” function and send your variable as an argument.

{ HTMLReactParser(myVar) }

If you run the page now, you will see that your HTML entity has been parsed properly.