Hi,
I am using appgini v 5.82
There is a table named (deposit_request)
In the table there are the following fields
id
delegate_name (lookupfield from employees table)& employees table Connected to users through view_membership_users table
order_status (optionlist New;; Under review;; Postponed;; Posting)
created_by (<%%creatorUsername%%>)
There is a group named (collectors) They can only modify or view the records they own
For reasons related to application analysis The record must be created automatically
So it will be executed in the hooks/ __global.php in function login_ok
function login_ok($memberInfo, &$args){
// First the following variables were created in function
$username = $memberInfo['username'];
$employee_id = sqlValue("select id from employees where username='{$username}'");
$cn='New';
$tbname='deposit_request';
$openreques_existsbefore = sqlValue("select count(1) from deposit_request where created_by='{$username}' and order_status='{$cn}'");
// If the user belongs to a group collectors.
if($memberInfo['group']=="collectors"){
// To create an automatic new record in the deposit_request table, there must be no records for the same user with the value of the order_status field equal to new.
if (!$openreques_existsbefore) {
sql("insert into deposit_request set delegate_name='{$employee_id}',order_status='{$cn}', created_by='{$username}'", $eo);
// To get LAST INSERT ID from inserted record
$last_id = sqlValue("select id from deposit_request where id = LAST_INSERT_ID() and created_by='{$username}' and order_status='{$cn}' ");
// To Set record owner
sql("insert into membership_userrecords set memberID='{$username}',tableName='{$tbname}', pkValue='{$last_id}'", $eo);
}else{
return false;
}
}
return '';
}
Now everything is fine and the record is created automatically with the condition after logging in without any problem
Users in the group collectors
After logging in
The app checks the user's previous records of the deposit_request table
If the condition is true in the order_status field
The app creates a new record automatically
If the condition is false in the order_status field
The app ignores the process
*Note if you want to implement the same method*
Now the user will see the record in the table and can work on it
But if the user changes the value of the order_status field and the value does not meet the condition in the variable $ cn
A new automatic record will not be created in the deposit_request table
Only after logging out of the app and logging in again
If you want to create a new automatic record in the deposit_request table
After the user changes the value of the order_status field, the value does not meet the condition in the variable $ cn
Without logging out of the app and then logging in again ,Additional procedures must be added in hooks
So it will be executed in the hooks/deposit_request.php in function after_update With a few simple changes
For example we can use set_record_owner function
*Now there is a question*
Is the LAST_INSERT_ID function return the first automatically generated record successfully inserted
For the same session ?
I am not sure of that I added two additional criteria in $last_id to avoid any errors until this is confirmed
Create automatic records with conditions for the user without the validity of the entry
Re: Create automatic records with conditions for the user without the validity of the entry
Hi,
please use the [ code ] ... [ /code ] to display your code. This would make your post(s) much easier to read.
I am not sure, If I understand your (very hidden ) question but as it says
here https://www.mysqltutorial.org/mysql-last_insert_id.aspx, I would expect the value is basically not only limited to the current session.
But, as you write something to the table and use last_insert directly after you should most often get the correct value. It is possible that another users loges in the at almost the exact same time, so that you would get the ID from that insert. Maybe this is what you mean by session. I am not sure if your second and third condition
will be applied though.
As an alternative you might use (SQL not tested)
This should imho return the last inserted record with the second and third condition, i.e. the record you probably want.
Olaf
please use the [ code ] ... [ /code ] to display your code. This would make your post(s) much easier to read.
I am not sure, If I understand your (very hidden ) question but as it says
The LAST_INSERT_ID() function returns the first automatically generated integer ( BIGINT UNSIGNED) successfully inserted for an AUTO_INCREMENT column.
here https://www.mysqltutorial.org/mysql-last_insert_id.aspx, I would expect the value is basically not only limited to the current session.
But, as you write something to the table and use last_insert directly after you should most often get the correct value. It is possible that another users loges in the at almost the exact same time, so that you would get the ID from that insert. Maybe this is what you mean by session. I am not sure if your second and third condition
Code: Select all
... and created_by='{$username}' and order_status='{$cn}' ");
As an alternative you might use (SQL not tested)
Code: Select all
$last_id = sqlValue("select MAX(id) from deposit_request WHERE created_by='{$username}' and order_status='{$cn}' GROUP BY created_by ");
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Create automatic records with conditions for the user without the validity of the entry
thanks for the helponoehring wrote: ↑2020-08-06 15:30Hi,
please use the [ code ] ... [ /code ] to display your code. This would make your post(s) much easier to read.
I am not sure, If I understand your (very hidden ) question but as it saysThe LAST_INSERT_ID() function returns the first automatically generated integer ( BIGINT UNSIGNED) successfully inserted for an AUTO_INCREMENT column.
here https://www.mysqltutorial.org/mysql-last_insert_id.aspx, I would expect the value is basically not only limited to the current session.
But, as you write something to the table and use last_insert directly after you should most often get the correct value. It is possible that another users loges in the at almost the exact same time, so that you would get the ID from that insert. Maybe this is what you mean by session. I am not sure if your second and third conditionwill be applied though.Code: Select all
... and created_by='{$username}' and order_status='{$cn}' ");
As an alternative you might use (SQL not tested)This should imho return the last inserted record with the second and third condition, i.e. the record you probably want.Code: Select all
$last_id = sqlValue("select MAX(id) from deposit_request WHERE created_by='{$username}' and order_status='{$cn}' GROUP BY created_by ");
Olaf
I mean
Returns the ID of the last inserted row from
Code: Select all
sql("insert into deposit_request set delegate_name='{$employee_id}',order_status='{$cn}', created_by='{$username}'", $eo);
Something similar to that
Code: Select all
INSERT INTO deposit_request set delegate_name='{$employee_id}',order_status='{$cn}', created_by='{$username}'INSERT INTO membership_userrecords set memberID='{$username}',tableName='{$tbname}', pkValue=LAST_INSERT_ID()
Re: Create automatic records with conditions for the user without the validity of the entry
Hi,
I think I do not understand the why and how of your request.
Why don't you want to use another query? In your original code you actually suggested using a query
I suggested the use the MAX() option/function in combination with your WHERE criteria.
Olaf
I think I do not understand the why and how of your request.
Why don't you want to use another query? In your original code you actually suggested using a query
Code: Select all
// To get LAST INSERT ID from inserted record
$last_id = sqlValue("select id from deposit_request where id = LAST_INSERT_ID() and created_by='{$username}' and order_status='{$cn}' ");
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view