Hello, for my message system im trying to find the easiest way to get the latest messages rather than all of them, i initially tried

 

$sqlquery = "SELECT * FROM `messages` WHERE ((uid1='$cid' OR uid2 ='$cid') AND (uid1='$otherid' OR uid2 ='$otherid')) ORDER BY id ASC LIMIT 20";

 

but obviously thats bringing the first 20 messages ever sent, if your slightly confused why its the first 20 not the last, its because my messaging system is showing newest messages from bottom to oldest at the top (like facebook and similar messaging systems)

 

Any help? Thanks, Gary

Edited May 27, 201115 yr by web-itec

I'm not quite sure what you mean, but have you tried order by DESC?

If the ID field is auto-incrementing, the newest posts will always have the highest values. If you order by DESC instead, you'll get the latest 20. If they're in the wrong order you can assign the returned values to an array and reverse it before you use it.

 

Edited for clarity:

 

ASC vs DESC looks at the whole dataset or table to get the values, and takes the number specified by LIMIT from either the top (DESC) or the bottom (ASC).

Edited May 27, 201115 yr by Renaissance-Design

If the ID field is auto-incrementing, the newest posts will always have the highest values. If you order by DESC instead, you'll get the latest 20. If they're in the wrong order you can assign the returned values to an array and reverse it before you use it.

 

Edited for clarity:

 

ASC vs DESC looks at the whole dataset or table to get the values, and takes the number specified by LIMIT from either the top (DESC) or the bottom (ASC).

 

good idea thanks +1

just to let you know, i got this to work pretty easily, the only problem was though, is it took just as long to load as before when loading in all the messages, so im just goin to reverse the whole thing all together and use limit on descending order, thanks anyway

Are there indexes on uid1 and uid2? Are there fields in the table you don't need for this specific query?

 

Indexing uid1 and uid2 and specifying only the fields you want should really boost the performance of this particular query.

Are there indexes on uid1 and uid2? Are there fields in the table you don't need for this specific query?

 

Indexing uid1 and uid2 and specifying only the fields you want should really boost the performance of this particular query.

 

they arent indexed no, ive just reversed everything now anyway and using limit, but thanks anyway mate, much appreciated