Date Formatting mysql->php  | Newbie | | Join Date: Feb 2007
Posts: 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"]. -
#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>";
-
-
| | Member | | Join Date: Feb 2007
Posts: 99
| | | re: Date Formatting mysql->php
Quote:
Originally Posted by 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>";
-
-
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)
| | Newbie | | Join Date: Feb 2007
Posts: 8
| | | re: Date Formatting mysql->php
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";
| | Member | | Join Date: Feb 2007
Posts: 99
| | | re: Date Formatting mysql->php
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
| | Newbie | | Join Date: Feb 2007
Posts: 8
| | | re: Date Formatting mysql->php
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>";
-
| | Member | | Join Date: Feb 2007
Posts: 99
| | | re: Date Formatting mysql->php
Quote:
Originally Posted by Tormod -
-
-
#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.
| | Newbie | | Join Date: Feb 2007
Posts: 8
| | | re: Date Formatting mysql->php
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.
| | Member | | Join Date: Feb 2007
Posts: 99
| | | re: Date Formatting mysql->php
Quote:
Originally Posted by 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. 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.
| | Newbie | | Join Date: Feb 2007
Posts: 8
| | | re: Date Formatting mysql->php
What, you mean without the DATE_FORMAT function embedded in the query? It definitely works like that.
| | Member | | Join Date: Feb 2007
Posts: 99
| | | re: Date Formatting mysql->php
Quote:
Originally Posted by Tormod 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.
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,374 network members.
|
