CODEWITHSUNDEEP
phpsql
user632347
user632347

Reputation: 835

How to get the recently joined users from mysql table

So I started my coding wit MYSQL and I have a table like following

CREATE TABLE IF NOT EXISTS `my_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`school_division` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`email` text NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;

Now I want to show the recently joined students of a class in that class page. How can i do it? I tried the following. 

"SELECT * FROM my_users WHERE school_division={$divsion} ORDER BY 'id' DESC LIMIT 0 , 5";

But its listing only the first five students of the class. These students are the first joined users. How can I get the last 5 students who joined?

Upvotes: 0

Views: 95

Answers (6)

user1061620
user1061620

Reputation:

The column id is an auto_increment column. Retrieving the last five users is thereby easily accomplished by quering for the last highest five values of that column. Explicitly ordering by the date column is redundant.

Upvotes: 1

Cyclonecode
Cyclonecode

Reputation: 30091

Use date instead of id:

 "SELECT * FROM my_users WHERE school_division={$divsion} ORDER BY 'date' DESC LIMIT 0 , 5";

Upvotes: 2

arahaya
arahaya

Reputation: 1020

I think it should be

ORDER BY `id` DESC

not a semicolon

Upvotes: 0

ping localhost
ping localhost

Reputation: 479

order by date not id

"SELECT * FROM my_users WHERE school_division={$divsion} ORDER BY 'date' DESC LIMIT 0 , 5";

Upvotes: 0

Bogdan
Bogdan

Reputation: 44556

Sort descending by date:

"SELECT * FROM my_users WHERE school_division={$divsion} ORDER BY 'date' DESC LIMIT 0 , 5";

Upvotes: 2

Bas Slagter
Bas Slagter

Reputation: 9929

You should order by date instead of id, like:

$sql = "SELECT * FROM `my_users` WHERE `school_division`='".$divsion."' ORDER BY `date` DESC LIMIT 0 , 5"

Or, if you somehow must order on id...do an order by id ascending instead of descending.

Upvotes: 0

Related Questions