The `LIMIT` clause in MySQL is used to constrain the number of rows returned by a query. It's often used with PHP to paginate results. When you fetch data from a MySQL database in PHP, it's a common practice to use `LIMIT` to control the amount of data you retrieve, especially for large datasets.
If you simply want to retrieve a specific number of records, you can use `LIMIT` like so:
SELECT * FROM table_name LIMIT number;
Example:
SELECT * FROM users LIMIT 5;
This will fetch the first 5 rows from the `users` table.
If you want to specify an offset, meaning you skip a number of records before fetching, you can add a second number:
SELECT * FROM table_name LIMIT offset, number;
Example:
SELECT * FROM users LIMIT 10, 5;
This will skip the first 10 records and then fetch the next 5.
Here's a simple example using PHP to fetch data from a MySQL database and using `LIMIT`:
<?php
$host = 'localhost';
$db = 'my_database';
$user = 'my_user';
$pass = 'my_password';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
// Fetching the first 5 users
$stmt = $pdo->query('SELECT * FROM users LIMIT 5');
while ($row = $stmt->fetch()) {
echo $row['name'] . "<br>";
}
?>
Using `LIMIT` for pagination is common. Here's a basic example:
<?php
$records_per_page = 10;
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? intval($_GET['page']) : 1;
$offset = ($page - 1) * $records_per_page;
$sql = "SELECT * FROM users LIMIT $offset, $records_per_page";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch()) {
echo $row['name'] . "<br>";
}
// Now let's generate pagination links
$total_pages = ceil($pdo->query('SELECT COUNT(*) FROM users')->fetchColumn() / $records_per_page);
for ($p = 1; $p <= $total_pages; $p++) {
echo "$p ";
}
?>
In this example, `$_GET['page']` is used to determine the current page. We calculate the offset based on the current page and records per page. After fetching the data, pagination links are generated at the bottom. Remember to validate and sanitize all user input and avoid directly interpolating data into your queries to prevent SQL injection. Prepared statements (as shown in the PDO example) are recommended for security reasons.
UPCET Exam
Click Here
SAAT Exam
Click Here
MHT CET Exam
Click Here
IPU CET Exam
Click Here
KCET Exam
Click Here
COMEDK UG Exam
Click Here
VITEEE Exam
Click Here
BITSAT
Click Here
DSAT: Dayanand Sagar Admission Test
Click Here
Career In Animation in india
Click Here
Merchant Navy Courses in india
Click Here
Interior Design Career in india
Click Here
UGC NET Exam
Click Here
B. Ed Exam
Click Here
AFCAT - Air Force Common Admission Test
Click Here
GATE Exam
Click Here
Joint Entrance Examination (JEE)
Click Here
Common Admission Test (CAT)
Click Here
CDS - Combined Defence Services Exam
Click Here