This topic is locked
[SOLVED]

 Event to check if date value exists before add

11/14/2017 9:22:36 PM
PHPRunner General questions
woodey2002 author

Hi Guys,
On an add page, I'm trying to create a before record added event that will check if the date a of record (chosen by the user) exists in another record.
If it does exist, return a message confirming this and stop the record being saved until the user selects another date.
I found this cool example here
https://xlinesoft.com/phprunner/docs/check_if_specific_record_exists.htm
I added this code to my before record added event.



//********** Check if specific record exists ************

$strSQLExists = "select * from dailydate where date='" . $values["date"] . "'";

$rsExists = db_query($strSQLExists);

$data=db_fetch_array($rsExists);

if($data)

{

// if record exists do something

$message = "date" . $values["date"] . " already exists."

return false;

}

else

{

// if dont exist do something else

}


Anyone got any ideas why refuses to compile? Is "date" a reserved word.
I very much appreciate your thoughts.
Best wishes,

J

romaldus 11/14/2017

Why don't you use "prevent duplicate values" option in visual editor?
https://xlinesoft.com/phprunner/docs/_edit_as__settings.htm

woodey2002 author 11/15/2017

Great Romaldus, that worked perfect.
Many thanks!

woodey2002 author 11/15/2017

Sorry Guys
I have just noticed that this solution works perfect except my project security settings throw a spanner in the works.
I have my table set for users to see and edit their own data only.
I user A adds a date to the the date field lets say the 15/01/2018 the database should allow that date to be saved as the security settings are set to users to see and edit their own data only.
A problem arises if user B who is part of a separate security group attempts to add 15/01/2018. The application won't allow User B to add the same value as user A, Even though they both users are in separate groups.
Therefore I am wondering if anyone has got any ideas how I can solve this problem I would very much appreciate it.
Many thanks

J

romaldus 11/16/2017

Of course it is easy to check this using before record added event but there is also an easier way. In your table, make sure date field is not a primary key. Than, create unique index contains username and date field.
https://www.sitepoint.com/use-unique-indexes-mysql-databases/

woodey2002 author 11/17/2017

Many thanks once again Romaldus!
I'm giving that a go now.
Cheers,

J