-1

I've created a dateTime in php which is added to a database and the formatting is very strange. In the program it is written out like this:

$postTime = new DateTime();
$stringTime = $postTime -> format('d/m/y H:i:s');

and I have also tried it using dashes like d-m-y which gives the same results. For some odd reason the year seems to wrap around the rest of the date:

example

As you can see the date seems to format itself like this YYDD-MM-YY. Any ideas why this is happening, I haven't found any similar problems searching online unless I was just searching the wrong things. If so, sorry in advance!

Method for inserting the data:

$sql = "INSERT INTO Post (postTitle, postDescription, postLocation, postTime, UserID) VALUES ('$postTitle', '$postDesc', '$postLocation', '$stringTime', '$userID')";


    if(mysqli_query($connection, $sql)) {
        echo "success.";
        
    }else{
        echo mysqli_error($connection);
SourOddity
  • 141
  • 11
  • 1
    _What_ is that, that we are looking at there in your screenshot? Is this from phpMyAdmin or something? Then you are probably just looking at a value that was _formatted_ differently for display purposes. – CBroe Mar 10 '21 at 12:45
  • 2
    Most probably it's client's date formatting, use raw SQL client to check what's saved. BTW i don't think that `d/m/y H:i:s` is correct datetime format that should be used in query, use `Y-m-d H:i:s` instead. – biesior Mar 10 '21 at 12:47
  • Ah yeah probably should mention it's in phpmyadmin – SourOddity Mar 10 '21 at 12:47
  • For me it seems like format is `Y-m-d H:i:s`. Maybe your timestamp is wrong? How do you save it in database? Database clients has it's own format to show date field – Justinas Mar 10 '21 at 12:47
  • I don't see how the code is connected to the image of data from phpMyAdmin. Your code deals with current dates. The data in the database is 10+ years old. – El_Vanja Mar 10 '21 at 12:48
  • After the datetime string is created it's just inserted into the DB with a regular insert statement, I'll add it to the post – SourOddity Mar 10 '21 at 12:48
  • 2
    Does this answer your question: https://stackoverflow.com/questions/9541029/insert-current-date-in-datetime-format-mysql/12968248 – Justinas Mar 10 '21 at 12:49
  • 1
    Can you try it with `'d/m/Y` - i.e. capital Y and not lower case – Nigel Ren Mar 10 '21 at 12:49
  • 1
    Ah, I see what might be at fault here. If this format is what you pass to the insert statement, then `10/03/21` is interpreted as `2010-03-21` instead of `2021-03-10`. – El_Vanja Mar 10 '21 at 12:50
  • 1
    When using d/m/y it converts 9th of march 2021 to `2009/03/21` fix the format as mentioned before to `Y-m-d H:i:s` – biesior Mar 10 '21 at 12:52
  • formatting it as Y-m-d H:i:s seems to have worked perfectly, thanks for the quick resolution everyone – SourOddity Mar 10 '21 at 12:52
  • DO NOT use variables in queries! Use prepared statements via `PDO` or `mysqli_*` (i suggest PDO). Search for "SQL-injections" for further|backgorund info. PDO: https://www.php.net/manual/de/book.pdo.php – cottton Mar 10 '21 at 12:54
  • @cotton as they are generated in code, what's the difference? Do you think that OP is gonna attack himself? Of course in any other cases, prepared statements are more than welcome. – biesior Mar 10 '21 at 12:55
  • Thanks for the advice either way, I am relatively new to this, but this is just for a first year uni assignment ATM so I'm just following along with lecture code – SourOddity Mar 10 '21 at 12:56
  • @biesior How do you know its generated? – cottton Mar 10 '21 at 12:57
  • @cottton `$postTime = new DateTime();`? – biesior Mar 10 '21 at 12:58
  • 2
    @SourOddity Ok, just keep in mind that you later do not code that way. SQL-injections are *so* easy to *prevent*, but very dangerous if you do not prevent them. Ask them to change their lecture code - they should tell ppl to not code that way from the beginning =) – cottton Mar 10 '21 at 13:00
  • 1
    @cotton fully agreed ;) It's better to prevent then to cure. – biesior Mar 10 '21 at 13:02
  • 1
    @SourOddity https://www.php.net/manual/en/book.pdo.php is good start - "prepared statements" in this case.. Do some exercise on dummy data and you'll get good habbits quite fast. – biesior Mar 10 '21 at 13:07

1 Answers1

0

Formatting the date as Y-m-D H:i:s has fixed the issue completely.

SourOddity
  • 141
  • 11