Solved

Pulling MySQL for calculation using PHP

Posted on 2004-03-22
3
432 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

777 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