I'm making a basic discussion board program (no categories). What I'm trying to do is get as much info about a thread as I can with a single query, and so far I been able to get data from my threads, posts, and users table with:
threads.updated as last_post_time,
threads.last_post_id as last_post_id,
users.user_name as user_name,
posts.subject as subject,
posts.user_id as user_id
from threads, users, posts
where threads.post_id = posts.id and
posts.user_id = users.id
order by last_post_time desc
but I also want to get a count of the number of posts within the thread in the same query, instead of my having to do:
for each record.
Any ideas on how I can squeeze this into my existing query?
Philip
------------------
Limecat is not pleased.
Code:
select threads.id as thread_id, threads.updated as last_post_time,
threads.last_post_id as last_post_id,
users.user_name as user_name,
posts.subject as subject,
posts.user_id as user_id
from threads, users, posts
where threads.post_id = posts.id and
posts.user_id = users.id
order by last_post_time desc
but I also want to get a count of the number of posts within the thread in the same query, instead of my having to do:
Code:
$rec->{posts} = $dbh->selectrow_array("select count(*) from posts where posts.thread_id = $rec->{thread_id}");for each record.
Any ideas on how I can squeeze this into my existing query?
Philip
------------------
Limecat is not pleased.
subquery problem