php

PHP Format Date from MySQL

There are lots of methods in PHP for working with dates and time. Although it is relatively easy to work with date and time in PHP, finding which way to use. can be intimidating

This tutorial will teach you how to format date information fetched from a MySQL database.

Step 1: Fetch Date from MySQL

The first step is to learn how to fetch data stored in a MySQL table. For such an example, we can use a raw query.

<?PHP
    $conn = new mysqli("localhost", "root", "mysql", "sakila");
    if ($conn->connect_error) {
        die("Connection failed" . $conn->connect_error);
    }
    $query = "SELECT * FROM film WHERE film_id=1";
    $result = mysqli_query($conn, $query);
    $conn->close();
?>

Once the connection to the database is created, we perform a query on the database to fetch all the rows where the film_id = 1.

Step 2: Format Database result into an Array

Until now, we have a variable called $result that stores the query’s result in the database. To use it, we need to convert it into an array, making it easier to fetch the date. We can do this by using the mysqli_fetch_array function. The function takes the result from the mysqli_query function.

Consider the example shown below:

<?PHP
    $conn = new mysqli("localhost", "root", "mysql", "sakila");
    if ($conn->connect_error) {
        die("Connection failed" . $conn->connect_error);
    }
    $query = "SELECT * FROM film WHERE film_id=1";
    $result = mysqli_query($conn, $query);
    print_r((mysqli_fetch_array($result)));
    $conn->close();
?>

The above code formats the output from the database into an array as shown below:

(
    [0] => 1
    [film_id] => 1
    ………TRUNCATED………..
    [11] => Deleted Scenes,Behind the Scenes
    [special_features] => Deleted Scenes,Behind the Scenes
    [12] => 2006-02-15 05:03:42
    [last_update] => 2006-02-15 05:03:42
)

In our example, we are only interested in the last_update column, which contains the date.

To fetch that index, we can use PHP indexing operation as:

$arr = mysqli_fetch_array($result);
echo end($arr);

The above snippet takes the array and retrieves the value in the last index. The result is as:

2006-02-15 05:03:42

You can save the above variable to a value to later use.

NOTE: You can circumnavigate the above method and query a single row from the database.

Step 3: Format Date

Once we have the date from the database, we need to use PHP to format it. In this example, we will use the date_format() method.

The syntax of the method is as shown:

date_format(date,format);

The function takes the date and a specific format.

You can learn more about the format specifiers in the resource provided below:

https://www.php.net/manual/en/function.date.php

Let us now convert the date from the database to a specific format.

$date = date_create(end($arr));
echo date_format($date, "D M j G:i:s T Y");

We use the date_create method first to convert the date from a string to a DateTime object. This allows the date_format method to format it.

The resulting value is as:

Wed Feb 15 5:03:42 UTC 2006

Conclusion

This article covered how to query and format date from a MySQL database to any specified format using the PHP programming language.

Thanks for reading!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list