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.
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.
(2018-01-24, 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.
(2018-01-24, 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.
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");