SQL Query is not reformatting date on PHP file using DATE_FORMAT method?

Question

By default, PHPMyAdmin has the datetime column formatted as YYYY-MM-DD, but I'm trying to reformat it into MM-DD-YYYY using an SQL statement on my PHP file. This is the code I used, but it's not working:

<?php $usertext = $_POST['textinput']; $insertsql = "INSERT INTO testtext (usertext) VALUES ('$usertext')"; if (isset($_POST['textinput'])) { require '../db.php'; mysqli_query($db, $insertsql); } $selectdate = "SELECT textdate, DATE_FORMAT(textdate,'%m/%d/%Y') AS newdate FROM testtext"; $dateresult = mysqli_query($db, $selectdate); while ($row = mysqli_fetch_assoc($dateresult)) { echo "{$row['textdate']}<br>"; } ?>

This code is able to display rows from the 'textdate' column onto my test site just fine, but the DATE_FORMAT code was unable to reformat the date. Can you please help me fix this code? Thanks so much.

Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements. — John Conde
– John Conde, Commented Mar 20, 2018 at 20:36
@rickdenhaan $row['newdate'] doesn't show the date, just an empty string. $row['textdate'] does show the date though, but not in desired format. — codo7081
– codo7081, Commented Mar 20, 2018 at 20:47
@John Conde Thanks for the info and letting me know. I was just testing this script first since it's easier. I was going to work on security after. — codo7081
– codo7081, Commented Mar 20, 2018 at 20:49

John Conde · Accepted Answer · 2018-03-20 20:37:55Z

2

You are calling your formatted date newdate but are referring to textdate in your code. You need to refer to that alias you assigned the results of DATE_FORMAT() to and not the original column identifier.

while ($row = mysqli_fetch_assoc($dateresult)) { echo "{$row['newdate']}<br>"; }

answered Mar 20, 2018 at 20:37

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

4 Comments

codo7081 Over a year ago

But when I change it to {$row['newdate']}, the date doesn't show up. I think it's because the table in my database is called 'textdate' and 'newdate' doesn't exist there.

John Conde Over a year ago

Do var_dump($row); and see if the value exists in the results

Shadow Over a year ago

@codo7081 nope, you are wrong. Newdate is a field alias. If nothing shows up there, then either the conversion went wrong annd it returned null, or some other error occured.

codo7081 Over a year ago

@John Conde hey it works now! It seems I was supposed to echo out newdate instead. Thanks so much for your help!!

Collectives™ on Stack Overflow

SQL Query is not reformatting date on PHP file using DATE_FORMAT method?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related