Page 1 of 1
prevent it from accepting duplicate values with the whole row
Posted: 2020-06-25 13:36
by amyat
hi everybody...
i'm beginner of appgini user
i have one question about prevent it from accepting duplicate values with the whole row.
'unique' can prevent it but he prevent all data from column
my table have
column data will the same
but need prevent from the same the whole row data
not for 1 column
please help me
thank you
Re: prevent it from accepting duplicate values with the whole row
Posted: 2020-06-25 17:54
by pbottcher
Hi,
you cannot do that without coding.
You can use Javascript to check it in the front-end and/or use the hooks file (before_insert / before_update) to check it on the backend side.
Re: prevent it from accepting duplicate values with the whole row
Posted: 2020-06-25 18:50
by jsetzer
I agree with @pböttcher that you need additional programming in case you want some validation and user interface feedback before saving the record.
If you only need to ensure data integrity and don't care too much about UI, you can create a unique constraint on multiple columns on the database table directly.
I think this tutorial should help:
https://www.mysqltutorial.org/mysql-unique/
There is another thread here:
viewtopic.php?t=2759
Regards,
Jan
Re: prevent it from accepting duplicate values with the whole row
Posted: 2020-06-26 12:30
by onoehring
Hi amyat,
I also totally agree with pbötcher and Jan.
To do what you want, you will need to run some SQL in the /hooks/tablename.php -> before_update and before_insert functions. This SQL could count all rows that have the same value as the "new" (or update) record.
Something like (not tested)
Code: Select all
$count = sqlValue ("SELECT COUNT(*) FROM yourTable WHERE field1 = `field1RecordValue` AND field2 = `field2RecordValue` ... );
if ($count > 0){
return FALSE;
} else {
return TRUE;
}
You will need to investigate in some SQL and PHP knowledge, but you should be able to handle your initial request.
Olaf
Re: prevent it from accepting duplicate values with the whole row
Posted: 2020-06-27 17:10
by amyat
thanks to all for helping me...
hi amyat,
i used ur code in my before_insert function.
but i don't get any effect why i dont know.
field1='field1RecordValue'
so i wrote with my column name 'year=yearRecordValue'
is wrong?
Code: Select all
function years_before_insert(&$data, $memberInfo, &$args) {
$count=sqlValue ("SELECT COUNT(*) FROM years WHERE year = `yearRecordValue` AND academic_year = `academic_yearRecordValue` ");
if($count>0){
return FALSE;
}
else
{
return TRUE;
}
}
everybody can help me
i trying to understand
sry
Re: prevent it from accepting duplicate values with the whole row
Posted: 2020-06-27 19:20
by pbottcher
Hi,
you comparison is not getting to where you want it
year = `yearRecordValue`
should look something like
year = {$data['FIELDNAMEOFTHEYEARVALUE']}
But without knowing the datatype or what names you use it is difficult to help further