Selecting Limited records via LIMIT in MySQL

Your table can contains thousands and thousands of records. However, seldom do we need to retrieve all the records. What if you want to display only top 100 records? MySQL allows you to do so via LIMIT query. The syntax of LIMIT query is as follows:

SELECT * FROM table
LIMIT no_of_records

Selecting Limited records via Object Oriented MySQLi

To select limited records via MySQLi, pass the query for selecting limited records to the query function of the mysqli object. The following example demonstrates this concept.




Using MySQLi for Selecting Limited Records



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Selecting limited data

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "LIMIT 5";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
";
    }
} else {
    echo "0 results";
}

$connection->close();
?>

Download the code

The above code will retrieve values for patient_fname and patient_age columns of the top 5 records from Patient table of the hospital database.

Selecting Limited records via Procedural MySQLi

Selecting limited table data via procedural MySQLi is very easy. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using MySQLi for Selecting Limited Records



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Selecting limited data

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "LIMIT 3";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
";
    }
} else {
    echo "0 results";
}

$connection->close();
?>

Download the code

The above code will retrieve values for patient_fname and patient_age columns of the top 3 records from Patient table.

<<< AND/OR Operators In MySQLMax and Min Functions in MySQL >>>