Combining form input column data with just created id autoincrement data on the fly

Question

I have a table with cases in which on HTML form POST I create an INT case_id autoincrement number. In this table I would like to have a filed nas_br witch will be a varchar column in whom I would like to combine the ID field from form input + case_id just created on insert.

Example: When I create a new case I pass the ID filed of let's say number 3, case_id is created on an insert with number 4. I need to pass that info automatically to field nas_br so it would be 34. (I will add a dot in between but that's easy I guess).

My question is is this even possible to do on the fly because case_id is made on the insert, or I need separate process. How can I do this automatically on form input?

EDIT: Generated Columns are not supported in MARIADB on primary key/autoincrement.

$sql = "INSERT INTO PREDMETIF (ID, NAZIV, PROTUSTRANKA, SUD, SUDBROJ, PREDMET, VPS, grad, post_br, adresa, jb, upr_tj, sudac, datum, status) VALUES ('$STRANKAFID','$STRANKAFNAZ','$PROTUSTRANKA', '$SUD', '$SUDBROJ', '$PREDMET', '$VPS', '$grad', '$post_br', '$adresa', '$jb', '$upr_tj', '$sudac', '$datum', '$status')";
            $query = mysqli_query($conn, $sql);

EDIT As using Generated Columns is not supported on primary keys and AUTO_INCREMENT, found the other solution here: What is the alternative for generated column in MySQL 5.6. This creates a new table in Views and copy the table PREDMETIF with added column nas_br and does the adding. It's not in the original table but it gets to job done.

CREATE VIEW PREDMETIFView AS (
    SELECT ID, case_id, COALESCE(CONCAT(ID, '.', case_id)) AS nas_br
    FROM PREDMETIF 
)

you can use a [generated column](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html). — danblack, Feb 16 '20 at 22:07
This is very interesting I didn't know this exists. I am reading now docs on MySQL Generated Columns. Thanks for pointing it — ikiK, Feb 16 '20 at 22:16

score 2 · Answer 1 · answered Feb 16 '20 at 22:07

2

Instead of implemeting some (potentially cumbersome) logic to just generate a derived value, why not simply use a computed column?

If the column does not yet exist:

alter table predmetif 
    add column nas_br varchar(10) 
    generated always as concat(id, '.', case_id) stored;

Or if is already there:

alter table predmetif 
    modify column nas_br varchar(10) 
    generated always as concat(id, '.', case_id) stored;

Then, you can just leave that column apart when inserting into the table, and rest assured that your database will manage it under the hood.

Side note: mungling paramaters in the query string is bad practice (some would say: evil). You should seriously consider using paramaterized queries, to make your queries more efficient and safer.

answered Feb 16 '20 at 22:07

GMB

216,147
25
84
135

I'm getting an error on this code #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'concat(ID, '.', case_id) stored' at line 3. But this is very interesting I didn't know this exists. I am reading now docs on MySQL Generated Columns. Thanks for pointing it out. – ikiK Feb 16 '20 at 22:14
1

Its in MariaDB-10.2+ per [its documentation](https://mariadb.com/kb/en/library/generated-columns/) – danblack Feb 16 '20 at 22:19
Yeah I altred it a bit for mysql and now i get for GENERATED ALWAYS AS (concat(ID, '.', case_id)) STORED; Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `case_id` ... – ikiK Feb 16 '20 at 22:22
1

@ikiK: yes, you are correct... Digging into the [MariaDB documentation](https://mariadb.com/kb/en/generated-columns/), it states that, starting with version 10.2.6, *using the AUTO_INCREMENT column option is not supported when defining generated columns*. Sames goes in MySQL 5.7 apparently. – GMB Feb 16 '20 at 23:10
I posted a solution to my problem in the edit, I wouldn't have found it if you didn't point out Generated Columns, thank you for that and your time. – ikiK Feb 16 '20 at 23:12

ikiK · Accepted Answer · 2020-02-16T23:18:16.030

1

As using Generated Columns is not supported on primary keys and AUTO_INCREMENT, found the other solution here: What is the alternative for generated column in MySQL 5.6. This creates a new table in Views and copy the table PREDMETIF with added column nas_br and does the adding. It's not in the original table but it gets to job done.

CREATE VIEW PREDMETIFView AS (
    SELECT ID, case_id, COALESCE(CONCAT(ID, '.', case_id)) AS nas_br
    FROM PREDMETIF 
)

edited Feb 16 '20 at 23:18

answered Feb 16 '20 at 23:02

ikiK

6,328
4
20
40

1

Yes, this is a good workaround for autoincremented key being disallowed in generated columns. – GMB Feb 16 '20 at 23:10

Combining form input column data with just created id autoincrement data on the fly

2 Answers2