Page 1 of 1

formula in mysql

Posted: Wed Mar 08, 2006 6:38 am
by Dark_AngeL
hi

I was wondering if it is possible to add a formula in mysql?

like for example if i want to generate and automatic ID but this ID must contain lets say 1st 3 letters of the username and year he joined

Posted: Wed Mar 08, 2006 9:44 am
by feyd
it's best to do that in php. I'd also suggest not using that as the internal identifier of the user, but as a "nice" identifier for users' reference.

Posted: Wed Mar 08, 2006 1:20 pm
by josh
You could use substr and concat functions in mysql to do that, but then you have the issue of collisions that feyd hinted at. What are you going to do if bob and bobby both joined in the same year? If you want a solution that is like this here is what I would do

First when I generate bob's ident, i concate bob to 2006, i get bob_2006 for example, next user is bobby
bobby>bob .. then i concat it to 2006 and get bob_2006, I then check the table and this already exists, let's say there's one occurance so I add a 2 onto the end and end up with:
bob_2006_2

I use an auto_increment primary key on every table though, and if I need a secondary identifier such as this I'll add it is a unique key (to make sure a collision can never occur under any condition)