mysql help

PRSBOY

$quer2 = mysql_query("
SELECT
    f.name,
    f.id,
    count(t.id) AS count,
    count(t.id) AS count2 where t.topic = ""
FROM
	forums_forums f
	left JOIN forums_topics t ON t.fid = f.id
WHERE f.id = '" . $row['id'] . "' GROUP BY t.fid");

now i know count(t.id) AS count2 where t.topic = "" isnt going to work, obviously, but how can i get something that will do that?

Thanks in advance 🙂

bastien

not sure you can have 2 count(field) in a statement

PRSBOY

SELECT count(id) AS c1, count(id) AS c2 FROM `forums_topics` WHERE fid = '3' AND topic = 'yes'

works fine though..

Lars_Berg

What do you want to count?

The total number of topics?

The number of forums?

The number of topics in each forum?

PRSBOY

i first want to count the number of posts (including the fist post (which creates the topic)) then i want to count just the posts that are the topic starters, that way i can get the number of topics (topic = 'yes') and the total number of posts .

Lars_Berg

SELECT
    f.name,
    f.id,
    count(*) AS count,
    count(distinct t.id) AS count2
FROM
    forums_forums f
    left JOIN forums_topics t ON t.fid = f.id
WHERE f.id = " . $row['id'] . " 
GROUP BY t.fid,f.name

PRSBOY

that doesnt get topic = yes, id is post id (so each POST has a different ID) and the tid is the same for each whole topic + all posts.

Lars_Berg

count(distinct t.tid)

if I understood correctly

or you can do

sum(case when topic <> '' then 1 else 0 end)

PRSBOY

thanks a lot, that worked perfect 🙂

now i just need to find a nice way to get the last posted data 😛