Display birthday with dd-mm only from mysql date (dd-mm-yyyy)

Databases
1

I have setup a church membership application where I am capturing member information. On the date of birth, even though I am capturing dd-mm-yyyy I want the display to only retrieve dd-mm from mysql database. How do I accomplish this? I am using …echo $row[date].

2

MySQL stores the date as yyyy-mm-dd

Use php to explode() the string at the - points and then select the two parts you want to use, and echo them.

http://php.net/manual/en/function.explode.php

3

Or you can do it in MySQL with DATE_FORMAT

SELECT DATE FORMAT (dat_date_field, '%d %M') AS birthday
4

Here is the code I am using to execute this.

$sql = "select * from membership WHERE dob != 0 order by dob ASC";
$query = mysql_query( $sql );
 
echo "<table width='100%' border='1' cellpadding='3' cellspacing='0'>
<tr bgcolor='#DBEAF9' border:'1px solid #ccc'>

<th>First Name</th>
<th>Middle Name</th>
<th>Last Name</th>
<th>Date of Birth</th>

</tr>";
 
while( $row = mysql_fetch_assoc($query) )
{
echo "<tr><td>$row[fname]</td><td>$row[mname]</td><td>$row[lname]</td><td>$row[dob]</td></tr>";
}
echo "</table>";
 
?>
5

@tonyck;

A change to your query:

SELECT
      fname
    , mname
    , lname
    , DATE_FORMAT(dob, '%d %m) AS birthday
FROM
    membership
WHERE
    dob <> 0
ORDER BY
    dob ASC

When dealing with the result set from that the dob field of the result set will be “birthday” and not “dob”

Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.

Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.

6

Thanks Spacephoenix this works. Now trying to figure out how to sort by date month. Since the mysql database has the year in it. The display is still using the year to sort, hence when it displays dd mm it will have the same month apart cause its considering the year as well on the database. e.g 12 Nov, 13 Jan, Dec 10 Nov 5…

7

Change the ORDER BY clause to:

ORDER BY     birthday ASC
8

Thanks it worked.

9

yeah, but how useful is that?

you’re going to get them in day order, regardless of month

i would’ve thought month-day order would be more useful

10

Actually I had to switch to month day cause when I did day month. It was sorting by day and hence months were scattered. Thanks again