MyBB Support

COUNT (SQL) problem

Submitted by DarSider, , Thread ID: 5026

Thread Closed
DarSider
Member
Level:
0
Reputation:
13
Posts:
149
Likes:
14
Credits:
55
25-06-2015, 04:01 AM
#1
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
- Web Developer & Designer

Website : https://developement.design/
Skype : ef.team
Email : [email protected]

RE: COUNT (SQL) problem

Sozin
Nan Ihier Gelair Mordor
Divine
Level:
0
Reputation:
91
Posts:
2.33K
Likes:
374
Credits:
11K
25-06-2015, 04:26 AM
#2
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.

RE: COUNT (SQL) problem

Coin
Level:
0
Reputation:
59
Posts:
1.13K
Likes:
54
Credits:
243
25-06-2015, 04:28 AM
This post was last modified: 25-06-2015, 04:28 AM by Kill
#3
Code:
SELECT postnum FROM mybb_users WHERE username = 'Kill'


On my own threads. ok.

RE: COUNT (SQL) problem

Sozin
Nan Ihier Gelair Mordor
Divine
Level:
0
Reputation:
91
Posts:
2.33K
Likes:
374
Credits:
11K
25-06-2015, 04:33 AM
#4
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.

RE: COUNT (SQL) problem

DarSider
Member
Level:
0
Reputation:
13
Posts:
149
Likes:
14
Credits:
55
OP
25-06-2015, 04:39 AM
#5
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:
- Web Developer & Designer

Website : https://developement.design/
Skype : ef.team
Email : [email protected]

RE: COUNT (SQL) problem

Sozin
Nan Ihier Gelair Mordor
Divine
Level:
0
Reputation:
91
Posts:
2.33K
Likes:
374
Credits:
11K
25-06-2015, 04:56 AM
#6
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.

RE: COUNT (SQL) problem

DarSider
Member
Level:
0
Reputation:
13
Posts:
149
Likes:
14
Credits:
55
OP
25-06-2015, 05:15 AM
This post was last modified: 25-06-2015, 05:24 AM by DarSider
#7
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 <33
- Web Developer & Designer

Website : https://developement.design/
Skype : ef.team
Email : [email protected]

Users browsing this thread: 1 Guest(s)