Incremental Membership Number instead of Username?

[Moomintroll]

Hi (for the second time in two days!),

I'm a complete noob to User Spice but it is presenting some exciting possibilities.

The site I am designing is for orthopaedic surgeons. They are a bit straight laced for usernames (and I doubt they'd remember them anyway).

I was wondering if anyone could recommend the best way of autogenerating a membership number that would consist of, say, the year, month and date of joining, as well as a short unique number in case a few join on the same day (highly likely as we intend to relaunch the site soon). I had something like '20180820-001' in mind and would envisage displaying it in the account page and perhaps the topbar along with their first and last names.

Username looks pretty key to the whole construct - is it easy to switch that reliance to a membership number?

I'd like to leave the US code as intact as possible for headache-free upgrades so I am unsure if it is a good idea to tinker with either the id or username fields in the database.

Much obliged.
M

[Brandin]

There is no straight forward way to get this action completed. On the other hand, you can already use a User ID to login to UserSpice, but these IDs normally begin at 1. You can always reset the auto increment on the Database to begin at says 200000000 and increment by like an odd number (3, 7) or what have you.

Thanks,
Brandin.

[mudmin]

I have an idea that @brandin might have an opinion on. What if you setup a cron job to insert and delete a blank user at midnight. Note that because of length issues, you can't do the full year, but you can do:
$db->insert('users',['id'=>'182308000');
$db->delete('users',array('id','=','182308000'));

Or I guess you could just have it do
$db->query("ALTER TABLE users AUTO_INCREMENT = 182308000");

Set that to happen every night at midnight and all the other ids should fall into place.

We would have to help flesh a little more of this out, but it could work.

[Brandin]

Best solution would be:
$db->query("ALTER TABLE users AUTO_INCREMENT = 182308000");

With a cron job every night that changes the Auto Increment to the current day at 000.

Thanks,
Brandin.

[Moomintroll]

Really interesting; I hadn't even considered a cron job.

My thoughts had been going along the lines of calculating a concatenation of the year + month + day + increment starting at 100 which would happen when the DB record is created. But 'm not sure that's even possible.

It wouldn't really matter about resetting the trailing number as I doubt we'd ever get more than 150 registering on the same day, so the date prefix would guarantee the uniqueness. Part of my logic is that it would be easy to work out when their subscription is due even if you were nowhere near a computer (e.g. at the annual conference).

As a corollary question, how easy would it be to replace the dependence on the username in the out-of-the-box version? It seems like you can't do anything without creating it.

[Brandin]

You can log any user in by adding their User ID to the Session and Redirecting them. That will trigger the login function. For example, I have a page that I allow localhost only use from that just gives me a list of users and I pick which one I want to be and it just logs me in-never see a password field. My best recommendation is explore the code a bit, and find out what solution you would find the best to implement.

I believe there is a function called usernameExists, if so, you could foreach and loop from 0 with a concatenation of the date and go until it returns false. Although relatively light queries-just remember that could be a lot of queries.

Thank you,
Brandin.

[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.

[Brandin]

My recommendation is to always set username to the users email to avoid username issues (if you didn't say that already)

[Moomintroll]

You are right. DB altered.