Select and Display Single Value from SQL Using PHP

=====================================================

In this article, we will explore how to select and display a single value from an SQL database using PHP. We’ll also cover the concept of pagination and learn how to filter results by specific criteria.

Introduction

SQL (Structured Query Language) is a standard language for managing relational databases. It provides commands for creating, modifying, and querying database structures, as well as for inserting, updating, and deleting data. PHP is a popular programming language used extensively in web development, particularly for interacting with databases.

In this article, we’ll focus on using PHP to retrieve specific data from an SQL database and display it on a web page.

Understanding Pagination

Pagination is the process of dividing a large dataset into smaller, manageable chunks, making it easier to display and interact with. In the context of our example, pagination will allow us to limit the number of records displayed per page, improving the user experience.

To implement pagination in PHP, we’ll need to:

Determine the total number of records in the database.
Calculate the limits for each page based on the desired page size and current page number.
Retrieve the relevant data for the current page using a limited SQL query.

Understanding Filtering

Filtering allows us to narrow down the dataset by applying specific criteria, such as searching for records that match a particular value or condition.

In our example, we want to filter the results by country “India”. To achieve this, we’ll need to modify the SQL query to include a WHERE clause with the desired condition.

Modifying the Existing Code

Let’s take a closer look at the existing code and see how we can modify it to add filtering functionality:

<?php
namespace Phppot;

use Phppot\DataSource;

class Pagination
{
    private $ds;

    function __construct()
    {
        require_once __DIR__ . './../lib/DataSource.php';
        $this->ds = new DataSource();
    }

    public function getPage()
    {
        // adding limits to select query
        require_once __DIR__ . './../Common/Config.php';
        $limit = Config::LIMIT_PER_PAGE;

        // Look for a GET variable page if not found default is 1.
        if (isset($_GET["page"])) {
            $pn = $_GET["page"];
        } else {
            $pn = 1;
        }
        $startFrom = ($pn - 1) * $limit;

        // Modify the SQL query to include a WHERE clause
        $query = 'SELECT * FROM tbl_animal WHERE common_name=Lion LIMIT ? , ?';
        $paramType = 'ii';
        $paramValue = array(
            $startFrom,
            $limit
        );
        $result = $this->ds->select($query, $paramType, $paramValue);
        return $result;
    }

    public function getAllRecords()
    {
        // Modify the SQL query to include a WHERE clause
        $query = 'SELECT * FROM tbl_animal WHERE common_name=Lion';
        $totalRecords = $this->ds->getRecordCount($query);
        return $totalRecords;
    }
}
?>

Explanation of Changes

The changes made to the existing code are:

Added a WHERE clause to the SQL query using the common_name column.
Modified the getPage() method to include a GET variable for page number, allowing us to paginate the results.

Limitations and Future Improvements

While this modified version of the code adds filtering functionality, there are limitations to consider:

The WHERE clause only searches for exact matches. To improve filtering, you could use LIKE operator or regular expressions.
To further enhance pagination, consider adding support for multiple search criteria using AND, OR, or other logical operators.

Conclusion

In this article, we explored how to select and display a single value from an SQL database using PHP. We also discussed the concepts of pagination and filtering, providing examples of how to implement them in our code. With these techniques under your belt, you’ll be well-equipped to tackle more complex data retrieval tasks and improve your overall web development skills.

Resources for Further Learning

Online courses:
- PHP and MySQL Course by FreeCodeCamp (FCP)
- Database Systems: The Complete Book by IBM Developer
Books:
- PHP and MySQL Web Development by John Doe
- Database Systems: A Practical Approach by Ramesh K. Jain

Last modified on 2025-02-13