How to convert date from YYYY-MM-DD to DD-MM-YY and time from HH-MM-SS to HH-MM AM or PM in PHP

cws147 · October 11, 2022, 11:55pm

Username (e.g. epiz_XXX) or Website URL Cue Well Snooker (epizy.com)

(please specify the website or account you are asking about)

Error Message

(please share the FULL error message you see)

Other Information

(other information and details relevant to your question) I’m trying to convert both dates and times that are displayed in my PHP file after retrieving from Mysql database.
If you go to Cue Well Snooker (epizy.com), you can see that dates are displyed as YYYY-MM-DD. I need them to display as DD-MM-YY.
Also, times are displayed as HH:MM:SS. I need them to display as HH:MM AM or PM
The PHP file code is given below.
I tried changing the date format in the db using various sql quaries, but either got multiple errors or changes weren’t seen.
I’ve also tried inserting various PHP code found from searches or other forumns, but no success. Possibily because I don’t know how to insert any found code into my current code
Here’s the code for ladder-matches.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<title>Cue Well Snooker</title>
<link rel="icon" type="image/x-icon" href="/images/favicon.ico">

<meta name="viewport" content="width=device-width, initial-scale=1" user-scalable="no">


<!-- Link css file -->
    <link href="css/style.css" rel="stylesheet" type="text/css">
    <style>
	table{
            width: 280px;
            margin: auto;
            font-family: Calibri;
			font-size: 11px;
			color: #CC0033;
        }
        table, tr, th, td{
			border: 1px solid #009966;
            border-collapse: collapse;
            padding: 5px;
        }
        th, td{
            text-align: center;
            vertical-align: middle;
        }
        tr:nth-child(even){
            background-color: #88ffbb;
        }
		</style>


</head>

<body>

    <div class="header">
        <!-- Logo -->
        <a href="index.php" class="logo"><img src="images/cws.png" style="border: 0px"></a>
</div>
<?php require_once('navbar.php'); ?>

<!-- Container -->
<div class="container">

<div class="pageheading">
<h1>Scheduled Ladder Matches</h1>
</div>
<div class="innercontainer">
  
  <button class="ladderbutton"><a href="book-ladder-match.php">Book Ladder Match</a></button>
<button class="ladderbutton"><a href="player-contacts.php">Player Contacts</a></button>
<br>


<?php require 'config/config.php';
       
?>

<?php   
	//Output Form Entries from the Database
    $sql = "SELECT date, time, challenger, opponent, frames FROM bookladdermatch";
    //fire query
    $result = mysqli_query($con, $sql);
    if(mysqli_num_rows($result) > 0)
    {
       echo '<table> <tr> <th> Match Date </th> <th> Start Time </th> <th> Challenger </th> <th> Opponent </th> <th> Best of Frames </th> </tr>';
       while($row = mysqli_fetch_assoc($result)){
         // to output mysql data in HTML table format
           echo '<td>' . $row["date"] . '</td>
           <td> ' . $row["time"] . '</td>
           <td>' . $row["challenger"] . '</td>
		   <td>' . $row["opponent"] . '</td>
		   <td>' . $row["frames"] . '</td> </tr>';
       }
       echo '</table>';
    }
    else
    {
        echo "0 results";
    }
    // closing connection
    mysqli_close($con);


?>
</div>
</div>
</body>
</html>

YT_Xaos · October 12, 2022, 12:05am

This seems like a question better suited for Stackoverflow. Have you tried using the date() function in PHP?
e.g:
date("d-m-Y")

cws147 · October 12, 2022, 12:24am

Thanks for the fast response.
I do not know PHP, so don’t know how to use the date() function.
I searched for a solution on Stackoverflow, but every piece of code I tried didn’t work. Possibly because I don’t know where to edit my php code with any of the suggested codes.
I will try Stackoverflow and ask if someone can explain how I edit my current code with the correct PHP.

YT_Xaos · October 12, 2022, 1:18am

I showed you how to use it in my post.

cws147 · October 12, 2022, 1:34am

You did, however, you did not show how to edit my current code.
I tried this - echo ‘’ . $row[date(“d-m-Y”)] . '
But that just displayed blank date column - see Cue Well Snooker (epizy.com)

YT_Xaos · October 12, 2022, 2:27am

