PDA

View Full Version : Order Mysql Results??



jnscollier
05-21-2007, 05:15 AM
Hey all,

So here's the deal. I have a list of comments, and each comment gets an automatically generated commentid when it gets added to my mysql table. I just added the functionality to allow users to reply to other ppl's comments (with much difficulty and help!)... So I added another field to my table called replytoid. This field holds the commentid number of the comment being replied to.

When someone views a blog, I want it to display all the associated comments in ASC order. If a comment is actually a reply to another comment, it should show up directly under it (despite the commentid number).

I normally use ORDER by or Group by in my mysql queries to organize data for output. The pages are in php.



$cmtqry="select * from ".BLOG_COMMENTS." where eparent='".$HTTP_GET_VARS['blogid']."' order by commentid, replytoid";
$cmtres=mysql_query($cmtqry);
$cmtrows=mysql_num_rows($cmtres);
if($cmtrows > 0){
while($cmtarr=mysql_fetch_array($cmtres)){

But this doesn't seem to work correctly. I also used the conjunction of the group by and order by... that didn't work either.

Is there another way to do this?? Any help would be appreciated! You guys are a ton of help. THANKS IN ADVANCE!

boogyman
05-21-2007, 04:53 PM
this sounds like a database structuring problem, possibly?

do you have seperate tables for posts / replies?

here is how I would do organize it.

table 1: all posts (starter / reply)
table 2: post_has_reply
record 1: commentid
record 2: parent post

that way you can have an unlimited number of posts with as many replies to each as you wanted and you wont have to worry about redundant data being in a post table and a reply table all at the same time.

ps. if this isnt what you were looking for sorry

jnscollier
05-21-2007, 05:16 PM
Right now here's my table structure...

Commentid - Unique generated num for each comment
Comment
Eparent - blog id number comment is associated with
dtPostDate - date comment was posted
intMemberid - id for the person who posted the comment
replytoid - this defaults to zero if it's just a comment, if it's a reply to a comment this field holds the commentid the reply is to

So, if i split the table up into replies and comments (posts), would that be easier for sorting the display??

What I mean by that is... I'm having a problem displaying the comments in order (date posted, i just use the commentid for this because I don't track times comments are posted just date) and then the replies under the appropriate comments... Would this be easier to accomplish if I had two tables?

boogyman
05-21-2007, 07:45 PM
Right now here's my table structure...

Commentid - Unique generated num for each comment
Comment
Eparent - blog id number comment is associated with
dtPostDate - date comment was posted
intMemberid - id for the person who posted the comment
replytoid - this defaults to zero if it's just a comment, if it's a reply to a comment this field holds the commentid the reply is to

is this your only table?



What I mean by that is... I'm having a problem displaying the comments in order (date posted, i just use the commentid for this because I don't track times comments are posted just date) and then the replies under the appropriate comments... Would this be easier to accomplish if I had two tables?

well if the commentid is auto incremented, then you can order by the date then the commentid... the comments would be sorted by the date, and since the commentid is auto incremented, the ones that are posted first will display first because they will have a lower comment id.




So, if i split the table up into replies and comments (posts), would that be easier for sorting the display??

well that depends on how you set up your database and how well you know what you are doing. if you only have 1 table with the specs above, I can tell you right now that it is not optimized to full capacity. if you are trying to build like a message forum type thing then you would need alot more then just 1 table to be optimized. you would need at least 4 tables.

user_info
forum
message
msg_replies

for your example i would go for splitting up that table you have into 5 tables
user_info
forum
comment
comment_forum
comment_reply


does that make any sense?

jnscollier
05-22-2007, 04:41 PM
Thanks for your help, after reading your replies.... Everything just clicked!

An if-while loop through the comments and then a if-while loop through the replies seems to do the trick

boogyman
05-22-2007, 04:59 PM
Thanks for your help, after reading your replies.... Everything just clicked!

An if-while loop through the comments and then a if-while loop through the replies seems to do the trick

what do you mean an if-while loop?

jnscollier
05-22-2007, 05:08 PM
well this is what i did, and it seems to work perfectly so i don't know (i'm definitely NOT a php expert)

query the comments table by commentid ASC order
if rows exist for the blog, while going through the array... display the results.

query the replies
if replies exist for the blog, while going through the array, if the reply = the commentid number... display when commentid = replytoid

don't know if that makes much sense without posting all the code... care to see the page? if so, message me

boogyman
05-23-2007, 01:30 PM
you could have just posted your query, but if its working for you then thats cool fine

jnscollier
05-24-2007, 10:28 PM
lol yeah, that would've worked if i was at home and had the code at the time i wrote that post.

thanks :)