Select stats from different days

What is the most efficient way to do this?

Right now I am doing it this way..

$m= date("m");
$de= date("d");
$y= date("Y");
$userid = $_SESSION['user_id'];
//today
$date = date开发者_如何学JAVA("Y-m-d");
$today = mysql_query("SELECT * 
                      FROM cdr 
                      WHERE accountcode = '$userid' 
                        AND calldate LIKE '$date%'") or die(mysql_error());
$counttoday = mysql_num_rows($today);
//yesterday
$yesterdaydate = date('m/d/Y', mktime(0,0,0,$m,($de-1),$y));
$yesterday = mysql_query("SELECT * 
                          FROM cdr 
                          WHERE accountcode = '$userid' 
                            AND calldate LIKE '$yesterdaydate%'") or die(mysql_error());
$countyesterday = mysql_num_rows($yesterday);

then

print $counttoday;
print $countyesterday;

I know there has to be a better way to do this.

Let me know, thanks!

wouldnt this work?

SELECT calldate, COUNT(calldate) FROM cdr WHERE accountcode = '$userid' AND (calldate LIKE '$today%' OR calldate LIKE '$yesterday%')

why is today Y-m-d while yesterday is m/d/Y ?

$yesterdaydate = date('Y-m-d', strtotime('-1 day')); 

calldate in the table should be date or datetime. Then you can simply use

SELECT DATE_FORMAT(calldate, '%Y-%m-%d') `calldate`, COUNT(calldate) `count` FROM cdr WHERE accountcode = '$userid' AND calldate >= '$yesterdaydate' group by calldate 

You can compare calldate to $yesterdaydate to find out if it is yesterdays or todays count.

Since the other answers didn't mention it, it can actually be done in a single query, like so:

$today = date('Y-m-d');
$yesterday = date('Y-m-d', strtotime('yesterday'));

$mysql_query("SELECT COUNT(DATE(`calldate`) = '$today') `today`, 
    COUNT(DATE(`calldate`) = '$yesterday') `yesterday`
    FROM `cdr`
    WHERE `accountcode` = '$userid' 
        AND DATE(`calldate`) IN ('$today', '$yesterday')");

This returns the count for today in the first column and the count for yesterday in the second column.

If calldate is already a DATE field, you can leave off the DATE() cast; that would improve the performance in fact.

I don't know if this is an absolute better way to do what you're trying to do, but you can use the MySQL statement COUNT() to count the number of rows returned by a query.

So for example, writing

SELECT COUNT(*) 
FROM cdr 
WHERE accountcode = '$userid' 
AND calldate LIKE '$date%'

Will return a 1x1 table of results with the number of rows in the query. Then you can use

$counttoday = mysql_result($today, 0);

to fetch the count.

There doesn't seem to be a faster way to do this with one function call, unless you define your own function I suppose. But for a very large result from your initial query, I would imagine this is faster since I assume mysql_num_rows() has to count each row individually all over again. Doing it this way only goes over the rows of the query result once, when the result itself is being populated.