Avatar of peter_coop
peter_coopFlag for United Kingdom of Great Britain and Northern Ireland asked on

date format not working

i am trying to return the date in 'mm-dd-yyyy' from a mysql timstamp field 'date'. i have tried the attached code, but still displays the date in 'yyyy-mm-dd' format. any help would be great. thanks
$date = $_GET['date'];
$newdate = date("M d, Y", strtotime($date));
---------------------------------------------------
select statement
$sql = 'select service, activity, department, company, item, date, quantity, type, new'
	. ' from act '
	. ' order '
	. ' by company desc';
	$result = mysql_query($sql);
	return $result;
----------------------------------------------------------------------------------------------------------
insert statement
 
$query = 'INSERT INTO `supply` (`type`, `service`, `quantity`, `department`, `date`, `request`) VALUES (\''.$type.'\', \''.$service.'\', \''.$quantity.'\', \''.$department.'\', \''.$newdate.'\', \''.$request.'\');';
		mysql_query($query) or die(mysql_error());

Open in new window

MySQL ServerPHP

Avatar of undefined
Last Comment
peter_coop

8/22/2022 - Mon
waygood

If your displaying the result FROM the mysql table then use the following. Your use of GET and INSERT looks like your trying to insert the value as a timestamp, BUT I suggest inserting as a datetime to make it readable and prevent issues.

$sql = 'select service, activity, department, company, item, date, quantity, type, new'
      . ' from act '
      . ' order '
      . ' by company desc';
      $result = mysql_query($sql);
      return $result;

$row=mysql_fetch_assoc($result);
$date=date("M d, Y", $row['date']);



suggestion
$newdate = date("Y-m-d H:i:s", strtotime($date));

and you can use this to create a timestamp from the mysql datetime.

function timestamp_from_mysql($timestamp)
{
// $timestamp should be $mysql_datetime  for easier reading.
      ereg ("([0-9]{2,4})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})", $timestamp, $regs);
      ereg ("([0-9]{2,4})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})[^0-9]([0-9]{1,2})", $timestamp, $regs);

      if (sizeof($regs)>1)
      {
            if (sizeof($regs)>4)
            {
                  $date=mktime($regs[4],$regs[5],$regs[6],$regs[2],$regs[3],$regs[1]);
            }
            else
            {
                  $date=mktime(0,0,0,$regs[2],$regs[3],$regs[1]);
            }
      }
      else
      {
            $date=0;
      }
      return $date;
}

Ray Paseur

From your code I can't see where you are displaying anything.  But here is some information on dates that you should know about when you are doing programming that involves dates:
http://en.wikipedia.org/wiki/ISO_8601

If your date string is in ISO8601 format (YYYY-MM-DD HH:MM:SS, for example) you can do a lot of good things with it.  You can sort it chronologically.  You can use it in strtotime to get a timestamp and thence to reformat the date.  You can use only part of the date string, omitting the trailing parts, etc.  For internal representation, the ISO8601 is the way to go.  Then you can reformat the date for external presentation.

$today = "2008-05-22 11:30";
$timestamp = strtotime($today);
$formatted_date = date("l, f J, m d Y", $timestamp); // or whatever date format you want to use.

HTH, ~Ray
ASKER CERTIFIED SOLUTION
hielo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
peter_coop

thanks
Your help has saved me hundreds of hours of internet surfing.
fblack61