Adding an OrderShippedDate field to my Order mysql table? Not allowed two timestamp fields?
I would like to add a date field to my Order table that logs when my client updates orders in their CMS as dispatched. We have a TINYINT field that does this, to simply indicate "Yes" or "No" for the dispatched status, and that works fine but I'd also like to log the date so that when a customer checks their order they can see a dispatch date.
I have tried creating another timestamp field in our table set to "ON UPDATE CURRENT_TIMESTAMP" however we receive an error on this because of the existing "OrderDate" field which is also a timestamp field...
"SQL Error (1293): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause"
What is the solution here? I considered setting the field as a "DATE" field, but I'm not sure how to collect, in PHP, the current date and, for example, add it to a hidden form field in my client's CMS.
Related to this, I noticed that the OrderDate field is set to "ON UPDATE CURRENT_TIMESTAMP". If my client updates an order as shipped, in the CMS, won't that change the OrderDate date? Should I just change that default state to "CURRENT_TIMESTAMP" as opposed to "ON UPDATE CURRENT_TIMESTAMP"?
Would appreciate any help or advice that could be offered. Thank you.