User count registered today

[thaidomizil]

Hello,

 

i'm trying to get the number of users that registered today and the number of users that registered yersterday (seperate), i've got this field in mysql: 'registertime' which stores data in this format 2011-11-14 14:53:49 also i have the field 'time' in the same format that updates everytime the user logs in.

 

Now previously i wanted to find out users online in last 24 hours, the code i used for that is this:

 

$query = "SELECT COUNT(*) as Anzahl FROM customers WHERE country = 'de' AND time BETWEEN DATE_SUB( NOW(), INTERVAL 24 HOUR ) and NOW() ";
$queryerg = mysql_query($query) OR die(mysql_error());
while($row = mysql_fetch_array($queryerg)){
  $customers_on_de_24 = $row[0];
} 

 

How could i edit that to select the count just by date and ignore the time (hours minutes seconds) ?

[thaidomizil]

Now i've tried this:

 

$query = "SELECT COUNT(*) as Anzahl FROM customers WHERE country = 'de' AND DATE('registertime') = DATE(NOW())";
$queryerg = mysql_query($query) OR die(mysql_error());
while($row = mysql_fetch_array($queryerg)){
  $registrationstoday = $row[0];
} 

 

But for some reason it's showing me 0 ? What am i doing wrong ?

 

Edit:

 

removed the ' ' .. it works now!

 

Now how can i subtract one day from this DATE(NOW())"; ? So it's showing me the result for yesterday

[jcbones]

<?php
//today
$query = "SELECT COUNT(*) as Anzahl FROM customers WHERE country = 'de' AND DATE('registertime') = CURDATE()";
$queryerg = mysql_query($query) OR die(mysql_error());
while($row = mysql_fetch_array($queryerg)){
  $registrationstoday = $row[0];
}
//yesterday
$query = "SELECT COUNT(*) as Anzahl FROM customers WHERE country = 'de' AND DATE('registertime') = DATE_SUB(CURDATE(),INTERVAL 1 DAY)";
$queryerg = mysql_query($query) OR die(mysql_error());
while($row = mysql_fetch_array($queryerg)){
  $registrationstoday = $row[0];
} 

 

MySQL Date Functions