How to use SQL Limit to Limit Results in PHP/MySQLi

Published on: June 15, 2020

The use of limit limits the number of results of your MySQL Query and is usually at the last part of your SQL. This is most applicable if, for example, you want to get the top-selling product, you need to order the result then limit it by 1. This tutorial will teach you how to use this limit.

Note: Bootstrap CSS and script used in this tutorial are hosted; therefore, you need an internet connection for them to work.

Creating a Database

The first step is to create our database.

1. Open phpMyAdmin.

2. Click databases, create a database and name it as testing.

3. After creating a database, click the SQL and paste the below codes. See image below for detailed instruction.

CREATE TABLE `tutorial` (
  `tutorialid` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(150) NOT NULL,
  `link` VARCHAR(150) NOT NULL,
PRIMARY KEY(`tutorialid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tutorial` (`tutorialid`, `title`, `link`) VALUES

Creating a Connection

Next, we create our connection to our database. This will serve as the bridge between our forms and database. We name this as conn.php.

<?php
 
$conn = mysqli_connect("localhost","root","","testing");
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
 
?>

index.php

And this contains our sample table and a limit form so you can practice limiting our sample table.

<!DOCTYPE html>
<html>
<head>
    <title>How to use SQL Limit to Limit Results in PHP/MySQLi</title>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">  
</head>
<body>
<div class="container">
    <div class="row" style="margin-top:20px;">
        <center><span style="font-size:25px; color:blue"><strong>How to use SQL Limit to Limit Results in PHP/MySQLi</strong></span></center>
        <form method="POST" class="form-inline" style="margin-top:10px;">
            <input type="text" class="form-control" name="limit">
            <input type="submit" name="limitme" value="Limit Query" class="btn btn-primary">
            <input type="submit" name="all" value="Show All" class="btn btn-success">
        </form>
    </div>
    <div class="row"style="margin-top:20px;">
        <h4><a href="https://www.campcodes.com/user/224918/track" style="text-decoration:none;">My Tutorials</a></h4>
        <table class="table table-bordered table-striped">
            <thead>
                <th>ID</th>
                <th>Title</th>
                <th>Link</th>
            </thead>
            <tbody>
                <?php
                    include('conn.php');
                    $limit="";
                    if(isset($_POST['limitme'])){
                        $number=$_POST['limit'];
 
                        $limit='limit '.$number;
                    }
 
                    if(isset($_POST['all'])){
                        $limit='';
                    }
 
                    $query=mysqli_query($conn,"select * from tutorial order by tutorialid asc $limit");
                    while($row=mysqli_fetch_array($query)){
                        ?>
                        <tr>
                            <td><?php echo $row['tutorialid']; ?></td>
                            <td><?php echo $row['title']; ?></td>
                            <td><?php echo $row['link']; ?></td>
                        </tr>
                        <?php
                    }
                ?>
            </tbody>			
        </table>
    </div>
 
</div>
</body>
</html>

That ends this tutorial. If you have any questions or comments, feel free to write it below or message me. Happy Coding!

Download Code