date not displaying on populated edit form

[webdeveloper123]

Hi,

I have a "View" records page where data is displayed from a database, and next to each record there is a "Edit" and "Delete" link. Problem is displaying the date on the "Edit" page. On the View page, it shows the dates correctly as they appear in the database. Now the funny thing is, if the date is in YYYY-MM-DD, it will populate on the Edit page, inside the field, correctly. But if the date is in DD-MM-YYYY format, it won't show on the edit page. The date is stored as varchar in the database, and about halfway through the database I changed the format for storage from YYYY-MM-DD to DD-MM-YYYY. This part seems to be ok. I thought the problem might be because originally it was YYYY-MM-DD then in php I changed it to DD-MM-YYYY so on the database end there is a mis-match of the date format and maybe that is what is the cause. And interestingly, if I click on a record which is YYYY-MM-DD, on the date field it will display in DD-MM-YYYY format, which is what I want. 

Here is some code:

<?php 
$queryselect ="SELECT VehicleId, VehSelection, Form.FormId, Form.FirstName, Form.LastName, Form.Email, Form.Age, Form.Birthdate, Form.FavLanguage FROM Form LEFT JOIN Vehicle ON Form.FormId = Vehicle.FormId WHERE Form.FormId = '$FormId'"; 


$result = mysqli_query($link, $queryselect);



 $table = [];

while ( $row = mysqli_fetch_assoc( $result ) ) {
   $table[] = $row;  //add each row into the table array
}


if ( count($table) == 0) {
 //echo ("No records found");
 exit;

}
else
{
	 $birthday = $table[0]["Birthdate"];
}


$date=date_create($_POST['birthday']);
$date1= date_format($date,"d-m-Y");



  $updatequery = "UPDATE Form SET FirstName = '$fname', LastName = '$lname', 
    email = '$email1', age = '$age1', Birthdate= '$date1', FavLanguage = '$fav_language1' WHERE  FormId = '$FormId'";


 $result1 = mysqli_query( $link, $updatequery );

?>


  
   <label for="birthday">Birthday:</label><br>
     <input type="date" id="birthday" name="birthday" value="<?php echo($birthday); ?>"><span id="errorbday"></span><br>

I have attached a screen shot of what the edit page looks like when I click on a record which is DD-MM-YYYY

Thanks

[Barand]

I commented on your d-m-y date formats in your last topic

Quote

Your date storage format is useless. Can't be processed or sorted. Always store in yyyy-mm-dd format (type DATE)

 

[webdeveloper123]

Yes I remembered  but If I change the data type now, will it be ok or will I loose something in my db?

[ginerjm]

If your date value is stored in a non-date type you would need to add a column to your table that is some kind of date field then run a query on the table to convert all of the bad column values to a yyyy-mm-dd value and update the new column with that value.  Then you can remove the bad column from the structure.

[webdeveloper123]

So straight changing the data type is not a good idea?

[ginerjm]

If the column is NOT a type that is date-related then NO you can't just convert it cause it will still be saved in the wrong type.  That's why I took the time to write out what you (may) need to do.   Think.  Think.  Think.

Edited April 18, 2022 by ginerjm

[Barand]

You can change the format of your d-m-Y dates to the correct Y-m-d format with this query

UPDATE date_test 
	SET birthday = str_to_date(birthday, '%d-%m-%Y')
WHERE locate('-', birthday) = 3;

For example

mysql> CREATE TABLE `date_test` (
    ->   `date_test_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `the_date` varchar(15) DEFAULT NULL,
    ->   PRIMARY KEY (`date_test_id`)
    -> ) ;
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO `josen2`.`date_test` (`the_date`) VALUES ('2022-01-01'), ('01-01-2022');
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM josen2.date_test;
+--------------+------------+
| date_test_id | the_date   |
+--------------+------------+
|            1 | 2022-01-01 |
|            2 | 01-01-2022 |
+--------------+------------+
2 rows in set (0.00 sec)

mysql> UPDATE date_test
    ->     SET the_date = str_to_date(the_date, '%d-%m-%Y')
    -> WHERE locate('-', the_date) = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM josen2.date_test;
+--------------+------------+
| date_test_id | the_date   |
+--------------+------------+
|            1 | 2022-01-01 |
|            2 | 2022-01-01 |
+--------------+------------+
2 rows in set (0.00 sec)

Once that has been done and all dates in the correct format you can change the column from varchar to date. (The ALTER query will fail if any dates have an incorrect format)

mysql> ALTER TABLE `josen2`.`date_test`
    -> CHANGE COLUMN `the_date` `the_date` DATE NULL DEFAULT NULL;
Query OK, 2 rows affected (0.87 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM josen2.date_test;
+--------------+------------+
| date_test_id | the_date   |
+--------------+------------+
|            1 | 2022-01-01 |
|            2 | 2022-01-01 |
+--------------+------------+
2 rows in set (0.01 sec)

 

[ginerjm]

Learning moment.  Never had to know but now I see that you can correct the data and then change the format.

[webdeveloper123]

19 hours ago, Barand said:

'%d-%m-%Y')

Hey barand are you sure the above is correct, because that's what I have down for DD-MM-YYYY?

[Barand]

19 hours ago, Barand said:

SET the_date = str_to_date(the_date, '%d-%m-%Y')

The purpose of that is to change a date string (eg '19-04-2022') which is in %d-%m-%Y format to a correct DATE format.

RTFM - https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date

[webdeveloper123]

thanks

[webdeveloper123]

done it! thanks for the code Barand, very kind of you!