MySQL all posts by member and post that started corresponding thread

[zylstra]

What is the MySQL code for selecting all posts by a particular member and the posts that started the corresponding thread for those posts?

Thanks.

[Dismounted]

You want it all lumped into one query, right?

[zylstra]

Yeah, a member of my board would like a file of all his posts and the first post in those threads that he responded to. His posts and their corresponding starting posts will have to be close to each other in the query result, else it won't make sense to him.

[zylstra]

anyone?

[Eikinskjaldi]

What do you want to have happen when there are multiple posts by him in a thread? Lets say there is a thread in which he has replied 100 times. What kind of display are you looking for?

[zylstra]

A list of his posts in a thread followed by (or preceded by) the first post in that thread,
followed by a list of his posts in the next thread that he posted in followed by (or preceded by) the first post in that thread,
followed by a list of his posts in the next thread that he posted in followed by (or preceded by) the first post in that thread,
etc.

[Eikinskjaldi]

It will require the creation of a temporary table, be a big slow process and quite possibly crash your server. You would be better off scripting it in php or any other language that can connect to your db.

This query will return a list of firstpostid, user's posts:

[sql] select concat_ws(',',t.firstpostid, group_concat(p.postid))
from thread t
join post p using (threadid)
where p.userid=your_users_id
group by t.firstpostid[/sql]

example output

 37916,75977,75978,79764,79767,81094,81096

You can then take that list and pass it into a second query
[sql]select p.username, p.pagetext from post where postid in (above_list)[/sql]

Note that group_concat has a character limit, which can be changed if you have the appropriate mysqld permissions.

[zylstra]

Thanks so much Eikinskjaldi! Would it be easier on the server to return this format:

user post
firstpost
user post
firstpost
...

even though the firstpost might be repeated?

[Eikinskjaldi]

Originally Posted by zylstra

Thanks so much Eikinskjaldi! Would it be easier on the server to return this format:

user post
firstpost
user post
firstpost
...

even though the firstpost might be repeated?

yes it would.

[sql]
select p.username, p.pagetext, q.username, q.pagetext
from post p
joint thread t using (threadid)
join post q on (t.firstpostid=q.postid)
where p.userid=your_users_id
[/sql]

[zylstra]

Great, thank you.