Complex auto increment primary key
Complex auto increment primary key
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.
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.
Re: Complex auto increment primary key
Hi,
you can try this.
I created a table test, have the field ID being autoincremented and use the field code1 as primary key,
will result in
id code1 unit1 data
1 66605030001 666 vvv
2 77705030002 777 vvv
Hope that helps.
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')
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.
Re: Complex auto increment primary key
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!
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!
Re: Complex auto increment primary key
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 )
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
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 ;
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.
Re: Complex auto increment primary key
When you mean trigger, is this in mysql or hooks?
Re: Complex auto increment primary key
Or.....tablename-dv.js file ?
Re: Complex auto increment primary key
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.
Re: Complex auto increment primary key
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
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
Re: Complex auto increment primary key
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.
Re: Complex auto increment primary key
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.
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.
Re: Complex auto increment primary key
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'
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'
Re: Complex auto increment primary key
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.
Re: Complex auto increment primary key
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 ;
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 (121.48 KiB) Viewed 9925 times
-
- Untitled.jpg (163.17 KiB) Viewed 9925 times
Re: Complex auto increment primary key
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;
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.
Re: Complex auto increment primary key
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 ;
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 (157.08 KiB) Viewed 9923 times
Re: Complex auto increment primary key
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;
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.
Re: Complex auto increment primary key
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
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
Re: Complex auto increment primary key
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.
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.
Re: Complex auto increment primary key
pböttcher you are the best! I figured it out, it was Big Integ, I set them to that, and it worked!
Re: Complex auto increment primary key
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?
New code: set new.code1 = concat(new.unit1, DATE_FORMAT(CURRENT_DATE(),'%y%-'),_max);
Notice the "-" <---dash will that put that in the database?
Re: Complex auto increment primary key
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.
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.
Re: Complex auto increment primary key
It is, just trying to add a dash in there.
Re: Complex auto increment primary key
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.
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.
Re: Complex auto increment primary key
I tried with varchar with auto increment, with primary, and appgini says it can only be a integ data types.
Re: Complex auto increment primary key
sure autoincrement can only be an int type.
So does it work for you now?
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.