MyBB Community Forums

Full Version: TIME_NOW Column Error
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I'm trying to use TIME_NOW to count x posts in the last month. I keep running across a TIME_NOW error.

Any idea why this error is popping up? What I'm doing wrong here?

$AdminCountQuery = $db->query('SELECT COUNT(*) AS postsnumbers FROM '.TABLE_PREFIX.'users u LEFT JOIN '.TABLE_PREFIX.'posts ON (u.uid = '.TABLE_PREFIX.'posts.uid) WHERE dateline > TIME_NOW - 2592000 AND u.usergroup = 3'); 

$AdminCount= my_number_format($db->fetch_field($AdminCount, 'postsnumbers'));


The code itself works just fine to count total posts if I take out dateline, unfortunately though I just need to count posts in the last 30 days.
$AdminCountQuery = $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 = 3'); 

$AdminCount= my_number_format($db->fetch_field($AdminCount, 'postsnumbers'));



Help is appreciated. Smile
TIME_NOW is a PHP constant like TABLE_PREFIX. You have to write it the same way in your query.
Addidionally you have to use the query $AdminCountQuery in fetch_field instead of $AdminCount.

$AdminCountQuery = $db->query('SELECT COUNT(*) AS postsnumbers FROM '.TABLE_PREFIX.'users u LEFT JOIN '.TABLE_PREFIX.'posts ON (u.uid = '.TABLE_PREFIX.'posts.uid) WHERE dateline > '.TIME_NOW.' - 2592000 AND u.usergroup = 3');

$AdminCount= my_number_format($db->fetch_field($AdminCountQuery, 'postsnumbers'));
Quote:1054 - Unknown column 'TIME_NOW' in 'where clause'
^ Error message.

I am unsure what you mean by
Quote:Addidionally you have to use the query $AdminCountQuery in fetch_field instead of $AdminCount.
(01-24-2018, 07:04 PM)isoldehn Wrote: [ -> ]
Quote:1054 - Unknown column 'TIME_NOW' in 'where clause'
^ Error message.

You have to use the exact code I posted above to avoid this error. I even tested it again to ensure it works as expected.

(01-24-2018, 07:04 PM)isoldehn Wrote: [ -> ]I am unsure what you mean by
Quote:Addidionally you have to use the query $AdminCountQuery in fetch_field instead of $AdminCount.

Compare your last line with mine. Wink
Oh my goodness, I didn't even realize I had made that mistake! xD

Thanks for the help, I didn't know the TIME_NOW part needed to be in the '. and .' prefixes. I had searched it in the global.php file and it was just used as TIME_NOW.

Appreciate the help!!
Change your single quotes to double quotes.

$AdminCountQuery = $db->query("SELECT COUNT(*) AS postsnumbers FROM ".TABLE_PREFIX."users u LEFT JOIN ".TABLE_PREFIX."posts ON (u.uid = ".TABLE_PREFIX."posts.uid) WHERE dateline > TIME_NOW - 2592000 AND u.usergroup = 3");

It matters.


Or use the MySQL function UNIX_TIMESTAMP()

$AdminCountQuery = $db->query("SELECT COUNT(*) AS postsnumbers FROM ".TABLE_PREFIX."users u LEFT JOIN ".TABLE_PREFIX."posts ON (u.uid = ".TABLE_PREFIX."posts.uid) WHERE dateline > UNIX_TIMESTAMP() - 2592000 AND u.usergroup = 3");