...

View Full Version : Date Output to Age Output from YYYY-MM-DD



shakyfry
11-08-2007, 09:25 PM
I have figured out how to display dates entered into my database as a users birthday but I cannot for the life me figure out how to have them translate into ages.

The current output for example on one is: 1962-07-20

This is the script I have for the template which displays the bdate in YYYY-MM-DD


{section name=i loop=$answers}

{$answers[i].bdate}

{/section}

and this is the PHP script that is calling sql for the bdate.


$sql="SELECT * from signup where account_status='Active' order by UID desc limit $startfrom, $config[items_per_page]";
$rs=$conn->execute($sql);
$users = $rs->getrows();
STemplate::assign('answers',$users);

Does anyone know how I can translate the date output to display an age instead of date of birth.

Thank you kindly.

CFMaBiSmAd
11-08-2007, 09:38 PM
You can do this in the query - http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

shakyfry
11-08-2007, 10:42 PM
Am I doing this right?


$sql="SELECT bdate, CURDATE(), (YEAR(CURDATE())-YEAR(bdate)) (RIGHT(CURDATE(),5)<RIGHT(bdate,5)) AS age FROM signup ORDER BY age;"
$conn->execute($sql);
STemplate::assign('profileage',$age);

This returns the following error...
syntax error, unexpected T_VARIABLE

I am sorry this is not my area, thanks for your help.

Inigoesdr
11-08-2007, 11:24 PM
You forgot the ; at the end of the $sql line.

shakyfry
11-09-2007, 05:35 AM
I fixed the missing; but still the age and nothing shows now. Please help as I have been sitting here all day just to get the age to show lol.


I have this in the PHP file..


MORE PHP...

elseif($category=="rf")$sql="SELECT * from signup where account_status='Active' and gender like 'Couple' order by UID desc limit $startfrom, $config[items_per_page]";

elseif($category=="rd")$sql="SELECT * from video WHERE channel like '%|$_REQUEST[chid]|%' AND type='public' $active order by rand() limit $startfrom, $config[items_per_page]";



$sql="SELECT bdate, CURDATE(), (YEAR(CURDATE())-YEAR(bdate)) (RIGHT(CURDATE(),5)<RIGHT(bdate,5)) AS age FROM signup ORDER BY age" ;


$rs=$conn->execute($sql);

$users = $rs->getrows();

$start_num=$startfrom+1;

$end_num=$startfrom+$rs->recordcount();

$page_link="";

$k=1;


MORE PHP...

and this in the html template.


{section name=i loop=$answers}.... More HTML

<dd>{$answers[i].bdate}</dd>

<dd>Location: <strong>{if $answers[i].city ne ""}{$answers[i].city},{/if} {if $answers[i].town ne ""}{$answers[i].town}{/if} </strong></dd>

<dd>Country: <strong>{$answers[i].country}</strong></dd>
</dl>

MORE HTML....
{/section}


Without the line
$sql="SELECT bdate, CURDATE(), (YEAR(CURDATE())-YEAR(bdate)) (RIGHT(CURDATE(),5)<RIGHT(bdate,5)) AS age FROM signup ORDER BY age" ;

The page loads and
$answers[i].bdate on the template shows the birthday in YYYY-MM-DD format but I want it to show the age.

shakyfry
11-09-2007, 07:54 AM
I hope I haven't been too confusing. Please help.

Thanks a million.

Fumigator
11-09-2007, 05:33 PM
Is your bdate column DATE type or is it CHAR or VARCHAR type?

aedrin
11-09-2007, 06:43 PM
and nothing shows now

Is error_reporting turned on?

shakyfry
11-09-2007, 08:00 PM
The type is a DATE Type.

Fumigator
11-09-2007, 08:10 PM
OK good... I took a look at your query and it doesn't make sense to me. If it's valid SQL then it's news to me (and I certainly could be missing something).

Here's how I arrive at age:


SELECT
year(
concat(
if (
concat(year(current_timestamp), '-', date_format(birth_dt, '%m-%d')) >= current_date,
year(current_timestamp),
year(current_timestamp + interval 1 year)
),
'-',
date_format(current_date, '%m-%d')))
- year(birth_dt) as age_nr

aedrin
11-09-2007, 08:15 PM
YEAR(CURDATE())-YEAR(bdate)

This doesn't work? Technically that should give this for birthday of 01-01-1980


2007 - 1980 = 20

This could be wrong because of the date component (for example if your birthday is 12-01-1980 and it is now 11-01-2007), but you could do this:


YEAR(CURDATE() - bdate)

That might work.

shakyfry
11-09-2007, 08:21 PM
I got that query from the link in this post http://dev.mysql.com/doc/refman/5.0/...culations.html

I am calling the sql from a table called signup that has the user info when they signup. The {section name=i loop=$answers} loops the answers at 20 users a page. If I have on the template $answers[i].city , $answers[i].bdate it will loop 20 users cities and birthdays in the section of html I have those answers in. My usual sql query that gets this table info is...


