08-29-2018, 06:13 PM
(This post was last modified: 08-29-2018, 06:14 PM by Moomintroll.)
Thanks Brandin.
I went and altered the database in the end using Sequel Pro.
First, I set the username field to autopopulate with fname-space-lname:
ALTER TABLE users ADD COLUMN `username` varchar(255) GENERATED ALWAYS AS (CONCAT(`fname`,' ',`lname`)) VIRTUAL ;
Then I created the membership number by creating a new field that concatenates the YearMonthDay-id. I found a way to pad the month, day and id with leading '0's to make it a bit more consistent:
ALTER TABLE users ADD COLUMN member_no varchar(25) GENERATED ALWAYS AS (CONCAT(DATE_FORMAT(`join_date`,"%y%m%d"),"-",LPAD(`id`,3,0))) ;
So if you joined today, your unique membership number would be 180829-015.
The only trouble now is that if two people with the same name join (actually not that improbable), if usernames are used only the first one seems to be able to log in. I think I can work around this by simply replacing the 'username OR email' instruction with just 'email' as duplicates cannot exist.
Crude perhaps, but I think it will do for now. It would be great if future implementations gave a few options for designing registrations formats like this.
I went and altered the database in the end using Sequel Pro.
First, I set the username field to autopopulate with fname-space-lname:
ALTER TABLE users ADD COLUMN `username` varchar(255) GENERATED ALWAYS AS (CONCAT(`fname`,' ',`lname`)) VIRTUAL ;
Then I created the membership number by creating a new field that concatenates the YearMonthDay-id. I found a way to pad the month, day and id with leading '0's to make it a bit more consistent:
ALTER TABLE users ADD COLUMN member_no varchar(25) GENERATED ALWAYS AS (CONCAT(DATE_FORMAT(`join_date`,"%y%m%d"),"-",LPAD(`id`,3,0))) ;
So if you joined today, your unique membership number would be 180829-015.
The only trouble now is that if two people with the same name join (actually not that improbable), if usernames are used only the first one seems to be able to log in. I think I can work around this by simply replacing the 'username OR email' instruction with just 'email' as duplicates cannot exist.
Crude perhaps, but I think it will do for now. It would be great if future implementations gave a few options for designing registrations formats like this.