MyBB Community Forums

I have an error in my where clause. Trying to count posts made by x usergroup for community advancements program.

Unsure of why the where clause is incorrect because from all my reading that’s how you dictate which usergroup. Anyone know why this could be happening?

$count = $db->query('SELECT COUNT(*) AS postsnumbers FROM '.TABLE_PREFIX.'users u LEFT JOIN '.TABLE_PREFIX.'posts WHERE usergroup = 4');

ERROR MESSAGE:
MyBB SQL Error
MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE usergroup = 4' at line 1
Query:
SELECT COUNT(*) AS postsnumbers FROM mybbvo_users u LEFT JOIN mybbvo_posts WHERE usergroup = 4
Please contact the MyBB Group for technical support.

Try this:

$query = $db->query('SELECT COUNT(*) AS postsnumbers FROM '.TABLE_PREFIX.'users u LEFT JOIN '.TABLE_PREFIX.'posts ON (u.uid = posts.uid) WHERE u.usergroup = 4'); 
$count = $db->fetch_field($query, 'postsnumbers');

NEW ERROR:
MyBB SQL Error
MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1054 - Unknown column 'posts.uid' in 'on clause'
Query:
SELECT COUNT(*) AS postsnumbers FROM mybbvo_users u LEFT JOIN mybbvo_posts ON (u.uid = posts.uid) WHERE u.usergroup = 4
Please contact the MyBB Group for technical support.

Should posts.uid be p.uid instead?
No, nevermind, it shouldn’t lol.

Oh, woops. Try this:

$query = $db->query('SELECT COUNT(*) AS postsnumbers FROM '.TABLE_PREFIX.'users u LEFT JOIN '.TABLE_PREFIX.'posts ON (u.uid = '.TABLE_PREFIX.'posts.uid) WHERE u.usergroup = 4'); 
$count = $db->fetch_field($query, 'postsnumbers'); 

Super weird how coding works xD!

So basically all I was messing up on was the actual posts itself and not the where clause?

Also, as an added question. Why the extra row for the $count instead of just having it where $query is?

(2018-01-01, 07:14 PM)isoldehn Wrote: [ -> ]Super weird how coding works xD!

So basically all I was messing up on was the actual posts itself and not the where clause?

Also, as an added question. Why the extra row for the $count instead of just having it where $query is?

Yeah, when you do a JOIN, you need to tell it how to join the two tables. The "ON ..." part does that.

The $db->query function returns a query reference, not the actual number that you want. You then need to get the result out of the query itself. $db->fetch_field fetches a single column from the query result.