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>";
-
-
9 2108
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)
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";
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
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>";
-
-
-
-
#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.
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.
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.
What, you mean without the DATE_FORMAT function embedded in the query? It definitely works like that.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Julien - Marseille |
last post by:
Hello,
I need help for php syntax when i call Mysql database
I have wrote that and my sql connection is working
I just have a problem with this command line :
$query = "SELECT * FROM...
|
by: Alex Hunsley |
last post by:
A while ago I found a mysql/php/webserver bundle of software for windows
that installed all three at once and made it easy to start developing
sql/php web apps. Now I can't remember what it was or...
|
by: Tony Clarke |
last post by:
Hi,
Just a quick question about performance with MySQL & PHP. If I had a table
in a MySQL database with about 100,000 records in it and I need to find the
last record is there a quick way to do...
|
by: Gary Broughton |
last post by:
Thanks to everybody for all your help and advice. It seems Linux is
going to HAVE to be the next step, but while I know sod all about it, I
have enlisted the help of a colleague to assist with...
|
by: nospam |
last post by:
DEAR MICROSOFT:
WOULD YOU PLEASE put up a web page showing the price list of EXPECTED COSTS
for MOM & POP when choosing between MySql/PHP and .NET.
FIRST:
Show INITIAL COSTS for like a 5-10...
| |
by: RootShell |
last post by:
Hello
First of all i know that PHP is a server side, and JavaScript is a Client
side programming language, but i need to know if there is any way i can add
the variable "countDownInterval" from...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |