Calculated Field with Random Number

[hgarbe]

Hi everyone,

beforehand: appologies if this a dumb question or not best practice in coding. Still working my way through Mysql.
The idea or what I tried to achieve: getting a randomized Id with a query in a calculated field. Format something like Year-Random Alphanumeric

What I got is this:

Code: Select all

SELECT CONCAT(YEAR(CURDATE()),"-", UPPER(SUBSTR(MD5(RAND()),1,6))) AS random_num FROM data_protection_data WHERE "random_num" NOT IN ( SELECT random_id FROM TABLENAME) LIMIT 1

obviously not working. random_id is the calculted field, but also is not working in phpmyadmin. When I change "random_id" to "id" (primary key of the table) it is working. Which is cool but not helping as I want to check its uniquness against the random_id column.

Code: Select all

SELECT CONCAT(YEAR(CURDATE()),"-", UPPER(SUBSTR(MD5(RAND()),1,6))) AS random_num FROM data_protection_data WHERE "random_num" NOT IN ( SELECT id FROM TABLENAME) LIMIT 1

I got no clue why. Because its the primary key, unique or Auto Increment?

[jsetzer]

What about creating the initial random value in before-insert hook?

[hgarbe]

Would make most sense as otherwise the random value would be regenerated every time the tv oder dv is opened.
But other side: I didn't manage to get a result initially with phpmyadmin. I played around with the setting of the random_id column. Can the Default "null" of the column be reason i get an empty result?

[jsetzer]

(1) well, your SQL statement should update only records where random value is null (in your WHERE clause)

[hgarbe]

I thought "NOT IN ( SELECT random_id FROM TABLENAME)" would check if the random value is part of the results of this embedded query?
Only way I get this to work is on a column that is set to no "null" and has an Index. Trying it with a by default appgini generated column and it doesn't work.

[jsetzer]

Stupid question: are you sure you only update exactly 1 record?

Where YOUR_PK='%ID%'

[jsetzer]

Once again: what about using before_insert or after_insert hook?

[hgarbe]

First of all: thanks for taking time to still answer.
Yes I do. As you suggest I did include this in the hooks:

Code: Select all

function table1_before_insert(&$data, $memberInfo, &$args) {
	$IntRef=sqlValue("SELECT CONCAT(YEAR(CURDATE()),'-', UPPER(SUBSTR(MD5(RAND()),1,6))) AS random_num FROM table1 WHERE 'random_num' NOT IN (SELECT random_id FROM table1) LIMIT 1");
	$data['reference_field'] = $IntRef;
	
		return TRUE;
	}

So first keen to just update/insert one field. Problem is: depending on the setting for the column "random_num" this sql query in the hooks (same as running it in phpmyadmin) creates a result or not.

[jsetzer]

What about using PHP?

https://www.php.net/manual/de/function.uniqid.php

Create a unique string with prefix of current YEAR. Check if this already exists in database by using sqlValue function. Do this in a do () {} loop. If not exists, you have found a unique value. Theb update Data variable. Else repeat loop.

If your field is readonly, update database record directly by using sql- or sqlValue function in AFTER insert hook.