year only!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

year only!

Post by sirTemplar »

i have an sql querry that tells the database to fetch from the field NATDAT which contains the bdays, all who have their bdays today (current day). my querry is like this:
$result = mysql_query ("SELECT * FROM mytable

WHERE substring(NATDAT,6) = substring(curdate(),6)

ORDER BY CNOMEN ASC, NOMRL
",$conn);
then i echo it this way:
echo "<table bgcolor=#FFFEEF border=0 cellpadding=1 cellspacing=0 style=border-collapse: collapse bordercolor=#111111 width=100%>";
echo "<font face=Verdana size=1 color=#800000><li>{$row['NOMRL']} </b> {$row['CNOMEN']}</font></li></table>";
so it prints NOMRL which is the name and CNOMEN which is the surname. what i want to add after the CNOMEN is the year from the NATDAT field which contains the bday field in this format eg. 1972-01-21. i tried using substr in several ways but no luck! any help?

the result now looks like this: eg.

Paul Hayes
Robert Andrews
etc...

i want it to be:e.g.

Paul Hayes - 1972
Robert Andrews - 1967

or

Paul Hayes - '72
Robert Andrews - '67
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You could alter your SQL query to look something like this:

Code: Select all

SELECT NOMRL, CNOMEN, YEAR(NATDAT) AS NATYEAR FROM mytable WHERE substring(NATDAT,6) = substring(curdate(),6) ORDER BY CNOMEN ASC, NOMRL
Then you would have NOMRL and CNOMEN as before but would also have a NATYEAR element in the $row array which contained just the year data from the NATDAT field.

Check out this link for more info on YEAR():
http://www.mysql.com/doc/en/Date_and_ti ... ml#IDX1267

Mac
sirTemplar
Forum Commoner
Posts: 65
Joined: Wed Dec 18, 2002 1:57 am

thanx

Post by sirTemplar »

Thanks! That one works great! :D
Post Reply