sorting db query

[dev7]

Hello to everyone. Today is my first day here on webmasterworld. i am an amateur web developer started as a hobby :-) i am working on a social networking website.

i am facing tough time finding a way to sort database query result according to my needs. here is a database layout.

ID -- acc_id -- date so on...
---------------------------------
1 12345 unix time ...
2 12345 unix time ...
3 12345 unix time ...
like this it goes on....
54 12345 unix time ...
55 12345 unix time ...
---------------------------------
what am trying to do is fetch the latest 10 posts and sort it in desc order (by ID or date) so that the latest post will be at the bottom and oldest on top. this way the next 10 posts will be fetched through ajax in similar manner.
-----------------
46 12345 date
47 12345 date
this way...
54 12345 date
55 12345 date
-----------------
i cant figure out how to sort the query. can anyone show me ways to do so. i would appreciate your help :-)

[omoutop]

Your basic query should be modified like this:

$query = "SELECT .... FROM tableName WHERE .... ORDER BY fieldName DESC LIMIT 10"

ORDER BY [fieldName] DESC will sort your result in a descending order (latest to oldest)

LIMIT 10 will restrict your result set to 10 records

[dev7]

hi omoutop, my code somewhat look like this:

"SELECT
m.ID,m.acc.m.date,
a.msg_id,a.id
FROM adc m INNER JOIN efg a on (m.ID=b.msg_id)
ORDER BY m.ID DESC LIMIT 10
"

query result:

55 12345 date
54 12345 date
this way...
47 12345 date
46 12345 date

which means latest post goes on top and oldest at bottom. this is not what i want. i need to sort it just the opposite. old->latest;

please correct me if am wrong.

[StoutFiles]

Change DESC to ASC.

[dev7]

@StoutFiles: no

[nettulf]

Just put another select outside the one that works, and sort it ascending. Something like this (not checked for syntax errors):

SELECT mid, acc, date, amid, aid from (
SELECT m.ID as mid, m.acc as acc, m.date as date, a.msg_id as amid, a.id as aid
FROM adc m INNER JOIN efg a on (m.ID = a.msg_id)
ORDER BY mid DESC LIMIT 10 )
as xx order by xx.mid ASC :

[topr8]

how come StoutFiles is wrong? that is the obvious thing to do and should reverse the sort order.

[nettulf]

Sounds obvious, but that would get the 10 oldest post. He wanted the 10 newest posts, but sorted old -> new.

[topr8]

@nettulf - yeah, sorry, my bad for not reading it properly!

and that's a neat trick you did to get the order right! i wouldn't have thought of that, i would probably have made a temporary table or used soem kind of subquery - you much quicker and easier!

[dev7]

@nettulf: Thank you so much for the break through. This approach seems promising but i have never used subqueries before. so let me try to work, and one more thing - my database is having many to many relationships and using INNER JOIN (for single table). now am confused!

let me give you the code:

from class file:
----------------
parent::query("
SELECT m.msg_id,m.acc_id,b.msg_id .........
FROM eb_msg m
INNER JOIN eb_msg_body b on m.msg_id = b.msg_id
WHERE m.acc_id = 123 ORDER BY m.date DESC 10
");

@topr8: thanks for participating :-)

[dev7]

* ORDER BY m.date DESC LIMIT 10
sorry for the mistake

[nettulf]

My example above should be working with maybe some tweaks for the column names. It does not matter what happens inside (inner joins). As long as you have a working query that gives the data you want, in wrong order, it can be re-sorted like this.

But the syntax itself depends a bit on how your program are reading the data after the select; if you are depending on column names or just column numbers like [0], [1] and so on...

[dev7]

@nettulf: theres a column name read (TINYINT(1)) default val 0, when i add this at line 2 it throws an error saying [Trying to get property of non-object]. and i dont dont know why? the code bellow is working as intended. so that read column was creating problem after i sort it out. any idea what may be the problem?

1. parent::query("
2. SELECT msg_id,acc_id,msg_to_id,date,type,status,spam,attc,body FROM (
3. SELECT
4. m.acc_id,m.msg_to_id,m.date as date,m.type,m.status,m.read,m.spam,m.attc,
5. b.msg_id,b.body
6. FROM eb_msg m
7. INNER JOIN eb_msg_body b on m.msg_id = b.msg_id
8. WHERE m.acc_id = '$acc_id' AND m.msg_to_id = '$str2' {$jdate}
9. ORDER BY m.date DESC ". $querylimit ."
10. ) AS x ORDER BY x.date ASC
11. ");

heres the table structure:

CREATE TABLE `eb_portal`.`eb_msg` (
`msg_id` int(11) NOT NULL,
`acc_id` int(11) NOT NULL,
`msg_to_id` int(11) NOT NULL,
`date` int(10) DEFAULT NULL,
`type` tinyint(1) DEFAULT '0' COMMENT '1=blocked',
`status` tinyint(1) DEFAULT '0' COMMENT '1=replied',
`read` tinyint(1) DEFAULT '0' COMMENT '1=read',
`spam` tinyint(1) DEFAULT '0' COMMENT '1=spam',
`attc` tinyint(1) DEFAULT '0' COMMENT '0=no attc',
PRIMARY KEY (`msg_id`),
KEY `acc_id` (`acc_id`),
KEY `msg_to_id` (`msg_to_id`),
KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

[nettulf]

Sorry... no idea, google that message? It seems to have something to do with objects/arrays... :(

[swa66]

Isn't "read" on the reserved keyword list ?

[dev.mysql.com...]

[dev7]

aaah! its listed in reserved keywords. thanks swa66 :-) problem solved. i will invite you guys to join me when i finish my website :-) (will need months to take shape as am working alone!)