homepage Welcome to WebmasterWorld Guest from 54.234.147.84
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
sorting db query
sorting db query
dev7




msg:4568137
 5:18 am on Apr 26, 2013 (gmt 0)

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




msg:4568155
 7:09 am on Apr 26, 2013 (gmt 0)

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




msg:4568242
 12:01 pm on Apr 26, 2013 (gmt 0)

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




msg:4568244
 12:16 pm on Apr 26, 2013 (gmt 0)

Change DESC to ASC.

dev7




msg:4568252
 12:42 pm on Apr 26, 2013 (gmt 0)

@StoutFiles: no

nettulf




msg:4568287
 5:17 pm on Apr 26, 2013 (gmt 0)

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




msg:4568300
 6:35 pm on Apr 26, 2013 (gmt 0)

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

nettulf




msg:4568304
 7:13 pm on Apr 26, 2013 (gmt 0)

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

topr8




msg:4568309
 7:47 pm on Apr 26, 2013 (gmt 0)

@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




msg:4568868
 12:16 pm on Apr 29, 2013 (gmt 0)

@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




msg:4568921
 2:32 pm on Apr 29, 2013 (gmt 0)

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

nettulf




msg:4569296
 10:47 am on Apr 30, 2013 (gmt 0)


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




msg:4569743
 7:10 pm on May 1, 2013 (gmt 0)

@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




msg:4570129
 8:16 pm on May 2, 2013 (gmt 0)

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

swa66




msg:4570146
 9:01 pm on May 2, 2013 (gmt 0)

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

https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

dev7




msg:4571068
 11:09 am on May 6, 2013 (gmt 0)

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!)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved