Complex auto increment primary key

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Complex auto increment primary key

Post by mwilliam » 2018-05-01 10:39

Example: 4004300359

first two numbers is unit1 number
3rd and 4th number is the month and day
last 0359 is autoincremented

How can I make that be my primary key on save to code1

Is this done through hooks or in appgini?

Could someone please help me on this one.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 08:38

Hi,

you can try this.

I created a table test, have the field ID being autoincremented and use the field code1 as primary key,

Code: Select all

DROP TABLE IF EXISTS test;
CREATE TABLE `test` (
  `id` int(6) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `code1` bigint NOT NULL,
  `unit1` int(11) NOT NULL,
  `data` varchar(50) COLLATE latin1_german1_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

ALTER TABLE test DROP PRIMARY key, ADD KEY(id);
ALTER TABLE `test` ADD PRIMARY KEY(`code1`);



DELIMITER $$
CREATE TRIGGER `comp_key` BEFORE INSERT ON `test` FOR EACH ROW BEGIN
DECLARE _max varchar(6);
SELECT lpad(COALESCE(MAX(id), 0)+1,4,'0') FROM test into _max;
set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);
END
$$
DELIMITER ;

INSERT INTO `test`(`unit1`, `data`) VALUES (666,'vvv');
INSERT INTO `test`(`unit1`, `data`) VALUES (777,'vvv')
will result in

id code1 unit1 data
1 66605030001 666 vvv
2 77705030002 777 vvv

Hope that helps.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 12:04

pböttcher,

Thank you so much for the help, is this put in a hooks file? also, the format I am looking for is this.

Example code1: 4004300359

40 is unit1
04 is the month (so if the month is May, it will be 05 or if its June, it will be 06)
30 is the day of the month.
0359 is auto incremented, EX. 0359 0360 0361.

I have racked my brain till I am about to explode LOL!

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 12:40

Hi,

this would go into the database definition as a trigger. the table I created was just an example. you would need to replace the fieldnames with yours.

concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max)
will create your uniq key.

in the example I create above you would add e.g. via insert
INSERT INTO `test`(`unit1`, `data`) VALUES (40,'vvv')

This will create your code1 as 4005030003

where
40 = the value of unit1 (see INSERT statement)
05 = month (we have may, so it generates 05)
03 = day (we are the 3'rd today :-) )
0003 = autoincrement value of the ID field (which is the autoincrement of this table, for this instert 3, see above examples for id=1 and id=2)

So basically you need to add this trigger ( I adjusted the code to directly retrieve the autoincrement value )

Code: Select all

DELIMITER $$
CREATE TRIGGER `comp_key` BEFORE INSERT ON `test` FOR EACH ROW BEGIN
DECLARE _max varchar(6);
SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE table_name = 'test' into _max;
set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);
END
$$
DELIMITER ;
to your table.

be aware that there is a little chance that, if 2 people update the table at the same time, this might fail as the SELECT statement above would retrieve the max id of your current autoincremented id. But if meanwhile another insert happens, the id could already be incremented and hence you would get an incorrect result.

regards
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 13:42

When you mean trigger, is this in mysql or hooks?

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 13:44

Or.....tablename-dv.js file ?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 14:14

it is in mysql
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 14:34

So I have a database called lcso
Table called accident
unit1
code1 (primary)

DELIMITER $$
CREATE TRIGGER `comp_key` BEFORE INSERT ON `accident` FOR EACH ROW BEGIN
DECLARE _max varchar(6);
SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE accident = 'code1' into _max;
set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);
END
$$
DELIMITER ;

Would that be correct

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 15:10

Also, failed to mention. the unit1 number is entered by the person, it could be 43 55 99 16 03 ...its just two numbers, so when they enter their unit number, it needs to be part of the Code1 when it saves.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 15:53

Hi,

yes that is what I assumed. UNIT 1 is retrieve when the user clicks has entered the data into the form and saves it.

in the code you see it as new.unit1, this is the sql syntax to handle the actual value for that field.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 16:26

Error I get when I enter a new record.

Unknown column 'accident' in 'where clause'

Query:
insert into `accident` set `date1`='2018-5-3', `time1`='3:35:00', `unit1`='66', `days`='Monday', `type`='45', `parties`='Smith vs Douglas', `location`='123 Mains Street London, Ky'

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 16:46

can you post the trigger you created, also the table definition of accident.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 16:54

DELIMITER $$
CREATE TRIGGER `comp_key` BEFORE INSERT ON `accident` FOR EACH ROW BEGIN
DECLARE _max varchar(6);
SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE accident = 'code1' into _max;
set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);
END
$$
DELIMITER ;
Attachments
test2.png
test2.png (121.48 KiB) Viewed 9781 times
Untitled.jpg
Untitled.jpg (163.17 KiB) Viewed 9781 times

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 17:03

SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE accident = 'code1' into _max;

needs to be

SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE table-name = 'accident' into _max;
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 17:07

This is the error I get when I do it.

DELIMITER $$
CREATE TRIGGER `comp_key` BEFORE INSERT ON `accident` FOR EACH ROW BEGIN
DECLARE _max varchar(6);
SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE table-name = 'accident' into _max;
set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);
END
$$
DELIMITER ;
Attachments
Untitled.png
Untitled.png (157.08 KiB) Viewed 9779 times

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 17:10

sorry typo, it needs to be table_name instead of table-name

SELECT lpad(AUTO_INCREMENT ,4,'0') FROM information_schema.tables WHERE table_name = 'accident' into _max;
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 18:39

Out of range value for column 'code1' at row 1

Query:
insert into `accident` set `date1`='2018-5-3', `time1`='1:25:00', `unit1`='77', `days`='Wednesday', `type`='46', `parties`='Smith vs Evans', `location`='1802 HWY'

The above info is displayed because you are currently signed in as the super admin. Other users won't see this.Back

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 18:58

Hi,
where is your autoincremented "ID" field? In the picture I see that code1 is your primary key, but is that also your autoincrement?
if this is the case, the above will not work.
can you post a in phpMyAdmin a

DESCRIBE accident

to see you table definitions in MYSQL.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 19:10

pböttcher you are the best! I figured it out, it was Big Integ, I set them to that, and it worked!

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 19:12

Original code: set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%m%d'),_max);

New code: set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%y%-'),_max);

Notice the "-" <---dash will that put that in the database?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 19:26

Hi,

what do you try to acheive with the date_format of '%y%-'? I do not think that this will work. I thought you needed the month and day.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 19:29

It is, just trying to add a dash in there.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 19:37

if code1 is a big int, it cannot contain a - (dash).
if it is a varchar, you can do this. the %y will add the year ,not month or day. To add the - (dash) you do not need the second %

%y-

is enough.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mwilliam
Veteran Member
Posts: 32
Joined: 2018-03-31 09:03
Location: London, Kentucky

Re: Complex auto increment primary key

Post by mwilliam » 2018-05-03 20:13

I tried with varchar with auto increment, with primary, and appgini says it can only be a integ data types.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Complex auto increment primary key

Post by pbottcher » 2018-05-03 20:23

sure autoincrement can only be an int type.
So does it work for you now?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

Post Reply