PHP MySQL tutorial

Sorting MySQL table data via PHP Script

In previous articles, we used select statement to fetch records from a database table. The data returned by select statement is in the order in which data exists in the table. However, you can sort the order of the selected data in MySQL. For instance, you select records from Patient table in the sorted order of age. To sort data in MySQL, the ORDER BY statement is used. The syntax of Ordery by query is as follows:

SELECT colum1, column2,...columnN FROM table1, table2...
ORDER BY colum1, colum2 ... ASC/DESC

Sorting MySQL table data via Object Oriented MySQLi

To sort table data via MySQLi, pass the query for sorting data to the query function of the mysqli object. Have a look at the following example.




Using ORDER BY clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing ORDER BY clause

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "ORDER BY patient_age";
$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 ORDER BY query will select the values for patient_age and patient_fname columns of the Patient table sorted by ascending order of age i.e the data of the patient with the smallest age shall appear on top.

Sorting MySQLi Table data via Procedural MySQLi

Sorting table rows via procedural MySQLi is straight forward. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using ORDER BY clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing ORDER BY clause

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "ORDER BY patient_age DESC";
$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 ORDER BY query will select the values for patient_age and patient_fname columns of the Patient table sorted by descending order of age i.e the data of the patient with the largest value for age shall appear on top.

<<< Deleting Data From MySQL Table via PHP ScriptLIKE Query in MySQL >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .