Solved

phpMyAdmin - Date

Posted on 2010-09-23
5
1,085 Views
Last Modified: 2013-12-12
I have set up a table in phpMyAdmin using the "Date" field. It is set up as default to show the date as the following:

2007-08-10 i.e. Year-Month-Days

Is there anyway to change it to:

Days-Month-Year

?
0
Comment
Question by:petewinter
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:guitar7man
ID: 33744237
I don't believe so, it's a MySQL thing. But I could be wrong.

Why not just store the date as a timestamp (MySQL INT) and then using PHP just format the date as you see fit?
0
 

Author Comment

by:petewinter
ID: 33744302
Thanks for your reply.

My issue is that when users are adding the date into a form. It is added in the following format:

dd/mm/yy

but then MySQL changes it to:

yy/mm/dd

which creates an incorrect date. ie. mysql use the dd as the yy

How do I get around this problem?
0
 

Author Comment

by:petewinter
ID: 33744320
I know I could just set the type up as varchar, but this causes the sort by date to be incorrect?
0
 
LVL 3

Accepted Solution

by:
guitar7man earned 500 total points
ID: 33744368
If it was me, I'd take the user-submitted date and convert it to a timestamp, then save the timestamp to the DB in an INT field (this allows sorting). Then in the PHP code, I'd parse the timestamp to be whatever date format I see fit.

http://us.php.net/manual/en/function.date.php

If this isn't an option, then you'd have to account for the MySQL format, and readjust the user submission. You could do this with explode pretty easily. Something like:

$pieces = explode('/', $date_submission);
$mysql_date = $pieces[2] . '/' . $pieces[1] . '/' . $pieces[0];

then you can insert the $mysql_date var into the database which matches yy/mm/dd
0
 

Author Closing Comment

by:petewinter
ID: 33744697
Many Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question