Solved - Preventing Duplicates Entries per User

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
khouse
Posts: 11
Joined: 2019-05-12 14:34

Solved - Preventing Duplicates Entries per User

Post by khouse » 2020-02-14 20:40

:idea: I needed to limit user records to 10 and prevent duplicate entries for that specific user. Here's how I modified the hooks file for the table in question:

Code: Select all

	function tableMain_before_insert(&$data, $memberInfo, &$args) {

		$mi = getMemberInfo();
		$recordCount=sqlvalue("SELECT count(1) FROM `membership_userrecords` where memberID='".$mi['username']."' and tableName='tableMain'");

		$lookupIDCompare = $data['lookupID'];
		$maintablename = 'tableMain';
		$countReturn = TRUE;

		// check the current value submitted and compare vs query results, or if query results > 0 then it's a duplicate.
		$duplicateCheck = sqlvalue("SELECT count(1) from lookupTable WHERE ID = $lookupIDCompare AND ID in (SELECT lookupID FROM membership_userrecords JOIN tableMain ON membership_userrecords.pkValue = tableMain.ID WHERE tableMain.ID IN (SELECT pkValue from membership_userrecords WHERE memberID='".$mi['username']."' AND tableName = '$maintablename' ))");
		if ($duplicateCheck > 0) {
		$countReturn = FALSE; }
		
		// limit entries to 10 
		if ($recordCount > 9) {
		$countReturn = FALSE; }

		return $countReturn;

		//	return TRUE; (default value)
	}
I still haven't figured out how to present custom error messages but at least it's working. If anybody else is stuck with this issue I hope this helps!

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

Re: Solved - Preventing Duplicates Entries per User

Post by pbottcher » 2020-02-14 22:41

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.

khouse
Posts: 11
Joined: 2019-05-12 14:34

Re: Solved - Preventing Duplicates Entries per User

Post by khouse » 2020-02-14 23:34

Thanks! I forgot to note that this fix is used when you have a lookup table and want to use the unique ID to check for a duplicate instead of the value string.

vsandoval
Posts: 9
Joined: 2023-01-14 15:51

Re: Solved - Preventing Duplicates Entries per User

Post by vsandoval » 2023-01-16 13:49

I have tried to implement that code in my application and I have not been able to make it work for me. Here is the file with the name of the fields. Let's see if someone can help me, I would appreciate it.

Thanks in advance.

********************************************************
Table = 'Vientres'
primary key = 'ID'
Field 'CODIGO' is the field that should not be repeated
*********************************************************

function Vientres_before_insert(&$data, $memberInfo, &$args) {

$mi = getMemberInfo();
$recordCount=sqlvalue("SELECT count(1) FROM `membership_userrecords` where memberID='".$mi['username']."' and tableName='Vientres'");

$lookupIDCompare = $data['ID'];
$maintablename = 'Vientres';
$countReturn = TRUE;

// check the current value submitted and compare vs query results, or if query results > 0 then it's a duplicate.
$duplicateCheck = sqlvalue("SELECT count(1) from Vientres WHERE Vientres.ID = $lookupIDCompare AND membership_userrecords.ID in (SELECT lookupID FROM membership_userrecords JOIN Vientres ON membership_userrecords.pkValue = Vientres.ID WHERE Vientres.ID IN (SELECT pkValue from membership_userrecords WHERE memberID='".$mi['username']."' AND tableName = '$maintablename' ))");
if ($duplicateCheck > 0) {
$countReturn = FALSE; }

return $countReturn;

// return TRUE; (default value)


}

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Solved - Preventing Duplicates Entries per User

Post by jsetzer » 2023-01-16 14:11

function Vientres_before_insert(&$data, $memberInfo, &$args) {

Without further investigation: I don't think this ID is available BEFORE insert

Code: Select all

$data['ID'];
You are using this after the following line:

Code: Select all

$lookupIDCompare = $data['ID'];
Did you check the value?

I strongly recommend var_dump()-ing variable values before using them unverified.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

vsandoval
Posts: 9
Joined: 2023-01-14 15:51

Re: Solved - Preventing Duplicates Entries per User

Post by vsandoval » 2023-01-24 03:33

I made some changes but it still doesn't work could you please help me

Table = 'Vientres'
primary key = 'ID'
Field 'CODIGO' is the field that should not be repeated

Code: Select all


function Vientres_before_insert(&$data, $memberInfo, &$args) {
	
		$mi = getMemberInfo();
		$recordCount=sqlvalue("SELECT count(1) FROM `membership_userrecords` where memberID='".$mi['username']."' and tableName='Vientres'");

		$lookupIDCompare = $data['CODIGO'];
		$maintablename = 'Vientres';
		$countReturn = TRUE;

		// check the current value submitted and compare vs query results, or if query results > 0 then it's a duplicate.
		$duplicateCheck = sqlvalue("SELECT count(1) from Vientres WHERE CODIGO = $lookupIDCompare AND CODIGO in (SELECT memberID FROM membership_userrecords JOIN Vientres ON membership_userrecords.pkValue = Vientres.ID WHERE Vientres.ID IN (SELECT pkValue from membership_userrecords WHERE memberID='".$mi['username']."' AND tableName = '$maintablename' ))");
		if ($duplicateCheck > 0) {
		$countReturn = FALSE; }
		
		return $countReturn;

		//	return TRUE; (default value)

	}


Post Reply