Moiz Mansoor Ali Moiz Mansoor Ali - 12 days ago 5
MySQL Question

Acquiring the last 3 records from a database in ascending order using mySQL & PHP

I am creating a simple php file to read the last 3 records added to my database in phpMyAdmin. The problem I face is that I want the last 3 added records in ascending order and I can only get them in descending order. I tried the following code which worked for some other people but does not work for me in phpMyAdmin.


SELECT * FROM (SELECT * FROM studio ORDER BY studioId DESC LIMIT 3) ORDER BY studioId;


This is how I implemented the code into the php file.


$sql = "SELECT * FROM (SELECT * FROM studio ORDER BY studioId DESC LIMIT 3) ORDER BY studioId";
$query = mysqli_query($conn,$sql);
if(mysqli_num_rows($query) > 0){
while($row = mysqli_fetch_assoc($query)){
echo $row["studioName"]." ".$row["studioAddress"]." ".$row["studioPCode"]." ".$row["studioTelNo"]."<br/>";
}
}else{
echo "There Are No Studios Added";
}



For some reason the above code only works when I replace the first line of the above code with this line.


$sql = "SELECT * FROM studio ORDER BY studioId DESC LIMIT 3";


This is the code I used to create the table studio.


CREATE TABLE Studio(

studioId INT(5) NOT NULL,

studioName VARCHAR(50) NOT NULL,

studioAddress VARCHAR(50) NOT NULL,

studioPCode VARCHAR(7) NOT NULL,

CONSTRAINT ck_studioPCode

CHECK(studioPCode LIKE '_ _'),

studioTelNo VARCHAR(11) NOT NULL,

CONSTRAINT ck_studioTelNo

CHECK(LEN(studioTelNo) = 11),

PRIMARY KEY(studioId),

UNIQUE(studioName)

);

Answer

Use aliasing, this might work

$sql = "SELECT * FROM (SELECT * FROM studio ORDER BY studioId DESC LIMIT 3) AS stdId ORDER BY studioId";

Try this code and let me know if it's still not working