PHP MySQL ORDER BY bilan saralash
MySQL ma’lumotlar bazasidan ma’lumotlarni tanlang va tartib qiling
SQL ORDER BY bandi natijalar to‘plamini o‘sish yoki kamayish tartibida tartiblash uchun ishlatiladi.
ORDER BY bandi sukut bo‘yicha yozuvlarni o‘sish tartibida tartiblaydi. Yozuvlarni kamayish tartibida saralash uchun DESC kalit so‘zidan foydalaning.
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
SQL haqida ko‘proq ma’lumot olish uchun bizning SQL qo‘llanmamizga tashrif buyuring.
MySQLi bilan tanlang va tartib qiling
Quyidagi misol MyGuests jadvalidan id, ism va familiya ustunlarini tanlaydi. Yozuvlar familiya ustuni bo‘yicha tartiblanadi:
Misol - MySQLi obyektga yo‘naltirilgan
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname";
// Execute the SQL query
$result = $conn->query($sql);
// Process the result set
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Misolni ishga tushirish »
Kod tushuntirish
Birinchidan, MyGuests jadvalidan id, ism va familiya ustunlarini tanlaydigan SQL so‘rovini o‘rnatamiz. Yozuvlar familiya ustuni bo‘yicha tartiblanadi.
Keyingi kod satri $conn->query($sql) so‘rovni ishga tushiradi va olingan ma’lumotlarni $result deb nomlangan o‘zgaruvchiga joylashtiradi.
Keyin $result->num_rows>0 qatori qaytarilgan 0 dan ortiq qator mavjudligini tekshiradi.
Agar 0 dan ortiq satr qaytarilsa, fetch_assoc() funksiyasi natijalar to‘plamini biz aylanib o‘tishimiz mumkin bo‘lgan assotsiativ massivga joylashtiradi. while() sikli natijalar to‘plami bo‘ylab o‘tadi va id, firstname va lastname ustunlaridan ma’lumotlarni chiqaradi.
Quyidagi misol MySQLi-da yuqoridagi misol bilan bir xil narsani ko‘rsatadi protsessual metod:
Misol - MySQLi protsessual
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname";
$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["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Misolni ishga tushirish »
Natijani HTML jadvaliga qo‘ying
Bu yerda biz natijani HTML jadvaliga joylashtiramiz:
Misol - MySQLi obyektga yo‘naltirilgan
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>ID</th><th>Name</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
Misolni ishga tushirish »
PDO bilan tanlang va tartib qiling
Bu yerda MyGuests jadvalidan id, familiya va familiya ustunlarini tanlaymiz. Yozuvlar familiya ustuni bo‘yicha tartiblanadi va u HTML jadvalida ko‘rsatiladi:
Misol - PDO
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("Could not connect. " . $e->getMessage());
}
try {
$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname'";
// Execute the SQL query
$result = $conn->query($sql);
// Process the result set
if ($result->rowCount() > 0) {
echo "<table><tr><th>ID</th><th>Firstname</th><th>Lastname</th></tr>";
// Output data of each row
while($row = $result->fetch()) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "</tr>";
}
echo "</table>";
unset($result);
else {
echo "No records found.";
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
W3Schools Pathfinder
Yutuqlaringizni kuzating – bu bepul!