By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,949 Members | 1,824 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,949 IT Pros & Developers. It's quick & easy.

Date Formatting mysql->php

Tormod
P: 8
I'm having problems formatting a date retrieved from mysql database. When I retrieve the date it's in the format YYYY-MM-DD. I need to split the Day, Month and Year portions of the date up so that I can represent them in a drop down menu.

I've posted my source code below, such as it is. I need a function of some kind to allow me to manipulate the $row["DOB"].

Expand|Select|Wrap|Line Numbers
  1.     #connect to mysql
  2.     $conn = @mysql_connect( "host", "username", "password" )
  3.                         or die( "Err:Conn" );
  4.  
  5.     #select specified database
  6.     $rs = @mysql_select_db( "database", $conn )
  7.                         or die( "Err:Db" );
  8.  
  9.     #create the query
  10.     $sql="select playerid, firstname, surname, DOB from player order by surname";
  11.  
  12.     #execute the query
  13.     $rs = mysql_query( $sql,$conn );
  14.  
  15.     #write the data
  16.     echo" <table width=\"270\" border=\"0\">" ;
  17.  
  18.     while( $row = mysql_fetch_array( $rs ) )
  19.     {
  20.               echo" <tr> ";
  21.                 echo "<td> <a href=\"editplayer.php?playerid=$row[playerid]\">".$row["surname"].", ".$row["firstname"]."</a></td>";
  22.                 echo"<td>".$row["DOB"]."</td>";
  23.             echo" </tr> ";
  24.     }
  25.               echo" </table>";
  26.  
  27.  
Feb 25 '07 #1
Share this Question
Share on Google+
9 Replies


P: 99
I'm having problems formatting a date retrieved from mysql database. When I retrieve the date it's in the format YYYY-MM-DD. I need to split the Day, Month and Year portions of the date up so that I can represent them in a drop down menu.

I've posted my source code below, such as it is. I need a function of some kind to allow me to manipulate the $row["DOB"].

Expand|Select|Wrap|Line Numbers
  1.     #connect to mysql
  2.     $conn = @mysql_connect( "host", "username", "password" )
  3.                         or die( "Err:Conn" );
  4.  
  5.     #select specified database
  6.     $rs = @mysql_select_db( "database", $conn )
  7.                         or die( "Err:Db" );
  8.  
  9.     #create the query
  10.     $sql="select playerid, firstname, surname, DOB from player order by surname";
  11.  
  12.     #execute the query
  13.     $rs = mysql_query( $sql,$conn );
  14.  
  15.     #write the data
  16.     echo" <table width=\"270\" border=\"0\">" ;
  17.  
  18.     while( $row = mysql_fetch_array( $rs ) )
  19.     {
  20.               echo" <tr> ";
  21.                 echo "<td> <a href=\"editplayer.php?playerid=$row[playerid]\">".$row["surname"].", ".$row["firstname"]."</a></td>";
  22.                 echo"<td>".$row["DOB"]."</td>";
  23.             echo" </tr> ";
  24.     }
  25.               echo" </table>";
  26.  
  27.  
use the substr function

[PHP]
$date = $row['DOB'];
$year = substr($date,0,4);
$month = substr($date,5,2);
$day = substr($date,8);
[/PHP]

substr(string you want to cut up,start position, limit characters)
Feb 25 '07 #2

Tormod
P: 8
Thanks mate. That's fixed it.

I had tried using the DATE_FORMAT function and I thought I was getting pretty close at one stage but to no avail. If anyone can point out where I'm going wrong I'd be much obliged.

Heres what I'm trying to get working...

Expand|Select|Wrap|Line Numbers
  1. $sql="SELECT playerid, firstname, lastname, DATE_FORMAT(DOB, '%d-%m-%Y') FROM player order by surname";
Feb 26 '07 #3

P: 99
have you tried

Expand|Select|Wrap|Line Numbers
  1. $sql="SELECT playerid, firstname, lastname, DATE_FORMAT(DOB, '%d-%m-%Y') as date FROM player order by surname";
and get the alias (date) instead of the DOB field
Feb 26 '07 #4

Tormod
P: 8
I tried it but it's just not having any of it. The page doesn't crash but the table isn't displayed at all. I think it's something in the syntax of the query as I've tried commenting bits of the code out and that line appears to be the stumbling block.

Here's the code I'm trying...


Expand|Select|Wrap|Line Numbers
  1. #list all players
  2.     #connect to mysql
  3.     $conn=@mysql_connect( "host", "username", "password" ) or die( "Err:Conn" );
  4.  
  5.     #select specified database
  6.     $rs=@mysql_select_db( "database", $conn ) or die( "Err:Db" );
  7.  
  8.     #create the query
  9.     $sql="SELECT playerid, firstname, lastname, DATE_FORMAT(DOB, '%d-%m-%Y') as date FROM player order by surname";
  10.  
  11.     #execute the query
  12.     $rs = mysql_query( $sql,$conn );
  13.  
  14.     #open the table to write the data to
  15.     echo" <table width=\"270\" border=\"0\">" ;
  16.  
  17.     #create a loop
  18.     while( $row = mysql_fetch_array( $rs ) )
  19.         {
  20.               echo"<tr>";
  21.                 echo"<td><a href=\"editplayer.php?playerid=$row[playerid]\">".$row["surname"].", ".$row["firstname"]."</a></td>";
  22.                 echo"<td><a href=\"editplayer.php?playerid=$row[playerid]\">".$row["DOB"]."</a></td>";
  23.             echo"</tr>";            
  24.         }
  25.             echo" </table>";
  26.  
Feb 28 '07 #5

P: 99
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.     #open the table to write the data to
  4.     echo" <table width=\"270\" border=\"0\">" ;
  5.  
  6.     #create a loop
  7.     while( $row = mysql_fetch_array( $rs ) )
  8.         {
  9.  
  10.                 echo"<td><a href=\"editplayer.php?playerid=$row[playerid]\">".$row["DOB"]."</a></td>";
  11.             echo"</tr>";            
  12.         }
  13.             echo" </table>";
  14.  
you have to replace DOB by date because the query returns the alias and not the fieldname, you could add the fieldname to the selected columns but i think that is not what you are looking for.
Feb 28 '07 #6

Tormod
P: 8
Tried it. Still not working. It's not even giving me the firstname & lastname column in my table which suggests to me that the query line is causing the script to bomb somehow.

I appreciate all your efforts on this by the way.
Feb 28 '07 #7

P: 99
Tried it. Still not working. It's not even giving me the firstname & lastname column in my table which suggests to me that the query line is causing the script to bomb somehow.

I appreciate all your efforts on this by the way.
Try it without the mysql function. Sometimes they don't work play nice with the mysql_query function.

If that is the problem you have to reformat the DOB field in php.
Feb 28 '07 #8

Tormod
P: 8
What, you mean without the DATE_FORMAT function embedded in the query? It definitely works like that.
Feb 28 '07 #9

P: 99
What, you mean without the DATE_FORMAT function embedded in the query? It definitely works like that.
Then i think there is no other solution then to format your date in php.
Feb 28 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.