jay jay - 2 months ago 8
MySQL Question

PHP MySQL calculation AS not working

I tried searching for a similar case here in SO but I can't find the right search result. My problem is this:

I have this query which works fine and displays a lot of results (

net_salary
is the result of subtraction between
salary
and
totaldeductions
fields which are both stored as float. I can't remember why but I needed to store them as float):

SELECT name, address, city, state, zip_code, salary-totaldeductions as net_salary FROM employees WHERE state = 'CA' ORDER BY address ASC


If I try to modify it and add
AND net_salary > 5000
the page/frame becomes blank:

SELECT name, address, city, state, zip_code, salary-totaldeductions as net_salary FROM employees WHERE state = 'CA' AND net_salary > 5000 ORDER BY address ASC


I tried searching where the code fails, it seems
if (mysqli_stmt_prepare($stmt, $MyQuery))
fails. The first query works and
net_salary
works but on the second query where I tried using the variable
net_salary
in a condition it fails, is it wrong to use the variable that was created in a query as a condition in a query? Can you spot what was wrong?

Answer

To get the result try below query :

SELECT name, address, city, state, zip_code, (salary - totaldeductions) as net_salary FROM employees WHERE state = 'CA' AND (salary - totaldeductions) > 5000 ORDER BY address ASC

Here you need to put same condition in where conditions. If you want to use alias into where than it will not work but Having is used but Having is used with aggregate column.

Try this query.