Sorry, but I won’t go through all of your code to try to figure out where to put it. However, what you did is invalid. Let me explain, you’re watching a row called “10-11-2022” which is not correct. Inside your database you need to edit the dates and make them MM-DD-YYYY, then edit the code that you insert into the table and add date(”m-d-Y”). I do suggest you learn PHP as it would make this much easier.

cws147 · October 12, 2022, 2:52am

Thank you. I’m trying to learn PHP. This is my once and only attempt at building a website. I am only doing this for our social snooker club as we are all older guys and some are having difficulties organising games and reporting results via whatsapp which causes confussion amongst other players. We have a limited number of tables for game play. I thought a simple website would help, but the project turned into something bigger than I had anticipated. I’m so close to completion, so don’t won’t to give up now.
From my readings of how date is handled in myqsl database is that it needs to be inserted as YYYY-MM-DD as this is the default setting. This is why I believe I have to convert it in my PHP file and not in the db table.
Anyway, I will continue my lessons.
Thanks for your time here.

YT_Xaos · October 12, 2022, 10:36am

That’s if you put the default for that column to DATETIME.

cws147 · October 12, 2022, 10:53am

Sorry, but not true from what I’ve found. I can set the column to Varchar or Date and after input, the date still displays as YYYY-MM-DD
My input form just uses standard/default “date” input as the datepicker.

YT_Xaos · October 12, 2022, 10:55am

I’m not sure why that would be happening, I will test it out on my own and then I’ll get back to you.

wackyblackie · October 12, 2022, 11:15am

Here’s how to convert YYYY-MM-DD to whatever format you want:

$d= explode('-', $row['date']); //year-month-day
$d= mktime(0, 0, 0, $d[1], $d[2], $d[0]); //hour, minute, second, month, day, year
echo date('h:i:s A \o\n l, F jS, Y', $d); //07:15:00 AM on Wednesday, October 12th, 2022

PHP: mktime - Manual
PHP: date - Manual

YT_Xaos · October 12, 2022, 11:19am

I just tested the output of it and here’s what I got.

I’m not sure why yours converts to YYYY-MM-DD.

cws147 · October 12, 2022, 8:51pm

I have looked at your “How to convert” date format code, re-read the PHP manual links you’ve provided, but these fail to tell me how I insert the correct code into my current code, or how do to completely rewrite new php code? You have suggested that I learn PHP, which is a fair statement, but how does one learn a new lanuage when the lanuage can be written in so many different ways. This is what I have observed across many tutorials and forums, different php code to get the same result. Why is this??? It’s like as though programmers/coders have their own interputations of the lanuage.
I’ve noticed that your INSERT INTO code is different to mine. Does your code insert the date into db table as MM-DD-YYYY (i.e. month-day-fullyear 12-10-2022)? How than did you get 10-12-2022 (i.e. day-month-fullyear)?
No wonder I’m becoming extremely frustrated

This is my INSERT INTO code -

//This below line is a code to Send form entries to database
        $sql = "INSERT INTO bookladdermatch (date, time, challenger, opponent, frames) VALUES ('$date','$time','$challenger', '$opponent', '$frames')";
		
    	//fire query to save entries and check it with if statement
        $rs = mysqli_query($con, $sql);
        if($rs)
        {
            echo "<h4>Ladder booking recorded successfully!</h4>";
        }
      	else{
         	echo "<h4>Error, booking has not been recorded!</h4>"; 
        }
      //connection closed.
        mysqli_close($con);
    }
	
?>

This is my OUTPUT code -

<?php   
	//Output Form Entries from the Database
    $sql = "SELECT date, time, challenger, opponent, frames FROM bookladdermatch";
    //fire query
    $result = mysqli_query($con, $sql);
    if(mysqli_num_rows($result) > 0)
    {
       echo '<table> <tr> <th> Match Date </th> <th> Start Time </th> <th> Challenger </th> <th> Opponent </th> <th> Best of Frames </th> </tr>';
	   
       while($row = mysqli_fetch_assoc($result)){
         // to output mysql data in HTML table format
           echo '<td>' . $row["date"] . '</td>
           <td> ' . $row["time"] . '</td>
           <td>' . $row["challenger"] . '</td>
		   <td>' . $row["opponent"] . '</td>
		   <td>' . $row["frames"] . '</td> </tr>';
       }
       echo '</table>';
    }
    else
    {
        echo "0 results";
    }
    // closing connection
    mysqli_close($con);


?>

And this is my form -


