[PHP-DB] Calculated field in MySQL
Hello, I got the following error message from MySQL: --- Query failed. MySQL said: Unknown column 'AGE' in 'where clause' query = SELECT *, (Year(Now()) - Year(BDAY)) + ((DAYOFYEAR(Now())-DAYOFYEAR(BDAY))/365) as AGE FROM Katalog WHERE AGE >= 31 AND AGE <= 50 AND IS_MEM_EXP = 0 AND IS_OPEN = 1 ORDER BY INPUT_DATE DESC --- Now, obviously the column should exist, since I am creating it in the query, but I do not seem to be able to find the bug. If I would have used Access, I would have created a query inside the database from my table and SELECT from that, but since I am not I have to find other ways. What I would like to do the most, would be to create a field in the table, which would calculate the field inside the database, but it does not seem to be possible, though it would save me a lot of trouble every time I need that column in my application. Could someone either tell me whats wrong with my query or tell me what I need to do to make such a calculated field. Much appreciated. Morten Twellmann -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Calculated field in MySQL
Morten Twellmann wrote: Hello, I got the following error message from MySQL: --- Query failed. MySQL said: Unknown column 'AGE' in 'where clause' query = SELECT *, (Year(Now()) - Year(BDAY)) + ((DAYOFYEAR(Now())-DAYOFYEAR(BDAY))/365) as AGE FROM Katalog WHERE AGE >= 31 AND AGE <= 50 AND IS_MEM_EXP = 0 AND IS_OPEN = 1 ORDER BY INPUT_DATE DESC --- Now, obviously the column should exist, since I am creating it in the query, but I do not seem to be able to find the bug. If I would have used Access, I would have created a query inside the database from my table and SELECT from that, but since I am not I have to find other ways. What I would like to do the most, would be to create a field in the table, which would calculate the field inside the database, but it does not seem to be possible, though it would save me a lot of trouble every time I need that column in my application. Could someone either tell me whats wrong with my query or tell me what I need to do to make such a calculated field. Use HAVING for Age, rather than WHERE http://www.mysql.com/doc/en/SELECT.html (search for HAVING) -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals – www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Calculated field in MySQL
one thinglooks like you need one more outer set of paren's to tie both
sets of the equation together.
query = SELECT *, ((Year(Now()) - Year(BDAY)) + ((DAYOFYEAR(Now())
-DAYOFYEAR(BDAY))/365)) as AGE FROM Katalog WHERE AGE >= 31 AND AGE <= 50
AND IS_MEM_EXP = 0 AND IS_OPEN = 1 ORDER BY INPUT_DATE DESC
hth
jd
"Morten
Twellmann" To: [EMAIL PROTECTED]
<[EMAIL PROTECTED]> cc:
Subject: [PHP-DB] Calculated field in
MySQL
08/10/2003 01:40
PM
Hello,
I got the following error message from MySQL:
---
Query failed.
MySQL said:
Unknown column 'AGE' in 'where clause'
query = SELECT *, (Year(Now()) - Year(BDAY)) +
((DAYOFYEAR(Now())-DAYOFYEAR(BDAY))/365) as AGE FROM Katalog WHERE AGE >=
31
AND AGE <= 50 AND IS_MEM_EXP = 0 AND IS_OPEN = 1 ORDER BY INPUT_DATE DESC
---
Now, obviously the column should exist, since I am creating it in the
query,
but I do not seem to be able to find the bug.
If I would have used Access, I would have created a query inside the
database from my table and SELECT from that, but since I am not I have to
find other ways.
What I would like to do the most, would be to create a field in the table,
which would calculate the field inside the database, but it does not seem
to
be possible, though it would save me a lot of trouble every time I need
that
column in my application.
Could someone either tell me whats wrong with my query or tell me what I
need to do to make such a calculated field.
Much appreciated.
Morten Twellmann
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
