Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

Pulling MySQL for calculation using PHP

This is the PHP that I have so far to display firstname, lastname, birthdate and email for a MySQL database that I have. I want to know now how to make the calculation for age, I have the SQL ready (see $age_mysql) but do not know how to send it to the database to get the calculated age in return displayed in the HTML page.

Also, is there an easy way to get the dates displayed other than in MySQL format YYYY-MM-DD?


This is my program to-date:

mysql_select_db($dbname, $DBConn);
$query_mysql = "SELECT * FROM birthdays";
$mysql = mysql_query($query_mysql , $DBConn) or die(mysql_error());
$row_mysql = mysql_fetch_assoc($mysql );
$totalRows_mysql = mysql_num_rows($mysql );
$age_mysql = "(YEAR(CURDATE())-YEAR(birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(birthdate,5))";
?>

<html>
<head>
<title>Birthdays</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width=75%>
<tr>
<td width=20%>Last Name</td>
<td width=20%>First Name</td>
<td width=20%>Date Of Birth</td>
<td width=20%>Email</td>
<td width=20%>Age</td>
</tr>
<?php do { //Begin the loop to iterate through the records ?>
<tr>
<td><?php echo $row_mysql['lastname']; ?></td>
<td><?php echo $row_mysql['firstname']; ?></td>
<td><?php echo $row_mysql['birthdate']; ?></td>
<td><?php echo $row_mysql['email']; ?></td>
<td>the age calculation to be inserted here</td>
</tr>
<? } while ($row_mysql = mysql_fetch_assoc($mysql )); //End the loop ?>
</table>
</body>
<?php
mysql_free_result($mysql);
//And close the connection
mysql_close($DBConn);
?>
0
Rusty20009
Asked:
Rusty20009
  • 2
2 Solutions
 
CosminBCommented:
you could use the date_format function from mysql
or you could format it using php, using something like this:
<?
$dateString = '2004-03-21';
echo date('r', strtotime($dateString));
?>

so you birthdate line should look like this:

<td><?php echo date('f D Y', strtotime($row_mysql['birthdate'])); ?></td>

look at http://www.php.net/date for more info on date()
0
 
william_jwdCommented:
try this,

mysql_select_db($dbname, $DBConn);
$query_mysql = "SELECT lastname, firstname, birthdate, email, (YEAR(CURDATE())-YEAR(birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(birthdate,5)) as age FROM birthdays";
$mysql = mysql_query($query_mysql , $DBConn) or die(mysql_error());
$row_mysql = mysql_fetch_assoc($mysql );
$totalRows_mysql = mysql_num_rows($mysql );
?>

<html>
<head>
<title>Birthdays</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width=75%>
<tr>
<td width=20%>Last Name</td>
<td width=20%>First Name</td>
<td width=20%>Date Of Birth</td>
<td width=20%>Email</td>
<td width=20%>Age</td>
</tr>
<?php do { //Begin the loop to iterate through the records ?>
<tr>
<td><?php echo $row_mysql['lastname']; ?></td>
<td><?php echo $row_mysql['firstname']; ?></td>
<td><?php echo $row_mysql['birthdate']; ?></td>
<td><?php echo $row_mysql['email']; ?></td>
<td><?php echo $row_mysql['age']; ?></td>
</tr>
<? } while ($row_mysql = mysql_fetch_assoc($mysql )); //End the loop ?>
</table>
</body>
<?php
mysql_free_result($mysql);
//And close the connection
mysql_close($DBConn);
?>
0
 
CosminBCommented:
and if you want to find the age try this:
<?php
$dateString = '1980-03-21';
$age = date('Y', time()-strtotime($dateString))-1970;
echo $age;
?>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now