MySQL - Variable in WHERE clause result from SELECT statement

Question

I have this query:

INSERT INTO tab_reminder (ID_cliente, ID_articolo, Data_acquisto) 
        SELECT c.ID_cliente as cliente, ca.ID_articolo as articolo, MAX(c.Data_contratto) as ultimoAcquisto
        FROM tab_contratti_articoli ca
        LEFT JOIN tab_contratti c ON c.ID_contratto = ca.ID_contratto
        LEFT JOIN tab_articoli a ON ca.ID_articolo = a.ID_articolo
        LEFT JOIN tab_clienti cl ON c.ID_cliente = cl.ID_cliente
        WHERE c.ID_cliente != 0 
        AND cl.ID_utente = 0 
        AND Giorni_reminder != 0 
        AND c.Data_contratto <= DATE_SUB('2017-05-16', INTERVAL Giorni_reminder DAY) 
        AND c.Data_contratto > DATE_SUB(DATE_SUB('2017-05-16', INTERVAL Giorni_reminder DAY), INTERVAL 60 DAY)
        AND (c.Data_contratto NOT BETWEEN DATE_SUB('2017-05-16', INTERVAL Giorni_reminder DAY) AND '2017-05-16')
        GROUP BY c.ID_cliente, ca.ID_articolo
ON DUPLICATE KEY UPDATE ID_cliente=ID_cliente, ID_articolo=ID_articolo, Data_acquisto=Data_acquisto, Articolo_visibile=Articolo_visibile;

Basically, it annoys me that I have to repeat DATE_SUB(DATE_SUB('2017-05-16', INTERVAL Giorni_reminder DAY) date result.

Is there a way to declare a variable in MySQL for that date?

Something like:

SET @newDate := DATE_SUB(DATE_SUB('2017-05-16', INTERVAL Giorni_reminder DAY);

And then use it like this:

AND c.Data_contratto <= @newDate 
AND c.Data_contratto > DATE_SUB(@newDate, INTERVAL 60 DAY)
AND (c.Data_contratto NOT BETWEEN @newDate AND '2017-05-16')

I have searched and for what I have found, there is no way or it's not allowed to declare variables or aliases in the WHERE clause, but maybe there is a workaround to avoid typing the same thing several times in the same query.

As always, thank you all and have a nice day!

Update 1:

I have seen @TimBiegeleisen's link and I find it very, very interesting. But the project I work in uses deprecated mysql API to query. See example:

$sqlExample = "SELECT * FROM tab_example";
$qExample = mysql_query($sqlExample) or die ("Error in $sqlExample: ".mysql_error());
$rsExample = mysql_fetch_object($qExample);

Taking this into consideration, are still Prepared statements viable?

Returning to OP, Is it a way of declaring the variable in the query where I need it (In WHERE clause)?

Update 2:

This is a clarification of why this: How to declare a variable in MySQL?

Is not working for me or I am not managing to make it work.

I don't have to declare static data, I would like to declare a variable that is result of a SELECT.

The data I want to store and keep using within the WHERE clause is this DATE_SUB('2017-05-16', INTERVAL Giorni_reminder DAY), I removed the PHP variable because I recall can create confusion.

Is just that Giorni_reminder is a result of the SELECT and I want to use it in WHERE clause. If you see the query, I am repeating the same thing several times.

I wanted to avoid that.

So, something like this doesn't work for me:

SET @start = 1, @finish = 10;

I need something like posted in OP. If it's possible.

If it's not possible, then I have learned a lesson! :D

Have you heard of using [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) ? — Tim Biegeleisen, May 16 '17 at 09:09
@TimBiegeleisen No, looking into it right now. Thanks for the link. But we (in company I work in) are not using msqli at the moment. — SrAxi, May 16 '17 at 09:10
Interesting...then which API are you using to query on MySQL? — Tim Biegeleisen, May 16 '17 at 09:11
@TimBiegeleisen Old and deprecated mysql.. :( I'm looking into the prepared statements, I'm liking them a lot! Will see if I could implement them in the current system we have. I am not a native BE developer, so I'm sloppy with SQL and Databases in general, like you can see from the query I posted... — SrAxi, May 16 '17 at 09:14
@TimBiegeleisen I have updated my Question, you can see what API I use on the project. — SrAxi, May 16 '17 at 09:25
I'm not going to post an answer, because your query is huge, but look at the following link to see how to parameterize a query with your API: http://php.net/manual/en/function.mysql-query.php — Tim Biegeleisen, May 16 '17 at 09:28
Yes this is possible. See http://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql. — Chris Stryczynski, May 16 '17 at 09:29
Possible duplicate of [How to declare a variable in MySQL?](http://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql) — nyedidikeke, May 16 '17 at 09:34
Kind friends, I have updated my OP with Why http://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql is not working for me or my incompetence in MySQL doesn't allow me to make it work. — SrAxi, May 16 '17 at 09:48

score 0 · Accepted Answer · edited Jun 17 '17 at 15:01

Editted-2
Please remove/update the definer below to match your database.

  CREATE DEFINER=`Connect7827`@`%` PROCEDURE `new_procedure`(iN UserIput varchar(1000))
    BEGIN

     declare vNewDate_Value varchar(100);
     SET vNewDate_Value=concat("","DATE_SUB(DATE_SUB('2017-05-16', INTERVAL Giorni_reminder DAY)");

    Set @vquery=concat_ws
                ("","INSERT INTO tab_reminder (ID_cliente, ID_articolo, Data_acquisto) 
                SELECT c.ID_cliente as cliente, ca.ID_articolo as articolo, MAX(c.Data_contratto) as ultimoAcquisto
                FROM tab_contratti_articoli ca
                LEFT JOIN tab_contratti c ON c.ID_contratto = ca.ID_contratto
                LEFT JOIN tab_articoli a ON ca.ID_articolo = a.ID_articolo
                LEFT JOIN tab_clienti cl ON c.ID_cliente = cl.ID_cliente
                WHERE c.ID_cliente != 0 
                AND cl.ID_utente = 0 
                AND Giorni_reminder != 0 
                AND c.Data_contratto <=  vnewDate 
                AND c.Data_contratto > DATE_SUB(vNewDate, INTERVAL 60 DAY)
                AND (c.Data_contratto NOT BETWEEN vNewDate AND '$oggi')
                GROUP BY c.ID_cliente, ca.ID_articolo
                ON DUPLICATE KEY UPDATE ID_cliente=ID_cliente, ID_articolo=ID_articolo, Data_acquisto=Data_acquisto, Articolo_visibile=Articolo_visibile;");
                Set @vquery= replace(@vquery,'$oggi','2017-05-16');
                Set @vquery= replace(@vquery,'vNewDate',vNewDate_Value);
                Select @vquery;

                PREPARE stmt FROM @vquery;
                EXECUTE stmt;

    END

Thanks for your answer. The thing is that I wanted to simplify the query and try to avoid repeating that generated date. In your answer, the query complicates itself and the statement that I wanted to avoid to repeat is being repeated the same amount of times... Maybe I did not explain mayself well, it happens often... :S — SrAxi, May 16 '17 at 10:05
@SrAxi let me know if you need any explanation, Definer means schema. — Rohit Kumar, May 16 '17 at 10:09
i edited my OP removing the `$oggi`. That is not the important part. The thing is that I want to get a result of my select, declare it in a variable and use it in the WHERE clause. — SrAxi, May 16 '17 at 10:11
Please check the result of the Select @vquery; Hope it works this time. — Rohit Kumar, May 16 '17 at 10:25

MySQL - Variable in WHERE clause result from SELECT statement

1 Answers1