Solved - Preventing Duplicates Entries per User

[khouse]

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!

[pböttcher]

Hi,

have a look at

viewtopic.php?f=7&t=1740#p10877

[khouse]

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]

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)


}

[jsetzer]

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.

[vsandoval]

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)

	}