Change format of date from YYYY-MM-DD to DD-MM-YYYY

PHP
1

Good day!

I encountered error in saving data data in my database.

First, I have code for uploading/importing .xml file to database.

in my .xml file the date is : 10/1/2011 but when it was display in webpage the result is :
2011-01-10T00:00:00.000

and I used

$date = substr($date,0,-13);

to remove the last part.

and now I used this code:


$date = strtotime($date);
 $date = date('d-m-Y', $date);

to change the format of date…the good thing is it was display as 01-10-2011 when I run that code. But it did not save in database.

Here is my whole code:


<?php
$data = array();


$con = mysql_connect("localhost", "root","");
if (!$con) {
  die(mysql_error());
}
$db = mysql_select_db("db_upload", $con);
if (!$db) {
  die(mysql_error());
}

$sql = "select * from employee";
$result =  mysql_query($sql, $con);
if (!$result) {
    die(mysql_error());
}
$total = mysql_num_rows($result);
if ($total > 0) {
    $sql = "delete from employee";
    $result =  mysql_query($sql, $con);
    if (!$result) {
        die(mysql_error());
    }
}

function add_employee($emp, $employee, $last, $mi, $date, $time)
  {
      global $data;

      $con = mysql_connect("localhost", "root","");
      if (!$con){ die(mysql_error());}
      $db = mysql_select_db("db_upload", $con);
      if (!$db) {
          die(mysql_error());
      }

      $emp = $emp;
      $employee = $employee;
      $last = $last;
      $mi = $mi;

      $date = substr($date,0,-13);
      $time = substr($time,11,-4);
      $date = strtotime($date);
      $date = date('d-m-Y', $date);

      $time = strftime('%I:%M %p', strtotime($time));



      $sql = "INSERT INTO employee (EMP_NO, Name, last, mi, date, time) VALUES ('$emp', '$employee', '$last', '$mi', '$date', '$time')";
      mysql_query($sql, $con);


      $data []= array('EMP_NO' => $emp, 'Name' => $employee, 'last' => $last, 'mi' => $mi, 'date' => $date, 'time' => $time);

  }

  if ( $_FILES['file']['tmp_name'] )
  {
      $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );

      $rows = $dom->getElementsByTagName( 'Row' );
      global $last_row;
      $last_row = false;
      $first_row = true;
      foreach ($rows as $row)
      {
          if ( !$first_row )
          {

              $emp = "";
              $employee = "";
              $last = "";
              $mi = "";
              $date = "";
              $time = "";


              $index = 1;
              $cells = $row->getElementsByTagName( 'Cell' );

              foreach( $cells as $cell )
              {
                  $ind = $cell->getAttribute( 'Index' );
                  if ( $ind != null ) $index = $ind;

                  if ( $index == 1 ) $emp = $cell->nodeValue;
                  if ( $index == 2 ) $employee = $cell->nodeValue;
                  if ( $index == 3 ) $last = $cell->nodeValue;
                  if ( $index == 4 ) $mi = $cell->nodeValue;
                  if ( $index == 5 ) $date = $cell->nodeValue;
                  if ( $index == 6 ) $time = $cell->nodeValue;
                  $index += 1;
              }

              if ($emp=='' and $employee=='' and $last=='' and $mi=='' and $date=='' and $time=='') {
                    $last_row = true;
              }
              else {

                    add_employee($emp, $employee, $last, $mi, $date, $time);
              }
          }
          if ($last_row==true) {
              $first_row = true;
          }
          else {
              $first_row = false;
          }

      }
  }
  ?>

  <html>
  <body>
  <table>
  <tr>
      <th>Employee Attendance</th>
  </tr>

  <?php foreach( $data as $row ) { ?>
  <tr>
  <td><?php echo( $row['EMP_NO'] ); ?></td>
  <td><?php echo( $row['Name'] ); ?></td>
  <td><?php echo( $row['last'] ); ?></td>
  <td><?php echo( $row['mi'] ); ?></td>
  <td><?php echo( $row['date'] ); ?></td>
  <td><?php echo( $row['time'] ); ?></td>
  </tr>
  <?php } ?>
  </table>
  </body>
 </html>

Thank you

2

Use the date in format YYYY-MM-DD to save in the database.

3

How can I save to the database in YYYY-MM-DD format, based on my code…

Thank you

4 

$date = substr($date,0,-13);

At this point in your script, $date contains the date in YYYY-MM-DD format. Before you start transforming it for display, put it in another variable, for example:


$date = substr($date,0,-13); 
$mysqldate = $date;

Then use $mysqldate instead of $date in the INSERT query.

5

You don’t even need to remove those 13 chars at the end


var_dump(
  date(
    'd-m-Y',
    strtotime(
      '2011-01-10T00:00:00.000'
    )
  )
);
// string '10-01-2011' (length=10)

so you can just do


$timestamp=strtotime($date);
$display=date('d-m-Y', $timestamp);
$mysql=date('Y-m-d', $timestamp);

as long as you just keep $timestamp (which is the number of seconds since January 1st, 1970, or “the epoch”) you can do anything with it you like; just pass it to the [fphp]date[/fphp] function as the second parameter :slight_smile: