formula in mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dark_AngeL
Forum Commoner
Posts: 39
Joined: Tue Feb 21, 2006 5:16 am

formula in mysql

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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)
Post Reply