COUNT (SQL) problem

by DarSider - 25-06-2015, 04:01 AM
Closed Account
Posts:
145
Joined:
May 2015
Likes:
9
Credits:
65
Reputation:
7
2 Years of Service
#1
OP
Posted: 25-06-2015, 04:01 AM
Hello all
I have a small problem
I'm trying to do this query on phpmyadmin, to get member posts count where the member is the thread starter (the number of messages on my own threads)

Code:
SELECT COUNT(*) FROM mybb_threads as t, mybb_posts as p WHERE t.uid = p.uid AND t.uid = 1

And tried this :

Code:
SELECT COUNT(*) FROM mybb_threads as t, mybb_posts as p WHERE p.username = 'AmazOuz' AND t.username = 'DarSider'

But i get biiiig numbers (119639...etc)

I don't know where is the problem :noh:

Please help
Thanks
Nan Ihier Gelair Mordor
Posts:
2,626
Joined:
Jan 2015
Likes:
289
Credits:
7,879
Reputation:
89
2 Years of Service
#2
Posted: 25-06-2015, 04:26 AM
Code:
SELECT COUNT(t.tid) FROM mybb_threads t
LEFT JOIN mybb_posts as p ON( t.uid = p.uid )
WHERE t.uid = '1'
GROUP BY t.tid

Do not let your difficulties fill you with anxiety, after all it is only in the darkest nights that stars shine more brightly. - Ali(a.s)

Developer( PHP, Python, C++, HTML+CSS, JS I am available for Hire. Message Me for details.
@Believer rocks
Paradigm
Posts:
1,282
Joined:
May 2015
Likes:
43
Credits:
-1,051
Reputation:
45
2 Years of Service
#3
Posted: 25-06-2015, 04:28 AM (This post was last modified: 25-06-2015, 04:28 AM by Kill.)
Code:
SELECT postnum FROM mybb_users WHERE username = 'Kill'


On my own threads. ok.
Nan Ihier Gelair Mordor
Posts:
2,626
Joined:
Jan 2015
Likes:
289
Credits:
7,879
Reputation:
89
2 Years of Service
#4
Posted: 25-06-2015, 04:33 AM
I am not really sure what you are trying to achieve.

Tell me what you want to count? Number of threads or posts or threads+posts by a user?
Do not let your difficulties fill you with anxiety, after all it is only in the darkest nights that stars shine more brightly. - Ali(a.s)

Developer( PHP, Python, C++, HTML+CSS, JS I am available for Hire. Message Me for details.
Closed Account
Posts:
145
Joined:
May 2015
Likes:
9
Credits:
65
Reputation:
7
2 Years of Service
#5
OP
Posted: 25-06-2015, 04:39 AM
I have 10 threads
I have 30 posts
10 of these 30 posts = my own replies to my own threads.

I want a query to get the number of my own replies on my own threads Tongue
So i need to count the posts that have the same TID as the TID of my threads

I know it's difficult to understand :noh:
Nan Ihier Gelair Mordor
Posts:
2,626
Joined:
Jan 2015
Likes:
289
Credits:
7,879
Reputation:
89
2 Years of Service
#6
Posted: 25-06-2015, 04:56 AM
Then:

Code:
SELECT COUNT(p.pid) AS postnum, t.tid FROM mybb_posts p
LEFT JOIN mybb_threads t ON( t.tid = p.tid )
WHERE t.uid = '1'
GROUP BY t.tid

This should give you posts that you have made on each thread that you have made.
Do not let your difficulties fill you with anxiety, after all it is only in the darkest nights that stars shine more brightly. - Ali(a.s)

Developer( PHP, Python, C++, HTML+CSS, JS I am available for Hire. Message Me for details.
Closed Account
Posts:
145
Joined:
May 2015
Likes:
9
Credits:
65
Reputation:
7
2 Years of Service
#7
OP
Posted: 25-06-2015, 05:15 AM (This post was last modified: 25-06-2015, 05:24 AM by DarSider.)
Tried it in PHPMyAdmin
It returns :

My Threads TID, each TID has his own POSTNUM which is the number of posts in that thread
And not all my threads are shown, only some of them
http://prntscr.com/7l0jdh

EDIT : I added AND p.uid = 1 in the WHERE condition
I get each TID with my posts number in that thread.
I just have now to delete the group by, and i get the postnum
I just have to do this to get what i need :

SELECT COUNT(p.pid) FROM mybb_posts p LEFT JOIN mybb_threads t ON( t.tid = p.tid ) WHERE t.uid = '1' AND p.uid = '1'


LOVE YOU SOZIN Heart3
The last reply on this thread is older than a month. Please do not unnecessarily bump it.
Register an account or login to reply
Create an account
Create a free account today and start posting right away. It only takes a few seconds.
Login
Log into an existing account.
1 Guest(s)