Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Solved: 1 Year, 4 Months, 3 Weeks ago TIME_NOW Column Error
#1
Solved: 1 Year, 4 Months, 3 Weeks ago
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
> SELECT finger FROM hand WHERE id='3'
Reply
#2
Solved: 1 Year, 4 Months, 3 Weeks ago
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'));
Reply
#3
Solved: 1 Year, 4 Months, 3 Weeks ago
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.
> SELECT finger FROM hand WHERE id='3'
Reply
#4
Solved: 1 Year, 4 Months, 3 Weeks ago
(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
Reply
#5
Solved: 1 Year, 4 Months, 3 Weeks ago
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!!
> SELECT finger FROM hand WHERE id='3'
Reply
#6
Solved: 1 Year, 4 Months, 3 Weeks ago
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");
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)