Member Avatar for ohgee

On a project I am currently working on, we are supposed to differentiate between different classes of users based off their IDs (which are 10 digits). For example, one group's IDs should all start with a 9 (marking them as belonging to that group) and the other groups should start with a 5. Currently, the IDs are being assigned via autoincrement in a MySQL table. I know you can set the index of the autoincrement, but in this case it would only take care of one group's IDs (ie: the 9 group, but not the 5 group). Given that I only have one field for IDs, how could I go about making each group have a different number to begin with? Is something that is possible in MySQL? If not, how should I go about doing this? My goal is to assign the IDs automatically, with no user input necessary.

Thanks!

  • 2 Contributors
  • 1 Reply
  • 139 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by javaAddict
Member Avatar for javaAddict

I don't remember the exact syntax for the auto-increment, but you can try this:
Instead of having the auto-increment part in the insert query:

ex: insert into table (id, ... ) values (NEXT_VAL, ... )


You can have a select that returns that auto-increment value:
ex: select NEXT_VAL from dual

Then take that value as a String, add the value "5" or "9" in front of it and use that at the insert query.

The "dual" keyword is used in Oracle. You can search the web for the equivalent in mySQL.
EDIT: The dual is also used in mySQL so you can use it at the select to get the incremented value

Can you also post the sql code you use for the auto-increment ?

Edited by javaAddict because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.