Get info from mysql by date

Question

I have a script written in PHP which reports sales activity.

FOLLOWING currently fetching just values. Today Yesterday Last 7 days.

Today for field use the following code:

if($x==0) {
    $sql="SELECT SUM(incasat) FROM tichete WHERE DATE(datainchis) = DATE(NOW()) AND inchisde='$y'"; 
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_assoc($result);
    if(!empty($row['SUM(incasat)'])) echo $row['SUM(incasat)']; else echo '0';
}

Field yesterday to use the following code:

if($x==1) {
    $sql="SELECT SUM(incasat) FROM tichete WHERE datainchis BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE() AND inchisde='$y'";  
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_assoc($result);
    if(!empty($row['SUM(incasat)'])) echo $row['SUM(incasat)']; else echo '0';  
}

I would like to do like this. I wish I could show collections on each and every day like Today Yesterday 3 days ago 3 days but for 3 days ago i want to see like yesterday not amount just pays from this day.

Enclose the code.

function get_user_incasari($x,$y)
{ // 0 - azi, 1 - ieri, 7 - ultimele 7 zile, 30 - luna asta, 31 - luna trecuta
    if($x==0) {
        $sql="SELECT SUM(incasat) FROM tichete WHERE DATE(datainchis) = DATE(NOW()) AND inchisde='$y'"; 
        $result = mysql_query($sql) or die(mysql_error());
        $row = mysql_fetch_assoc($result);
        if(!empty($row['SUM(incasat)'])) echo $row['SUM(incasat)']; else echo '0';          
    } 
    if($x==1) {
        $sql="SELECT SUM(incasat) FROM tichete WHERE datainchis BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE() AND inchisde='$y'";  
        $result = mysql_query($sql) or die(mysql_error());
        $row = mysql_fetch_assoc($result);
        if(!empty($row['SUM(incasat)'])) echo $row['SUM(incasat)']; else echo '0';  
    }
    if($x==3) {
        $sql="SELECT SUM(incasat) FROM tichete WHERE datainchis BETWEEN DATE_ADD(CURDATE(), INTERVAL -3 day) AND CURDATE() AND inchisde='$y'";  
        $result = mysql_query($sql) or die(mysql_error());
        $row = mysql_fetch_assoc($result);
        if(!empty($row['SUM(incasat)'])) echo $row['SUM(incasat)']; else echo '0';  
    }
    if($x==7) {
        $sql="SELECT SUM(incasat) FROM tichete WHERE datainchis BETWEEN DATE_ADD(CURDATE(), INTERVAL -7 day) AND CURDATE() AND inchisde='$y'";  
        $result = mysql_query($sql) or die(mysql_error());
        $row = mysql_fetch_assoc($result);
        if(!empty($row['SUM(incasat)'])) echo $row['SUM(incasat)']; else echo '0';  
    }
    if($x==30) { //luna curenta
        $sql="SELECT SUM(incasat) FROM tichete WHERE MONTH(datainchis) = MONTH(CURDATE()) AND inchisde='$y'";   
        $result = mysql_query($sql) or die(mysql_error());
        $row = mysql_fetch_assoc($result);
        if(!empty($row['SUM(incasat)'])) echo $row['SUM(incasat)']; else echo '0';      
    }
    if($x==31) { //luna precedenta/trecuta
        $sql="SELECT SUM(incasat) FROM tichete WHERE MONTH(datainchis) = MONTH(CURDATE() - INTERVAL 1 MONTH ) AND inchisde='$y'";   
        $result = mysql_query($sql) or die(mysql_error());
        $row = mysql_fetch_assoc($result);
        if(!empty($row['SUM(incasat)'])) echo $row['SUM(incasat)']; else echo '0';      
    }
}

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Mar 14 '17 at 12:33

score 0 · Answer 1 · answered Mar 14 '17 at 11:57

You can use timestamps (like your datainchis column), truncated to date, in GROUP BY queries. It's easy. For example, this gives you a summary, by day, starting eight days ago and ending yesterday. (Note: CURDATE() means DATE(NOW()).)

 SELECT SUM(incasat) incasat_total, DATE(datainchis) datainchis
   FROM tichete
  WHERE inchisde='$y'
    AND datainchis >= CURDATE() - INTERVAL 8 DAY
    AND datainchis <  CURDATE()
  GROUP BY DATE(datainchis) WITH ROLLUP

Notice, please, that your code contains an error (a common error).

If you have timestamps and you say

 datainichis BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE()

you get all the rows with timestamps starting with midnight yesterday, up to and including midnight today. You actually want to exclude midnight today; those records belong to today, not yesterday.

     datainichis >= CURDATE() - INTERVAL 1 DAY
 AND datainichis <  CURDATE()

Notice the < in place of <=.

Your code also contains an inefficiency. The expression

 DATE(datainichis) = CURDATE()

is not sargable. It defeats the use of an index on the datainichis column.

I have written a little essay on this topic, here.

Get info from mysql by date

1 Answers1