delato468 delato468 - 23 days ago 6
SQL Question

sql take all querys from last month

I followed the first answer on this question but i have some problems with my sql query. I don´t get any result.

What error i made ?

How can i set CURDATE always on the first day of the month ?

SELECT DATE_FORMAT(`date`, '%Y-%m-%d') , `price`
FROM `sales`
WHERE `id` = :id
AND (`date` BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE())


Edit Whole code:


$verkaufmonat3 = 0;
$verkaufmonatanzahl2 = 0;
$verkaufmonat4 = array();
$sqluser5 = $X['dbh']->prepare("SELECT DATE_FORMAT(`date`, '%Y-%m-%d') , `preis` FROM `verkauf` WHERE `vertreterid` = :id AND (`date` BETWEEN CURDATE(), '%Y-%m-01' - INTERVAL 60 DAY AND CURDATE(), '%Y-%m-01')");
$sqluser5->execute(array(
':id'=>$_SESSION['id']
));
$verkaufmonatanzahl2 = $sqluser5->rowCount();
$verkaufmonat4 = $sqluser5->fetchAll();
for ($a = 0; $a <= $verkaufmonatanzahl2; $a++) {
$verkaufmonat3 += $verkaufmonat2[$a]['preis'];
}


In my database i have a sale on the 28.10.2016 wich has for ex. a value of 50 eur. So my $verkaufmonat3 should be 50 but it isn´t, it´s 0.

Answer

Your question isnt clear but to get the first day of current month then you use

 SELECT DATE_FORMAT(CURDATE() , '%Y-%m-1') 

So if you want the previous month data try

SELECT 
    DATE_FORMAT(CURDATE() , '%Y-%m-1') - INTERVAL 1 MONTH as first_day,
    DATE_FORMAT(CURDATE() , '%Y-%m-1') - INTERVAL 1 DAY as last_day

MEANING

 WHERE `date` BETWEEN  DATE_FORMAT(CURDATE() , '%Y-%m-1') - INTERVAL 1 MONTH
                  AND  DATE_FORMAT(CURDATE() , '%Y-%m-1') - INTERVAL 1 DAY