Working Code Snippet: Validation that a Record exists in Table1 but NOT in Table2

[Peter_S]

Just a small snippet I spent an hour about yesterday and might save somebody that hour.

Note that in other SQL dialects you might use EXCEPT for this purpose but MySQL doesn't have it, so therefore the workaround.

The context is

- anonymous customer survey by invitation only
- Therefore NO registration
- Still want some security
- An administrator will send an invite to the customer with a link, a project code and a password
- Customer follows link, lands on form, logs in and on validation may complete survey
- Project Code is held in column 'id' of table 't1'
- It is also held in the column `project_code` of table `t2` (that table holds the customer's answers)

The validation for the field 'project_code' on the login form is:

"Validate if that code was actually generated by the admin but hasn't been used already" or
"Validate if code exists in t1.id but NOT in t2.project_code.

Here's the SQL Statement:

SELECT `id` 
FROM `t1` 
WHERE `id` >0  \\ or your criterion

AND NOT 
EXISTS ( 

SELECT `project_code` 
FROM `t2`

WHERE `project_code` >0 \\some criterion
AND `t1`.`id` = `t2`.`project_code` 
)

In Fabrik you create a php validation and drop the code right in:

$db =&JFactory::getDBO();
$db->setQuery(" SELECT `id` FROM `t1`
WHERE `id` = '$data'  \\compare with customer input
AND NOT EXISTS 
( SELECT `project_code` FROM `t2` 
WHERE `project_id` >0
AND `t1`.`id` = `t2`.`project_code`) ");
$exist = $db->loadresult();
return $exist;

The only slight disadvantage is that for the two possible reasons for validation fail there will always be the same error message which is not too user friendly. Therefore one might consider splitting the statement and create two php validations instead. You should be able to figure that one out.

Hope somebody finds it and finds it useful.

Thanks

Peter