Skip to main content

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

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 just 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 internet connection for them to work.

Creating our Database

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.

  1. CREATE TABLE `tutorial` (
  2. `tutorialid` INT(11) NOT NULL AUTO_INCREMENT,
  3. `title` VARCHAR(150) NOT NULL,
  4. `link` VARCHAR(150) NOT NULL,
  5. PRIMARY KEY(`tutorialid`)

  1. INSERT INTO `tutorial` (`tutorialid`, `title`, `link`) VALUES
  2. (1, 'Submit Form using AJAX in PHP/MySQLi', ''),
  3. (2, 'Image Upload using AJAX in PHP/MySQLi', ''),
  4. (3, 'Creating an Iterator for While Loop Forms in PHP/MySQLi', ''),
  5. (4, 'Drag, Drop and Insert into Database using AJAX/jQuery in PHP', ''),
  6. (5, 'Simple POS and Inventory System', ''),
  7. (6, 'Performance Indicator System', ''),
  8. (7, 'Simple Chat System', ''),
  9. (8, 'Uploading Multiple Files into MySQL Database using PHP/MySQLi', ''),
  10. (9, 'Deleting Multiple Rows using Checkbox in PHP/MySQLi', ''),
  11. (10, 'PHP/MySQLi CRUD Operation with Bootstrap/Modal', ''),
  12. (11, 'PHP Passing Value to Modal using jQuery', ''),
  13. (12, 'How to Add Class to a Div using JQuery', ''),
  14. (13, 'How to Limit Number of Items per Row in While Loop using PHP/MySQLi', ''),
  15. (14, 'PHP Prevent the Return to Login Page/Disable Back after Login', '');


Creating our 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.

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


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

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <title>How to use SQL Limit to Limit Results in PHP/MySQLi</title>
  5. <script src=""></script>
  6. <link rel="stylesheet" href="">
  7. </head>
  8. <body>
  9. <div class="container">
  10. <div class="row" style="margin-top:20px;">
  11. <center><span style="font-size:25px; color:blue"><strong>How to use SQL Limit to Limit Results in PHP/MySQLi</strong></span></center>
  12. <form method="POST" class="form-inline" style="margin-top:10px;">
  13. <input type="text" class="form-control" name="limit">
  14. <input type="submit" name="limitme" value="Limit Query" class="btn btn-primary">
  15. <input type="submit" name="all" value="Show All" class="btn btn-success">
  16. </form>
  17. </div>
  18. <div class="row"style="margin-top:20px;">
  19. <h4><a href="" style="text-decoration:none;">My Tutorials</a></h4>
  20. <table class="table table-bordered table-striped">
  21. <thead>
  22. <th>ID</th>
  23. <th>Title</th>
  24. <th>Link</th>
  25. </thead>
  26. <tbody>
  27. <?php
  28. include('conn.php');
  29. $limit="";
  30. if(isset($_POST['limitme'])){
  31. $number=$_POST['limit'];
  33. $limit='limit '.$number;
  34. }
  36. if(isset($_POST['all'])){
  37. $limit='';
  38. }
  40. $query=mysqli_query($conn,"select * from tutorial order by tutorialid asc $limit");
  41. while($row=mysqli_fetch_array($query)){
  42. ?>
  43. <tr>
  44. <td><?php echo $row['tutorialid']; ?></td>
  45. <td><?php echo $row['title']; ?></td>
  46. <td><?php echo $row['link']; ?></td>
  47. </tr>
  48. <?php
  49. }
  50. ?>
  51. </tbody>
  52. </table>
  53. </div>
  55. </div>
  56. </body>
  57. </html>

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

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.


1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.