0

I want to echo number of users registered on my site's admin panel today[counting] and yesterday using PHP.

I am saving their signup date in this format

2017-01-09 20:32:40

After googling i found this

$daterange = "24 HOUR"; 
$sql = "SELECT COUNT(*)  
FROM ad_total_today      
WHERE DATE_SUB(CURDATE(),INTERVAL $daterange) 
<= FROM_UNIXTIME(date_time)"; 

$res = mysql_query($sql); 
$today = mysql_result ($res, 0, 0);

But I cant seem to understand it.Can anyone help me echo Total number of users registered today and yesterday?

user7550234
  • 21
  • 2
  • **Don't** use the **deprecated and insecure** `mysql_*`-functions. They have been deprecated since PHP 5.5 (in 2013) and were completely removed in PHP 7. Use MySQLi or PDO instead. – M. Eriksson Feb 11 '17 at 13:55
  • 1
    *"But I cant seem to understand it"* - can't understand what exactly? where are you echoing it and are there any errors? The question IMHO is both too broad and unclear; mostly unclear though. The schema is unknown as to where UNIXTIME is/what. – Funk Forty Niner Feb 11 '17 at 13:56
  • This is just the sql query .When i try to echo $today . i get errors. i want to echo total number of users registered on my site yesterday and echo them in admin panel – user7550234 Feb 11 '17 at 13:57
  • ...errors, again, being "what"? – Funk Forty Niner Feb 11 '17 at 13:57
  • ok, someone popped an answer below, ask them. Maybe it's the "magic" answer you were looking for. – Funk Forty Niner Feb 11 '17 at 13:58
  • 1
    If you are going to use SQL, study enough so you can understand the code that you have found. It is not difficult; however, don't use the deprecated "mysql_" functions. – Gordon Linoff Feb 11 '17 at 13:58
  • Just like @GordonLinoff said. To put this in another way: *"If you don't know about the race car you're driving or don't know how to drive (a car) yet, don't get in the driver's seat until you know exactly what you're dealing with, and the parts required to fix it, when it does break"*. – Funk Forty Niner Feb 11 '17 at 14:01

2 Answers2

1

A solution is to use the MySQL function CURDATE(), format your column to date-only with DATE() (so we don't have to deal with hour/seconds/minutes). Then you select what's equal to today or yesterday. I've used the SUBCAT() function to subtract one day from CURDATE().

SELECT COUNT(*) as `count`
FROM ad_total_today 
WHERE DATE(date_time) = CURDATE() 
   OR DATE(date_time) = SUBDATE(CURDATE(), 1)

Alternatively, you can use IN instead of two conditions.

SELECT COUNT(*) as `count`
FROM ad_total_today 
WHERE DATE(date_time) IN (CURDATE(), SUBDATE(CURDATE(), 1))

You should really stop using MySQL functions, they are old, deprecated, insecure and no longer maintained. Switch to either PDO or MySQLi - and don't forget to use parameterized queries with placeholders when dealing with variables in your query - this is the only way to protect your data from SQL injection.

References and readingmaterial

Community
  • 1
  • 1
Qirel
  • 25,449
  • 7
  • 45
  • 62
0

Try Below Query

SELECT COUNT(*)  
FROM ad_total_today      
WHERE DATE_SUB IN (CURDATE(), CURDATE() + INTERVAL 1 DAY)
Sujal Patel
  • 2,444
  • 1
  • 19
  • 38