Jump to content

date not displaying on populated edit form


Go to solution Solved by Barand,

Recommended Posts

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

update-entry-date.jpg

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by ginerjm
Link to comment
Share on other sites

  • Solution

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)

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.