Format date from database to table using php

Question

I have a database full of football fixtures, sorted in descending order. Everything works fine however the dates are in the following format: YYYYMMDD like so - 2014-05-11.

All table contents are selected with:

 $result = mysqli_query($con,"SELECT * FROM Fixtures ORDER BY Date DESC");

Then inserted into the table with the below code:

while($row = mysqli_fetch_array($result))
  {

  echo "<tr>";
  echo "<td>" . $row['Date'] . "</td>";
  echo "<td>" . $row['Home Team'] . "</td>";
  echo "<td>" . $row['Score'] . "</td>";
  echo "<td>" . $row['Away Team'] . "</td>";
  echo "<td>" . $row['Score1'] . "</td>";
  echo "<td>" . $row['Competition'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

How can I make it so these dates appear DDMMYYYY such as 11-05-2014?

Thanks!

Manual for equivalent in MySQL: dev.mysql.com/doc/refman/5.5/en/… — Marc B
– Marc B, Commented Apr 28, 2014 at 19:48

Teena Thomas · Accepted Answer · 2014-04-28 20:02:06Z

4

Solution:

echo "<td>" . date('d-m-Y', strtotime($row['Date'])) . "</td>";

Manual

edited Apr 28, 2014 at 20:02

answered Apr 28, 2014 at 19:50

Teena Thomas

5,2091 gold badge15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marc B Over a year ago

that'd only work if mysql actually returns a unix timestamp. date('d-m-Y', '2014-04-28') isn't going to work at all.

danielparry8 Over a year ago

Hi, Just tried this method and all dates are now set as: 31-12-1969??

Abhik Chakraborty · Accepted Answer · 2014-04-28 19:47:38Z

1

Use DateTime::createFromFormat

$date = DateTime::createFromFormat('dmY', $row['Date']);
echo $date->format('d-m-Y');

answered Apr 28, 2014 at 19:47

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

2 Comments

danielparry8 Over a year ago

without sounding like an idiot how would I put this into my code?

Abhik Chakraborty Over a year ago

DateTime::createFromFormat will accept the date from a specified form. Like in your case its dmY, then $date->format('d-m-Y') will hold the format as you want. DateTime is a powerful class in PHP to handle datetime conversion and should be used intead of other conversion technique which are not always correct. You can add the first line inside the loop and then echo "<td>" . $date . "</td>"; thats all.

dialogik · Accepted Answer · 2014-04-28 19:52:16Z

1

Either fetch it properly from the database:

$query = "SELECT
    ...,
    ...,
    DATE_FORMAT(Date, '%d-%m-%Y') AS Date
FROM Fixtures
ORDER BY Date DESC";

$result = mysqli_query($con, $sql);

Or format it in your PHP

echo date('d-m-Y', $row['Date']);

answered Apr 28, 2014 at 19:52

dialogik

9,59218 gold badges79 silver badges122 bronze badges

1 Comment

danielparry8 Over a year ago

Am i doing something wrong with my method? I don't have much knowledge in PHP so thought this would be fine. Without sounding too much like a noob, how would i implement your above code into mine?

Collectives™ on Stack Overflow

Format date from database to table using php

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related