I have a table profiles. This table has afield d.o.b. (date of birth). I don’t have a field age (since I don’t have an idea how to update that field yearly), but I still would like to be able to search on age. Is this possible within a query or should I use my server side scripting to do so?
Thank you in advance
Personally, I would use server-side scripting to calculate the age in real time. OR, if you are running something that can do scheduled tasks (like ColdFusion), you can create a script that will run every night just after midnight, find all dob’s of that morning, and update an age column in the database.
Just my $0.03472 worth (inflation, don’tchaknow).
V/r,
Hi WolfShade thank you for the reply. Okay fair enough. Funny that you mention Coldfusio. That is indeed the program that I use most
Nice to meet another CF guy! There are fewer and fewer of us, all the time.
I started learning in 2000 (CF Server 4.5). I love CF way more than PHP, or Classic ASP.
I can not more then aggree with you. I am using CF now since 1998. Unfortunately there are a lot of situations that I am forced to use PHP!
I decided to still do it in mySql and found the following sollution doing the trick:
SELECT *,
EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`))))+0
FROM
profiles
WHERE
EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`))))+0 >= <cfqueryparam value="FORM.age_from">
That is basically working fine. The only problem I have is that when I add the date_to in the where clause i.e.
AND EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`))))+0 <= <cfqueryparam value="FORM.age_to">
I don’t get any data returned. What should I adjust to make this work? Thank you in advance :tup:
coldfusion rocks big time, been using it on my personal web sites since 1997
SELECT * -- perfectly okay in an outer query
FROM ( SELECT something
, anything
, just_not_the_dreaded_evil_select_star
, EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age
FROM profiles ) AS inner
WHERE age BETWEEN <cfqueryparam value="FORM.age_from">
AND <cfqueryparam value="FORM.age_to">
Hi Rudy. Thank you for the reply. CF indeed rocks.
I get an error using the above query:
<cfset FORM.age_from = "20">
<cfset FORM.FORM.age_to = "40">
<cfquery name="getProfiles" datasource="Db_dating">
SELECT *
FROM ( SELECT naam
, EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age
FROM profielen ) AS inner
WHERE age BETWEEN <cfqueryparam value="FORM.age_from">
AND <cfqueryparam value="FORM.age_to">
</cfquery>
as you can see on this test page
whoops, INNER is a reserved word (i should’ve known that)
use some other table alias
Hi Rudy. Thanks again for the reply. I have changed that and ideed don’t get an error any longer, but I also don’t get any rows returned. As soon as I have:
WHERE age >= <cfqueryparam value=“FORM.age_from”> It is working but somehow using BETWEEN it doesn’t give me any results? What can be wrong. Could it be the dob field, which is currently timestamp
run the query without the WHERE clause, and inspect everybody’s age
this should confirm that the sql is okay
maybe there’s something wrong with FORM.age_to
Hi Rudy, without the where clause it is giving me the right results! What could be wrong with the age_to?
Should I be using date for the dob field or is timestamp okay?
Edit: I saw this mistake (<cfset FORM.FORM.age_to = “40”>) I have changed it but still nothing
timestamp is somewhat inappropriate (i mean, how many birthdates actually include the time???) but it actually works fine
if you put the WHERE clause with BETWEEN back into the query and hardcode the from and to ages (instead of using cf params) you will see there’s nothing wrong with the sql
Hi Rudy. Yes you are right. When I hard code those two it shows me the appropriate output. But I should be able to do it with form values as well! Or is it because I have used cfset on the test page?
Edit: got it working using cfparam instead of cfset and oops I had forgotten something very important Form.age_to should have been #Form.age_to# ofcource. Again thank you for all your input :tup:
Hi Rudy. I have another question about this. As you probably have noticed was the above just a test. The real query exists of many more fields and includes a few INNER JOINS like this:
SELECT P.profiel_id,
P.gebruikersnaam,
P.dob,
P.woonplaats,
PR.provincie,
G.geslacht
FROM
profielen P
INNER
JOIN provincies PR ON P.provincie_id = PR.provincie_id
INNER
JOIN geslacht G ON P.geslacht_id = G.geslacht_id
How should the query with sub query look like, In other words where should I include the INNER JOINS in the exampla as before and below:
SELECT *
FROM ( SELECT P.profiel_id,
P.gebruikersnaam,
P.dob,
P.woonplaats,
PR.provincie,
G.geslacht,
EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age
FROM profielen ) AS sub
Thank you in advance!
I found it after some playing arround
SELECT *
FROM ( SELECT P.profiel_id,
P.gebruikersnaam,
P.dob,
P.woonplaats,
P.geslacht_id,
P.isActive,
PR.provincie,
G.geslacht,
EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age
FROM
profielen P
INNER JOIN provincies PR ON P.provincie_id = PR.provincie_id
INNER JOIN geslacht G ON P.geslacht_id = G.geslacht_id
) AS sub
yeah, that’s fine
except you don’t really need the outer SELECT * FROM (…) AS sub unless you also have the WHERE clause referencing the age column
Hi Rudy. That is indeed the case. Thank you agin for the input