While learning through a video tutorial on creating Form and Login Systems with PHP and MySQL, I ran into a minor issue, that I would like to share in this blog post and what I did to solve it.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS, DB, and Language(s) Used:
- Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
- MySQL 5.7.23
- PHP 7.0.30-0ubuntu0.16.04.1
Photo by H E N G S T R E A M on Unsplash
To start, I have this table and structure:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> desc users; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | first_name | varchar(25) | NO | | NULL | | | last_name | varchar(25) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | password | text | NO | | NULL | | | image | text | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) |
I needed to add in a column for what date a user registers or joins in the ‘system’.
Simple enough.
Use ALTER TABLE ADD COLUMN syntax.
In my opinion, this a prime use case for a DEFAULT column value.
Why not make it as easy as possible for the user registering (Not to mention yourself the programmer!) and just remove all the ‘guesswork’, such as:
- What is even the date today (when registering) anyway? (Doh.. *Facepalm*)
- What format is the user to enter? (e.g., “July 2nd, 2018”, 07/02/18, 02 Jul 2018, etc…) You get the picture.
- That’s another field in the form on the front-end to code and manage.
So, my thinking is when a user registers, they register and I’ll just store the current date for them.
Easy peasy.
They (the user), need not even see the date field.
Matter of fact, MySQL has some handy date and time functions already.
(Check out Handy MySQL Date Functions With Examples I wrote about a few of them.)
In particular, 2 synonymous functions CURDATE() and CURRENT_DATE() both return, wait for it…. the current date.
Perfect…
Let me get this ‘light work’ done and get back to more pressing things (I think to myself).
So I’ll add in a column, date_registered, of data type DATE right after the existing image column.
Give it a default of CURDATE(), and on INSERT (registering), one less thing I need to worry with right?
Here is the SQL below (and there is plenty of it too):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> alter table users add column date_registered date after image default curdate(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default curdate()' at line 1 mysql> alter table users add column date_registered date default curdate() after image; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'curdate() after image' at line 1 mysql> alter table users add column date_registered date default curdate() after image; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'curdate() after image' at line 1 mysql> alter table users add column date_registered date set default curdate() after image; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set default curdate() after image' at line 1 mysql> alter table users add column date_registered date after image set default curdate(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set default curdate()' at line 1 mysql> alter table users add column date_registered date after image set default curdate; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set default curdate' at line 1 mysql> alter table users add column date_registered date default curdate() after image; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'curdate() after image' at line 1 mysql> alter table users add date_registered date default curdate() after image; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'curdate() after image' at line 1 mysql> alter table users add column date_registered date after image set default curdate(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set default curdate()' at line 1 |
Are you laughing at me yet?
I am.
I tried every configuration of the command that I knew how to get this column added with a DEFAULT value and in a certain position within the table.
Screw the DEFAULT value for now, can I please just get the column added already?
1 2 3 | mysql> alter table users add column date_registered date after image; Query OK, 0 rows affected (0.90 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Finally…
Seems like you can’t complete both of those actions in the same command I came to conclude.
You know adding in a new column to an existing table and being picky enough to want a DEFAULT value as well for crying out loud.
Now that the column is there, I’ll add in the DEFAULT and get back to PHP.
1 2 3 4 5 6 | mysql> alter table users alter column date_registered set default curdate(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'curdate()' at line 1 mysql> alter table users alter column date_registered set default current_date(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_date()' at line 1 mysql> alter table users modify date_registered date default current_date(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_date()' at line 1 |
I guess I need to spend ‘penance’ reading the ALTER TABLE documentation.
When in doubt, Google is your friend so the search began!
And I found this StackOverflow Q&A but it was a bit dated (for version 5.5), but nevertheless, pointed me in the right direction and off to the docs I went.
And wouldn’t you know, this section spelled it out for me:
11.7 Data Type Default Values
“With one exception, the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that, for TIMESTAMP and DATETIME columns, you can specify CURRENT_TIMESTAMP as the default.”
No wonder I couldn’t get it to work!
That is not supported!
Knowing this, finally I finished up with this table description:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> desc users; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | first_name | varchar(25) | NO | | NULL | | | last_name | varchar(25) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | password | text | NO | | NULL | | | image | text | YES | | NULL | | | date_registered | date | YES | | NULL | | +-----------------+--------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) |
But, skipped one thing in my haste to figure this out. I forgot to make the date_registerd column NOT NULL as well.
This command takes care of that:
1 2 3 | mysql> alter table users modify column date_registered date not null; Query OK, 0 rows affected (0.92 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Now the for final table description I have:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> desc users; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | first_name | varchar(25) | NO | | NULL | | | last_name | varchar(25) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | password | text | NO | | NULL | | | image | text | YES | | NULL | | | date_registered | date | NO | | NULL | | +-----------------+--------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) |
So what to do about the DEFAULT date value I want for column date_registered?
Luckily, I can accomplish it in PHP with this variable using the
date() function:
1 | $date = date("Y-m-d"); |
By default, MySQL displays values of the DATE data type in this format:
1 | 'YYYY-MM-DD' |
so using the format parameters "Y-m-d" provides that same format.
See the full list of available format parameters in the official PHP date() function documentation.
Now I can use the $date variable for INSERT and store a default date column value, which was my goal to begin with:
1 2 3 4 5 6 7 8 | mysql> select date_registered from users; +-----------------+ | date_registered | +-----------------+ | 2018-07-26 | | 2018-07-26 | +-----------------+ 2 rows in set (0.00 sec) |
I hope this blog post helps others’ facing this same requirement or problem as I did.
As always, I welcome comments below on the post itself, as well as better alternatives to those more experienced and versed in MySQL and/or PHP.
Thanks for reading.
Explore the official MySQL 5.7 On-line Manual for questions and more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.