PHP MySQLi order by
PHP Tutorial » PHP MySQLi order by
ORDER BY sort the result-set in ascending or descending order.
ORDER BY is used to sorts the records in ascending order by default. Sorting records in descending order, you have to use the DESC keyword.
Syntax:
In this chapter as well as in the following chapters we will show you three methods of working with PHP MySQLi ORDSER BY:
- MySQLi (object oriented)
- MySQLi (procedural)
- PDO
PHP mysql order by alphabetical Last Name
| ID | First Name | Last Name | UserName | |
|---|---|---|---|---|
| 1 | Clark | Kent | clarkkent | clarkkent@mail.com |
| 2 | John | Carter | johncarter | johncarter@mail.com |
| 3 | Peter | Parker | peterparker | peterparker@mail.com |
PHP MySQLi ORDER BY MySQLi (object oriented)
The following examples rows will be ordered by the Last Name (last_name) column::
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";
// Create connection
$link = new mysqli($server_name, $user_name,
$password, $db_name);
if ( $link->connect_error) {
die("Connection failed: " . $link->connect_error);
}
$sql = "SELECT id, first_name, last_name FROM Users ORDER BY last_name";
$result = $link->query($sql);if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["first_name"]. " " . $row["last_name"]. "<br />";
}
} else {
echo "0 results";
}
$link->close();
?>
num_rows() checks if there are more than zero rows returned.
fetch_assoc() puts all the results into an associative array that we can loop through.
while() loops through the result set and outputs the data from table "Users" the id, first-name and last-name columns.
Related subjects:
PHP myadmin where
PHP myadmin update date
Insert multiple rows
PHP select / view data
Conect to MySQLi
Tags: php mysql order by multiple columns,
order by ascending mysql,
sorting data from database in php,
mysql order by specific sequence,
php order by desc,
mysql order by date,
mysql descending order,
mysql order by desc limit,
PHP MySQL order by, asc desc, multiple columns, alphabetical, date descending, desc limit 1
PHP MySQLi ORDER BY MySQLi (Procedural)
Attempt MySQL server connection. Assuming you are running MySQL server with default setting ("localhost", "root", "your_password")
The following examples rows will be ordered by the Last Name (last_name) column - MySQLi (Procedural)
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";
// Create connection
$link = mysqli_connect($server_name, $user-name, $password, $db-name);
// Check connection
if (!$link) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firs_tname, last_name FROM Users ORDER BY last_name";
$result = mysqli_query($link, $sql);if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["first_name"]. " " . $row["last_name"]. "<br />";
}
} else {
echo "0 results";
}
mysqli_close($link);
?>
PHP MySQLi ORDER BY using (PDO) extension.
PDO extension. Attempt MySQL server connection. Assuming you are running MySQL server with default setting ("localhost", "root", "your_password")
The next PDO example ORDER BY last_name alphabetical and it will be displayed in an HTML table.
<?php
$server_name = "localhost";
$user_name = "my_username";
$password = "my_password";
$db_name = "my_DB";
try {
$link = new PDO("mysql:host=$server_name;dbname=$db_name", $user_name, $password);// Set the PDO error mode to exception
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt select query execution
try{
$sql = "SELECT * FROM Users ORDER BY last_name";
$result = $link->query($sql);
if($result->rowCount() > 0){
echo "<table align='center' style='width:99%'>";
echo "<tr style='background:#0C6; padding:8px'>";
echo "<th style='padding:8px'>id</th>";
echo "<th style='padding:8px'>first_name</th>";
echo "<th style='padding:8px'>last_name</th>";
echo "<th style='padding:8px'>email</th>";
echo "</tr>";
while($row = $result->fetch()){
echo "<tr style='border:thin #FFF; background: #CCC'>";
echo "<td style='padding:8px'>" . $row['id'] . "</td>";
echo "<td style='padding:8px'>" . $row['first_name'] . "</td>";
echo "<td style='padding:8px'>" . $row['last_name'] . "</td>";
echo "<td style='padding:8px'>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
unset($result);
} else{
echo "No records matching your query were found.";
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close connection
unset($link);
?>
PHP mysql order by alphabetical Last Name - the outcome
| ID | First Name | Last Name | UserName | |
|---|---|---|---|---|
| 1 | John | Carter | johncarter | johncarter@mail.com |
| 2 | Clark | Kent | clarkkent | clarkkent@mail.com |
| 3 | Peter | Parker | peterparker | peterparker@mail.com |
php mysql order by multiple columns, order by ascending mysql, sorting data from database in php, mysql order by specific sequence, php order by desc, mysql order by date, mysql descending order, mysql order by desc limit,
PHP MySQL order by, asc desc, multiple columns, alphabetical, date descending, desc limit 1
PHP MySQLi order by - php tutorial
This tool makes it easy to create, adjust, and experiment with custom colors for the web.
Magnews2 is a modern and creative free magazine and news website template that will help you kick off your online project in style.
Find here examples of creative and unique website layouts.
Find here examples of creative and unique website CSS HTML menu.