$sql="SELECT * from signup where account_status='Active' order by UID desc limit $startfrom, $config[items_per_page]";

This queries the right table. Then I have the template assigned for this sql query for the template.


$rs = $conn->Execute($sql);
$users = $rs->getrows();
STemplate::assign('answers',$users);

This works beautifully for all of the section looped answers. It feeds the output page the exact answers in the table...ie; YYYY-MM-DD for bdate and inputted city info.

Fumigator
11-09-2007, 08:31 PM
Your link is bad...

I'm not sure why you went into all that process detail-- it's not relevant.

Here's the part of your query that doesn't make sense:


SELECT bdate
, CURDATE()
, (YEAR(CURDATE())-YEAR(bdate)) (RIGHT(CURDATE(),5)<RIGHT(bdate,5)) AS age
FROM signup
ORDER BY age


It just doesn't look valid to me. The first part is fine, year - year, but then you plop a comparison in there without an "IF" or "CASE". Forgive me if it's syntax I'm not familiar with.

Aedrin the 2007 - 1980 only works if the person has already had a birthday this year, otherwise it's a year off. So on January 1st most everyone's age will be wrong. YEAR (curdate() - bdate) has the same issue.

shakyfry
11-09-2007, 08:37 PM
I went into all that process detail because I dont have the slightest idea what I am doing and I wanted to make sure that I said everything that might needed to be said. I am sorry if it was unnecessary.

So in order for it to process the bdate to age would I use your query and assign it a new template or use the same answers template.


SELECT
year(
concat(
if (
concat(year(current_timestamp), '-', date_format(birth_dt, '%m-%d')) >= current_date,
year(current_timestamp),
year(current_timestamp + interval 1 year)
),
'-',
date_format(current_date, '%m-%d')))
- year(birth_dt) as age_nr

I am sorry I am not an expert at this.

shakyfry
11-09-2007, 08:43 PM
This is a query that outputs the age but it doesn't work if I try to loop the answers. This query translates the bdate in YYYY-MM-DD to age but only for one user on one page.


$sql="select * from signup WHERE UID='$_REQUEST[UID]'";
$rs=$conn->execute($sql);
if($rs->recordcount()>0)
{
//INCREASE VIEW COUNT
$sql="update signup set profile_viewed=profile_viewed+1 WHERE UID=$_REQUEST[UID]";
$conn->execute($sql);
//END
$dat=explode("-",$rs->fields[bdate]);
$age=(date("Y")-$dat[0]);
STemplate::assign('profileage',$age);
$users = $rs->getrows();
$sql="select * from video WHERE UID=$_REQUEST[UID] $active order by VID desc limit 1";
$rs=$conn->execute($sql);
$vdo = $rs->getrows();


{$profileage}

I try to use this to loop the answer on another page and it loops 2007 as the answer.

Fumigator
11-09-2007, 08:57 PM
Aha, I found the query you tried in the MySQL docs. You are missing the "minus" sign.


SELECT bdate
, CURDATE()
, (YEAR(CURDATE())-YEAR(bdate)) - (RIGHT(CURDATE(),5) < RIGHT(bdate,5)) AS age
FROM signup
ORDER BY age


And it works by exploiting the return of a comparison being 1 or 0 (true or false), actually using that return value and subtracting either 1 or 0 from the original year - birthyear arithmetic. Clever... in my opinion it isn't good logic, but hey it works.

Anyways, your template code and everything related to it is out of my depth as I don't know how it's all put together.

shakyfry
11-09-2007, 09:00 PM
Can I ask you love, pretty please, if this works.... where do i put it in the PHP file and what template do I associate with it to loop the translated age answers. Please I'm loving you and dying over here all at the same time.

Fumigator
11-09-2007, 09:05 PM
Can I just ask, why don't you just add this SELECT query to your existing query? Just add the new "age" column to the query that is pulling out bdate?

CFMaBiSmAd
11-09-2007, 09:17 PM
The query in post #3 in this thread, with the inclusion of the missing minus sign, should return the age.

aedrin
11-09-2007, 10:34 PM
YEAR (curdate() - bdate) has the same issue.

I was basing this off of the fact that Oracle stores dates as a floating point number (1 being a day).

I'm not sure how MySQL handles that part.


CURDATE() - bdate

Would give you a number that indicates the number of years, but it would try to parse it as a date as that is the type of the field. In oracle you could just divide by 365 and then return that. But in MySQL it might be a little more difficult. If it is... then so much for SQL standards! ;)

Fumigator
11-09-2007, 10:42 PM
Hmm you are right... that should work actually. I just tried it on DB2 and it worked great. I was wrong in my thinking of what that query was doing but I do not know how MySQL will handle it. I'll have to try it tonight...



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum