Please login or register.

Login with username, password and session length
Advanced search  

News:

You need/want an older version of sNews ? Download an older/unsupported version here.

Author Topic: Autogenerate ID  (Read 2997 times)

HvGo D CB

  • Newbie
  • *
  • Karma: 0
  • Posts: 12
Autogenerate ID
« on: November 22, 2006, 11:00:05 pm »

buddies i have a problem with a store procedure en sql...i want to autogenerate my id...
the problem is that dont let me insert into my table beacuse the id is primary key...

create procedure Insertar_Distritos
@nombre varchar(20) ,
@cod varchar(6) output
as
BEGIN TRANSACTION
declare @texto varchar(30),@var1 varchar(2),@var2 varchar(2),@num int,@val int,@numero int
set @texto=(select descrip from inserted)
set @var1= substring(@texto,1,1)
set @var2= substring(@texto,5,1)
if @num < 9 set @val=@val + '0'
set @num=@num +1
set @numero=right(ltrim(@val) + ltrim(str(@num)),4)

set @cod=@var1 + @var2 + @numero
insert into DISTRITO values(@cod,@nombre)
COMMIT TRANSACTION
Logged

Luka

  • Administrator
  • ULTIMATE member
  • ******
  • Karma: 36
  • Posts: 1717
    • http://www.snewscms.com
Autogenerate ID
« Reply #1 on: November 22, 2006, 11:09:23 pm »

OK let me see here...

What are you trying to achieve here? Sorry I missed the point of this database so I need some info.

If you want your id to be auto incremented, set auto increment.
Logged

Mika

  • Hero Member
  • *****
  • Karma: 9
  • Posts: 1377
    • http://www.ni5ni6.com/
Autogenerate ID
« Reply #2 on: November 23, 2006, 12:32:39 am »

Hi HvGo :)
If you're having difficulties regarding stored procedures in MySQL5, you may consider following this link or downloading a reference manual from the official mysql page
Logged
http://www.ni5ni6.com/ - Tutorials, Mods and How-To's about sNews CMS
sNews 1.6 Developers Edition - commented sNews 1.6 version

HvGo D CB

  • Newbie
  • *
  • Karma: 0
  • Posts: 12
Autogenerate ID
« Reply #3 on: November 23, 2006, 07:02:37 am »

Hi

Mi table

example

id_dist  | nombre
-----------|--------
Auto       | San Juan
   

i want to get "S" and "J"...

set @var1= substring(@texto,1,1)
set @var2= substring(@texto,5,1)

then increment a number

set @num=@num +1

then insert into the table something like that:

id_dist    | nombre
------------|--------
SJ0001  |  San Juan



sorry for my english...
Logged

Mika

  • Hero Member
  • *****
  • Karma: 9
  • Posts: 1377
    • http://www.ni5ni6.com/
Autogenerate ID
« Reply #4 on: November 23, 2006, 08:30:42 am »

You're trying to create data type based on city name abbreviation and autoincremented number, right?

- example:
San Juan + autonumber
- would result with:
SJ0001

Am I correct? I can only provide you with a pure PHP solution because you haven't said what db version you're working on
Quote
# $string - value fetched from your db
$string = 'San Juan';
$string = explode(' ', $string);
foreach ($string as $value) {
   $abbreviation .= $value{0};
}
# returns first letters of all the words in the array (even if it's single word only)
//echo $abbreviation;
#------------------------
# $number - value fetched from your db or created with some other increment routine ($number = number +1)
$number = 1;
$number = sprintf("%04d", $number);
# returns numeric value in a format  000+number
//echo $number;
# final result prepared for the db
echo $abbreviation.$number;
?>
NOTE: you shouldn't rely on this datatype as unique ID because you might get into some troubles when moving/recreating/upgrading... your db
Logged
http://www.ni5ni6.com/ - Tutorials, Mods and How-To's about sNews CMS
sNews 1.6 Developers Edition - commented sNews 1.6 version