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

Submitted by nurhodelta_17 on Tuesday, October 10, 2017 - 21:12.
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`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  1. INSERT INTO `tutorial` (`tutorialid`, `title`, `link`) VALUES
  2. (1, 'Submit Form using AJAX in PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11649/submit-form-using-ajax-phpmysqli.html'),
  3. (2, 'Image Upload using AJAX in PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11648/image-upload-using-ajax-phpmysqli.html'),
  4. (3, 'Creating an Iterator for While Loop Forms in PHP/MySQLi', 'https://www.sourcecodester.com/php/11643/creating-iterator-while-loop-forms-phpmysqli.html'),
  5. (4, 'Drag, Drop and Insert into Database using AJAX/jQuery in PHP', 'https://www.sourcecodester.com/tutorials/php/11641/drag-drop-and-insert-database-using-ajaxjquery-php.html'),
  6. (5, 'Simple POS and Inventory System', 'https://www.sourcecodester.com/php/11625/simple-pos-and-inventory-system.html'),
  7. (6, 'Performance Indicator System', 'https://www.sourcecodester.com/php/11638/performance-indicator-system.html'),
  8. (7, 'Simple Chat System', 'https://www.sourcecodester.com/php/11610/simple-chat-system.html'),
  9. (8, 'Uploading Multiple Files into MySQL Database using PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11634/uploading-multiple-files-mysql-database-using-phpmysqli.html'),
  10. (9, 'Deleting Multiple Rows using Checkbox in PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11631/deleting-multiple-rows-using-checkbox-phpmysqli.html'),
  11. (10, 'PHP/MySQLi CRUD Operation with Bootstrap/Modal', 'https://www.sourcecodester.com/php/11629/phpmysqli-crud-operation-bootstrapmodal.html'),
  12. (11, 'PHP Passing Value to Modal using jQuery', 'https://www.sourcecodester.com/tutorials/php/11627/php-passing-value-modal-using-jquery.html'),
  13. (12, 'How to Add Class to a Div using JQuery', 'https://www.sourcecodester.com/tutorials/javascript/11619/how-add-class-div-using-jquery.html'),
  14. (13, 'How to Limit Number of Items per Row in While Loop using PHP/MySQLi', 'https://www.sourcecodester.com/tutorials/php/11618/how-limit-number-items-row-while-loop-using-phpmysqli.html'),
  15. (14, 'PHP Prevent the Return to Login Page/Disable Back after Login', 'https://www.sourcecodester.com/tutorials/php/11614/php-prevent-return-login-pagedisable-back-after-login.html');
limitquery

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
  2.  
  3. $conn = mysqli_connect("localhost","root","","testing");
  4. if (!$conn) {
  5. 	die("Connection failed: " . mysqli_connect_error());
  6. }
  7.  
  8. ?>

index.php

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="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  6. 	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">  
  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="https://www.sourcecodester.com/user/224918/track" 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'];
  32.  
  33. 						$limit='limit '.$number;
  34. 					}
  35.  
  36. 					if(isset($_POST['all'])){
  37. 						$limit='';
  38. 					}
  39.  
  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>
  54.  
  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 :)

Tags

Add new comment

About text formats
  • 148 views