PHP MySQL Limit Data Selections

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);
?>