473,881 Members | 1,653 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date Formatting mysql->php

Tormod
8 New Member
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
9 2129
xwero
99 New Member
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
8 New Member
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
xwero
99 New Member
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
8 New Member
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
xwero
99 New Member
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
8 New Member
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
xwero
99 New Member
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
8 New Member
What, you mean without the DATE_FORMAT function embedded in the query? It definitely works like that.
Feb 28 '07 #9
xwero
99 New Member
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

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

Similar topics

7
3833
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 utilisateurs WHERE pseudo_utilisateur='$login';";
7
2129
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 where to get it, and wondered if anyone here can suggest anything along those lines? thanks alex
8
4494
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 this, other than going through the whole database to find the last record? Would the amount of records in the table cause major performance problems? For example, if I had a html form that people used to log something and needed to display a...
0
1997
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 that side of things. So I shall crack on with that, and subsequently let you know what the outcome is. :-) Thanks again Gary -----Original Message----- From: Nils Valentin
13
1767
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 page web site that can take a few orders... INCLUDE THE ISP charges..developer, software...
2
5072
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 this particular FULL code into a MySQL field. Here's the drill: I have a timer on a PHP webpage, and i want to prevent the user from doing a
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10718
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10816
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10401
tracyyun
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9554
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7110
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 then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4597
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 we have to send another system
2
4196
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.