AgerNic.com
WEB DEVELOPER SITE, HTML, CSS, PHP, SQL

PHP MySQLi Limit Data


<< Previous Page
PHP tutorial
Next Page >>
PHP tutorial

PHP Tutorial » PHP MySQLi Limit Data

MySQL database provides a LIMIT clause which is used to specify the number of records displayed (returned).

This feature that PHP has is very useful for optimizing the page load time when we have a very large number of records in DataBase, as well as to improve the readability of a website.

For example, when we have a very large number of entries in the DataBase, it can be divided into several pages using pagination, where a limited number of entries will be uploaded on each page when a user requests that page by clicking on the pagination link.

Syntax:

SELECT * FROM table_name LIMIT value;

 

PHP MySQL Limit Data - MySQLi (object oriented)

Suppose we want to select all records from 1 to 25 (inclusive) from table "Users". The SQL query will look like this:

Example (this is HTML - PHP editor, change text on this window)

<?php

$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";

/* Attempt MySQL server connection. */
$link = new mysqli($server_name, $user_name,
$password, $db_name);

// Check connection
if($mlink === false){
die("ERROR: Could not connect. " . $link->connect_error);
}
// Attempt select query execution
$sql = "SELECT * FROM Users LIMIT 25";
if($result = $link->query($sql)){
if($result->num_rows > 0){
echo "<table>";
echo "<tr>";
echo "<th>ID</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>UserName</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = $result->fetch_array()){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['user_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
$result->free();
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . $link->error;
}


// Close connection
$link->close();
?>

 

Related subjects:
How to create database phpmyadmin? Insert multiple rows PHP select / view data Conect to MySQLi

Tags: PHP MySQL Limit: Data Selections, results per page, offset, next page, loggin attemps, order by, variable, update, limit mysql connection, limit mysql results, connections
mysql limit range
limiting number of records per page using php
mysql limit offset pagination
mysql limit order by
mysql limit number of rows in table

 

PHP MySQL Limit Data - MySQLi (Procedural)

The SQL query below says "return only 14 records per page, start on record 21 (OFFSET 20)": Example using MySQLi (Procedural)

Example (this is HTML - PHP editor, change text on this window)

<?php

$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";

/* Attempt MySQL server connection. */
$link = mysqli_connect($server_name, $user_name,
$password, $db_name);

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Attempt select query execution
$sql = "SELECT * FROM Users LIMIT 20, 14";
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) > 0){

echo "<table>";
echo "<tr>";
echo "<th>ID</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>UserName</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = $result->fetch_array()){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['user_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Close result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// Close connection
mysqli_close($link);
?>

The example above "return only 14 records, start on record 21 (OFFSET 20)":
$sql = "SELECT * FROM Users LIMIT 20, 14";
We used Shorter syntax to achieve the the result:

Using OFFSET
$sql = "SELECT * FROM Orders LIMIT 14 OFFSET 20";

 

PHP Get Last ID - MySQLi (PDO) extension

The following example insert another row and get the last ID from table named "Users" in database my_DB using PDO

Example (this is HTML - PHP editor, change text on this window)

<?php

$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";

try {
$link = new PDO("mysql:host=$server_name;dbname=$db_name",
$user_name, $password);
// set the PDO error mode to exception
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "INSERT INTO Members (first_name, last_name, user_name, email)
VALUES ('Stwart', 'Martin', 'StwartM', '[email protected]'";
// use exec() because no results are returned
$link->exec($sql);
$last_id = $conn->lastInsertId();
echo "New record created successfully. Last inserted ID is: " . $last_id;
} catch(PDOException $e) {
echo $sql . "<br />" . $e->getMessage();
}

$link = null;

?>

Output
last id : 4

 



PHP MySQL Limit: Data Selections, results per page, offset, next page, loggin attemps, order by, variable, update, limit mysql connection, limit mysql results, connections
mysql limit range
limiting number of records per page using php
mysql limit offset pagination
mysql limit order by
mysql limit number of rows in table

PHP MySQLi Limit Data - php tutorial

Online Editor
ONLINE EDITOR

news templates


COLOR PICKER

news templates
This tool makes it easy to create, adjust, and experiment with custom colors for the web.


HTML Templates
news templates
Magnews2 is a modern and creative free magazine and news website template that will help you kick off your online project in style.


CSS HTML Layout
news templates
Find here examples of creative and unique website layouts.


Free CSS HTML Menu
news templates
Find here examples of creative and unique website CSS HTML menu.


Home
PHP Introduction
PHP Install
PHP Syntax
PHP Variables
PHP Echo and Print
PHP Data Types
PHP String Function
PHP Constants
PHP Operator Types
PHP If Else and Elseif
PHP Switch
PHP While Loops
PHP Loop For Do Foreach
PHP Array
Convert Array to String
PHP Function
PHP GET and POST
PHP Date/Time Functions
PHP Login
PHP Delete Element
PHP eregi_replace()
PHP mysql_query()
PHP Errors to Display
PHP Loop For Do Foreach

PHP forms
PHP Form Example
Online Editor
ONLINE EDITOR

news templates


COLOR PICKER

news templates
This tool makes it easy to create, adjust, and experiment with custom colors for the web.


HTML Templates
news templates
Magnews2 is a modern and creative free magazine and news website template that will help you kick off your online project in style.


CSS HTML Layout
news templates
Find here examples of creative and unique website layouts.


Free CSS HTML Menu
news templates
Find here examples of creative and unique website CSS HTML menu.


jQuery replace...
jQuery replaceClass...
jQuery click ()...
jQuery switchClass()...
jQuery draggable()...
jQuery HTML...
jQuery scroll smooth...
jQuery scroll()...
jQuery stop()...
jQuery animate()...
jQuery SlideToggle()...
jQuery slideDown()...
jQuery slideUp()...
jQuery fadeToggle...
jQuery fadeOut()...
jQuery fadeIn()...
jQuery Effects - hide () and show ()...
jQuery Events...
jQuery Attributes...
jQuery Selectors...