Proper usage of MySQL variables

Question

I have a big MySQL query which is formed with PHP.
I don't like that this big IF statement repeats 4 times in a query (saved in $endTrialDate variable).

And I want to make it shorter and nicer, just using MySQL variables.

My Query (EDITED AND SIMPLIFIED):

$endTrialDateCondition = "IF(ec.extend_trial_type LIKE 'week',
        ADDDATE(ec.start_contract, (7 * ec.extend_trial_time)),
          IF(ec.extend_trial_type LIKE 'day',
            ADDDATE(ec.start_contract, ec.extend_trial_time),
            ADDDATE(ec.start_contract, INTERVAL ec.extend_trial_time MONTH)
          )
        )";

$endTrialDate = "(SUBDATE((".$endTrialDateCondition."), 1))";

$finalQuery = "SELECT e.firstname_employe, e.lastname_employe,
  ".$endTrialDate." as end_trial_date
    FROM employe_contract ec
    LEFT JOIN employe e ON ec.id_employe = e.id_employe
    WHERE ".$endTrialDate." >= ?
    AND ".$endTrialDate." <= ?
    ORDER BY ".$endTrialDate.", e.lastname_employe, e.firstname_employe";

When I tried to modify my query like: (using variable @end_trial_date)

$finalQuery = "SELECT e.firstname_employe, e.lastname_employe,
  @end_trial_date := ".$endTrialDate." as end_trial_date
    FROM employe_contract ec
    LEFT JOIN employe e ON ec.id_employe = e.id_employe
    WHERE @end_trial_date >= ?
    AND @end_trial_date <= ?
    ORDER BY @end_trial_date, e.lastname_employe, e.firstname_employe";

end_trial_date in results was NULL, so nothing was found.

How to make this query shorter?

This is a complex query and we have no idea how your database is structured or what the point of the query is. I think you either have to give us more details, or you won't get an (good) answer. — KIKO Software
– KIKO Software, Commented Aug 28, 2017 at 11:33
@KIKOSoftware I think I can simplify that. The main problem that I have. When I assign IF STATEMENT to variable, it shows me NULL in results. I thought it was like not assigned in moment of comparsion. — WinK-
– WinK-, Commented Aug 28, 2017 at 11:38
I'm not sure, but I think variables in MySQL are global, and you cannot use them inside a SELECT query on a row by row basis as you are. Or, to put it in other terms: the ORDER BY is performed after all the rows have been selected, so the variable there won't have the content you expect. — KIKO Software
– KIKO Software, Commented Aug 28, 2017 at 11:46

KIKO Software · Accepted Answer · 2017-08-28 11:48:41Z

1

Could you try this:

$finalQuery = "SELECT e.firstname_employe, e.lastname_employe,
 ".$endTrialDate." as end_trial_date
    FROM employe_contract ec
    LEFT JOIN employe e ON ec.id_employe = e.id_employe
    WHERE end_trial_date >= ?
    AND end_trial_date <= ?
    ORDER BY end_trial_date, e.lastname_employe, e.firstname_employe";

I'm not sure this will execute, but it is better than what you had. Basically the as gives you your variable that works row by row.

answered Aug 28, 2017 at 11:48

KIKO Software

17.5k3 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

WinK- Over a year ago

Thanks, but that doesn't work. Unknown column 'end_trial_date' in 'where clause'

KIKO Software Over a year ago

Yes, I was afraid of something like that. The expressions in the select are only evaluated after the where's have been done.

WinK- Over a year ago

I've also tried to set variable in where clause. But it was also not working as expected. Not NULL, but just empty row. (1 found but with totally empty fields)

KIKO Software Over a year ago

Yes, a query runs over the data several times, first to do the WHERE then to do the expressions on the selected rows, and finally to order them. global variables can therefore not be used. Another solution could be that you store the end_trial_date in the table, or in a temporary table, and then use it from there.

WinK- Over a year ago

That's interesting. Probably will leave that task for some better moment, thx for your time

Collectives™ on Stack Overflow

Proper usage of MySQL variables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related