1.6 PHP - Total sales (year, month) sql query

[luhlacom]

Hi,  

in Prestashop SQL Manager I have query for total sales by month, year. How I can view in PHP table ? I use Prestashop 1.6.

Function query in SQL Manager:

SELECT 
  year(date_add),date_format(date_add,'%M'),count(id_order), sum(total_paid) 
  FROM ps_orders 
  WHERE year(date_add) > 2018
  GROUP BY year(date_add),month(date_add) 
  ORDER BY year(date_add),month(date_add)

IN PHP File: (not working for me)

$data = Db::getInstance()->ExecuteS(' 
SELECT 
  year(date_add),date_format(date_add,'%M'),count(id_order), sum(total_paid) 
  FROM ps_orders 
  WHERE year(date_add) > 2018
  GROUP BY year(date_add),month(date_add) 
  ORDER BY year(date_add),month(date_add)dd)
       ');   
 
echo "Počet objednávek: ";
echo '<section class="table1"><table>';
         foreach($data as $order)
    {  
         
         echo '<td>"'.$order['date_add'].'"</td>';
         echo '<td>"'.$order['date_add'].'"</td>';
         echo '<td>"'.$order['id_order'].'"</td>';
         echo '<td>"'.$order['total_paid'].'"</td>';
echo '</table></section>';
    }

Thanks you.

[TheGreatGodOm]

Hi, why ")dd)" at the end of select query in ExecuteS function? I try your sql command PS 1.7.3 and it work nice. You can not use apostrophe char ' in string which start and end with apostrophe  

'SELECT 
  year(date_add),date_format(date_add,'%M'),count(id_order), sum(total_paid) 
  FROM ps_orders 
  WHERE year(date_add) > 2018
  GROUP BY year(date_add),month(date_add) 
  ORDER BY year(date_add),month(date_add)dd)'

If you want use apostrophe you should use quotation marks string like

"SELECT 
  year(date_add),date_format(date_add,'%M'),count(id_order), sum(total_paid) 
  FROM ps_orders 
  WHERE year(date_add) > 2018
  GROUP BY year(date_add),month(date_add) 
  ORDER BY year(date_add),month(date_add)"