In PHP and MySQL, you can limit the number of rows returned by a SELECT
statement using the LIMIT
clause. Here’s how you can use LIMIT
in a MySQL query in PHP.
Assuming we have a table called “users” with the following fields:
- id (integer)
- name (string)
- email (string)
- age (integer)
To select a limited number of rows from the table, we use the LIMIT
clause in the SQL SELECT
statement.
Here’s an example of selecting the first 5 users from the table:
<?php
// Connect to the database
$conn = mysqli_connect('localhost', 'username', 'password', 'mydb');
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL query to select the first 5 users
$sql = "SELECT * FROM users LIMIT 5";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. " - Age: " . $row["age"]. "<br>";
}
} else {
echo "No users found";
}
// Close the connection
mysqli_close($conn);
?>
In the example above, we first connect to the database using mysqli_connect()
. We then define the SQL statement to select the first 5 users, using the SELECT
statement and the LIMIT
clause. We execute the SQL statement using mysqli_query()
, and check if there are any rows returned using mysqli_num_rows()
. If there are rows, we output the data of each row using mysqli_fetch_assoc()
. If there are no rows, we print a message saying “No users found”. Finally, we close the database connection using mysqli_close()
.
You can modify the SQL query to select a different number of rows or to select a specific range of rows. For example, to select the next 5 users after the first 5, you can use the following SQL query:
SELECT * FROM users LIMIT 5, 5
And the PHP code to execute this query would look like this:
<?php
// Connect to the database
$conn = mysqli_connect('localhost', 'username', 'password', 'mydb');
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// SQL query to select the next 5 users after the first 5
$sql = "SELECT * FROM users LIMIT 5, 5";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. " - Age: " . $row["age"]. "<br>";
}
} else {
echo "No users found";
}
// Close the connection
mysqli_close($conn);
?>