Solved

Pulling MySQL for calculation using PHP

Posted on 2004-03-22
3
429 Views
Last Modified: 2013-12-12
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
Comment
Question by:Rusty20009
  • 2
3 Comments
 
LVL 6

Assisted Solution

by:CosminB
CosminB earned 25 total points
ID: 10648610
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
 
LVL 8

Accepted Solution

by:
william_jwd earned 75 total points
ID: 10648727
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
 
LVL 6

Expert Comment

by:CosminB
ID: 10648902
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now