Extract the number of invoices of the current year in mysql

Question

Goodmorning everyone. I'm going crazy I need to extract the number of invoices from the current year per customer.
in my table tbl_preventivi I have the field anagrafica_id for the customer and date_prev for the date of the invoice.
this is my code.

$anno = date('Y');    
SELECT tbl_preventivi.anagrafica_id, Count(tbl_preventivi.preventivo_id) AS totale
    FROM tbl_preventivi
    GROUP BY tbl_preventivi.anagrafica_id, Year(tbl_preventivi.data_prev)
    HAVING ((Year(tbl_preventivi.data_prev) = ".$anno.") AND (tbl_preventivi.anagrafica_id=".$_GET['anagrafica_id']."))

i am sure that in the test i am doing the result must be 1, instead the query is null. if I remove (Year(tbl_preventivi.data_prev) = ".$anno.") AND the query works and returns 6 (which is the number of invoices made even in previous years). where am i wrong?

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Apr 30 '21 at 15:13
Welcome to SO. For the preservation of your sanity, and ours, please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Apr 30 '21 at 15:22

score 0 · Answer 1 · answered May 02 '21 at 05:27

The simplest solution would be something like:

SELECT p.`anagrafica_id`,
       COUNT(p.`preventivo_id`) as `totale`
  FROM `tbl_preventivi` p
 WHERE p.`anagrafica_id` = 12345 and YEAR(p.`data_prev`) = 9999
 GROUP BY p.`anagrafica_id`;

Note: 12345 should be replaced with a sanitized customer number, and 9999 should be replaced with a sanitized and logical year. Prepared statements are your friend here.

If you wish to see all invoices for a customer across all years, you can do this:

SELECT p.`anagrafica_id`,
       YEAR(p.`data_prev`) as `year`,
       COUNT(p.`preventivo_id`) as `totale`
  FROM `tbl_preventivi` p
 WHERE p.`anagrafica_id` = 12345
 GROUP BY p.`anagrafica_id`, `year`
 ORDER BY `year` DESC;

Again, be sure to sanitize your inputs. In the famous words of Fox Mulder: Trust no one.

thank you very much i'm learning some php on my own ... you gave me a great hand (especially to sanitize my inputs) — Mr.Fifteen, May 03 '21 at 08:36

Extract the number of invoices of the current year in mysql

1 Answers1