PHP MySQL Order By

PHP MySQL Order By keyword

ORDER BY keyword is used to sort the data in the record set.

ORDER BY keyword

The ORDER BY keyword is used to sort the data in the record set.

The ORDER BY keyword sorts records in ascending order by default.

If you want to sort in descending order, use the DESC keyword.

Syntax

SELECT column_name(s)

FROM table_name

ORDER BY column_name(s) ASC|DESC

Here is the usage of GROUP BY:

The biggest role of GROUP BY is to be used in conjunction with COUNT

SELECT city, COUNT(city) FROM cv GROUP BY city;

The above statement can count the number of resumes in each city. Be sure to pay attention to which field you GROUP BY before you can COUNT which field

A brief introduction to the usage of LIMIT:

SELECT col_name FROM tbl_name LIMIT offset, rows;

•offset: start fetching data from the number of all records that meet the conditions •rows: How many pieces of data to get

Get 5 records starting from the 1st record (0)

SELECT * FROM cv LIMIT 5;

SELECT * FROM cv LIMIT 0, 5;

Get 5 records starting from the 6th record (5)

SELECT * FROM cv LIMIT 5, 5;

#By the way, the usage of having:

Having can only be used after group by to re-filter the grouped results

SELECT city, COUNT(city) FROM cv GROUP BY city HAVING COUNT(city) > 2;

The above statement indicates that the grouping is performed by city first, and then the grouped results are grouped by quantity Filter conditions exceeding 2

Note that because the results are re-filtered, the fields used in HAVING must be the fields included in the query results in SELECT. That is to say, in the previous statement, only The city field can be used as a filter condition for having. If you use other fields, you will be prompted for unknown fields

The difference between having and where:

SELECT name FROM cv WHERE age > 20 GROUP BY city;

The above statement indicates that the query is first based on the condition of age>20, and then the query results are grouped

The difference is that the order is different. where is to filter the data first, and having to filter the grouped results.

Example

The following example selects all the data stored in the "Persons" table, and Sort the results based on the "Age" column:

<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
         echo "连接失败: " . mysqli_connect_error();
}
 
$result = mysqli_query($con,"SELECT * FROM Persons ORDER BY age");
 
while($row = mysqli_fetch_array($result))
{
         echo $row['FirstName'];
         echo " " . $row['LastName'];
         echo " " . $row['Age'];
         echo "<br>";
}
 
mysqli_close($con);
?>

The above results will output:

Glenn Quagmire 33

Peter Griffin 35

Sort based on two columns

You can sort based on multiple columns. When sorting by multiple columns, the second column is used only if the value of the first column is the same:

SELECT column_name(s) FROM table_name ORDER BY column1, column2