<form action="book-ladder-match.php" form method="post">

       <label for="date">CHOOSE DATE OF PLAY</label>
	   <input type="date" id="date" name="date" required>
	   
	   <label for="time">START TIME</label><br>
	   <input type="time" id="time" name="time" required><br>
	   
	   <label for="challenger">ENTER CHALLENGER NAME</label>
	   <input type="doubles" id="challenger" name="challenger" required>
	   
	   <label for="opponent">ENTER OPPONENT NAME</label>
	   <input type="doubles" id="opponent" name="opponent" required>
	   
	   <label for="frames">BEST OF FRAMES</label><br>
	   <input type="frames" id="frames" name="frames" required><br>


	<input type="submit" value="Book Ladder Match" name="insert">
  
</form>

Greenreader9 · October 12, 2022, 9:25pm

First of all, that SQL INSERT code is vulnerable to SQL injection. It’s fine if this is for testing, but if you are planning to open this to the public, or store sensitive info, you need to fix this.

To use the different date type in the DB, change everything currently in the database by hand, or if it is not important, just delete it.

When you insert the data, you need to convert the POSTed date into the format you want, probably using mktime. When the form is submitted, have PHP grab the info that is submitted $_POST['date'], and convert that into the format you want. See @wackyblackie’s reply for a method (and resources) on how to do this.

cws147 · October 12, 2022, 10:03pm

Thanks for your comments.
I’m thinking it might be best if I just scrap the entire project. It’s all getting too complicated and stressful. I will take time off to reassess whether to continue with my hobby website.
I was told that using mysql code was old and subject to SQL injection, so went with mysqli. Now you say even with this I’m open to SQL injection. This means that I have to start all over again with the INSERT and SELECT code. I have multiple files using these codes. So all have to be edited or re-invented.
This is just all too much for me at the moment. I knew nothing about HTML, PHP and building a website when I started 8 weeks ago and it now it appears that I need to start all over again with the files that interact with the db.
Thanks for your input here. I praise those that have offered a helping hand. A far better response than what I receive from Stackoverflow. I apolgise if I have wasted anyones time here.

Greenreader9 · October 12, 2022, 10:11pm

Just because you are using MySQLi does not make it safe. You have to sanitize the data before you insert it into the database. On second review, you may have done that before that INSERT statement, I just can’t view the code since you did not share it.

A lot of times it is very stressful to jump right into a project your first time with the language. Personally, this is what I recommend (It is also what I (kind-of) did):

Create something you are proud of using just HTML and CSS.
Add simple PHP to the project and fully understand it (For example, using PHP to insert the current year for the copyright in the footer)
Slowly enhance your site using PHP, while learning it online (Or in-person) at the same time. Whenever you code something, make sure you understand it before you move on.

Thewebuser22 · October 14, 2022, 1:51am

I should add to this.

Doing something you are not familiar with will not help, and may even hinder you.

I have asked my teacher so many times to teach me calculus when I am on algebra 2.

I will go on Wikipedia, learn how to calculate it and try to apply it but am immediately faced with problems that I don’t know how to resolve. This is because while I think I get it, I can’t grasp why it works or how it works to the point where I am blindly following things. This is not helpful and is even dangerous at times.

Working from the ground up is the best method. That’s not to say that it’s possible the other way around, but it is extremely harder and your chances of crashing and burning are way greater.

cws147 · October 14, 2022, 3:05am

Hi, Thanks for your comments. I get where you’re coming from. However, this is a one off project that I’m not even sure will get used. If my snooker buddies don’t like it, than it’ll get scrapped. Therefore I can’t see the point in learning how the universe was created and how it manages to keep functioning.
Having said that, I am enjoying what I have managed to achieve so far even though there have been frustrations along the way.
I am continuing my quest to learn how to sanatise my form inputs before sending to the db.
I have had one success though. I now have the date output displaying as dd-mm-yy.
I know you managed this in the INSERT INTO code you gave above, but I couldn’t get that to work, so I fiddled around with my output code and came up with this code ‘’ . (date(“d-m-y”, strtotime( $row[“date”]))) . '
Did the trick.
Now I’m trying to set the date column to DESC, but it won’t stay fixed. Each time a new entry is added, the date order in the db resets back to default ASC.
To make it DESC, I used the following SQL query, but after clicking the GO button, and than add a new record via my form, the below query disapears and I’m just left with SELECT * FROM bookladdermatch

This the SQL query I add that will not stay.
SELECT *
FROM bookladdermatch
ORDER BY date DESC;

system · October 21, 2022, 3:05am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.