Date Formatting mysql->php

[Tormod]

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"].

	#connect to mysql
	$conn = @mysql_connect( "host", "username", "password" )
						or die( "Err:Conn" );
	
	#select specified database
	$rs = @mysql_select_db( "database", $conn )
						or die( "Err:Db" );
						
	#create the query
	$sql="select playerid, firstname, surname, DOB from player order by surname";
	
	#execute the query
	$rs = mysql_query( $sql,$conn );
	
	#write the data
	echo" <table width=\"270\" border=\"0\">" ;
	
	while( $row = mysql_fetch_array( $rs ) )
	{
  			echo" <tr> ";
				echo "<td> <a href=\"editplayer.php?playerid=$row[playerid]\">".$row["surname"].", ".$row["firstname"]."</a></td>";
				echo"<td>".$row["DOB"]."</td>";
			echo" </tr> ";
	}
	  		echo" </table>";

[xwero]

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"].

	#connect to mysql
	$conn = @mysql_connect( "host", "username", "password" )
						or die( "Err:Conn" );
	
	#select specified database
	$rs = @mysql_select_db( "database", $conn )
						or die( "Err:Db" );
						
	#create the query
	$sql="select playerid, firstname, surname, DOB from player order by surname";
	
	#execute the query
	$rs = mysql_query( $sql,$conn );
	
	#write the data
	echo" <table width=\"270\" border=\"0\">" ;
	
	while( $row = mysql_fetch_array( $rs ) )
	{
  			echo" <tr> ";
				echo "<td> <a href=\"editplayer.php?playerid=$row[playerid]\">".$row["surname"].", ".$row["firstname"]."</a></td>";
				echo"<td>".$row["DOB"]."</td>";
			echo" </tr> ";
	}
	  		echo" </table>";

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)

[Tormod]

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...

$sql="SELECT playerid, firstname, lastname, DATE_FORMAT(DOB, '%d-%m-%Y') FROM player order by surname";

[xwero]

have you tried

$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

[Tormod]

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...

#list all players
	#connect to mysql
	$conn=@mysql_connect( "host", "username", "password" ) or die( "Err:Conn" );
	
	#select specified database
	$rs=@mysql_select_db( "database", $conn ) or die( "Err:Db" );
						
	#create the query
	$sql="SELECT playerid, firstname, lastname, DATE_FORMAT(DOB, '%d-%m-%Y') as date FROM player order by surname";
	
	#execute the query
	$rs = mysql_query( $sql,$conn );
	
	#open the table to write the data to
	echo" <table width=\"270\" border=\"0\">" ;
	
	#create a loop
	while( $row = mysql_fetch_array( $rs ) )
		{
  			echo"<tr>";
				echo"<td><a href=\"editplayer.php?playerid=$row[playerid]\">".$row["surname"].", ".$row["firstname"]."</a></td>";
				echo"<td><a href=\"editplayer.php?playerid=$row[playerid]\">".$row["DOB"]."</a></td>";
			echo"</tr>";			
		}
			echo" </table>";

[xwero]

	
	#open the table to write the data to
	echo" <table width=\"270\" border=\"0\">" ;
	
	#create a loop
	while( $row = mysql_fetch_array( $rs ) )
		{
  			
				echo"<td><a href=\"editplayer.php?playerid=$row[playerid]\">".$row["DOB"]."</a></td>";
			echo"</tr>";			
		}
			echo" </table>";

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.

[Tormod]

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.

[xwero]

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.

[Tormod]

What, you mean without the DATE_FORMAT function embedded in the query? It definitely works like that.

[xwero]

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.