Db read to check duplicates in field1 OR in field2

Hello,

I added a Db Read action to a custom Subscription form, in order to check if the submitted email address OR the submitted Phone number have been already previously entered.

Then in the 'Fields' box of the Db Read action, I wrote: email,telefono (the db fields) and in my Condition Box I wrote the following code:

<?php return array('email' => $form->data['email'], 'telefono' => $form->data['telefono']); ?>

Now the check is working properly only when BOTH fields matches...

My question is, how can I change the above code to work when only 'email' OR 'telefono' OR both matches?

Thank you for your help. :-)
Piergiorgio

Hi Piergiorgio,

Please see this FAQ

Bob

Hi Bob,

Thank you for your answer.
I had already the chance to read the link you gave to me before to post my question and thought that the following code should have done the job:

<?php
return array (":model.column = 'value' OR model.column_2 = 'value_2'");
?>

That applied to my case should be:

<?php
return array (":email = $form->data['email'] OR telefono = $form->data['telefono']");
?>

Unfortunately it's not working...
I also tried other syntax but no luck.
Any suggestions?

Piergiorgio

Hi Piergiorgio,

Have you tried adding the model id as in the example so model_id.email = . . .

Bob

HI Bob,

Yes, i did.

<?php
return array (":verifica.email = $form->data['email'] OR verifica.telefono = $form->data['telefono']");
?>

It's opening an html page:
error 1064.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['email'] OR verifica.telefono = Array['telefono']' at line 1 SQL=SELECT `verifica`.`email` AS `verifica.email`, `verifica`.`telefono` AS `verifica.telefono` FROM `test_medicf_contatto_Stefy` AS `verifica` WHERE verifica.email = Array['email'] OR verifica.telefono = Array['telefono']

Piergiorgio

Hi pj1907,

There's a problem with the PHP syntax there where the $form->data values are added, and you need to quote the values. Please try

<?php
$dbo = \GCore\Models\MODEL_ID::getInstance()->dbo;
$email = $dbo->quote($form->data['email']);
$telefono = $dbo->quote($form->data['telefono']);
return array ( ":verifica.email = {$email} OR verifica.telefono = {$telefono} ");
?>

The extra {} should identify those parts as PHP variables and not plain strings.

Bob

Hi Bob,

Thank you again for your help.
With your new instruction, no more syntax error, but when I click on submit, I get back a blank screen and on the address bar I have:
IP_ADDRESS/FOLDER/index.php?option=com_chronoforms5&chronoform=Contatto_Stefy_admin&event=submit

(I replaced my ip and folder with IP_ADDRESS/FOLDER)

Piergiorgio

Hi Piergiorgio,

I think that you need to replace MODEL_ID with the model id you are using - I failed to say that.

Bob

Hi Bob,

I noticed that into the database, if I have an empty "email" or "telefono", they are loaded into the array invoked by:

{return array (":verifica.email = {$email} OR verifica.telefono = {$telefono}");}

So looks like that an empty 'email' or 'telefono' is accounted as it was a filled one, preventing to accept a further submission with another empty 'email' or 'telefono'.
Because the purpose of the full instruction is to check for duplicates but only if 'email' or 'telefono' are filled, I'm wondering if there is a way to exclude empty emails and/or telefonos when the array is loaded.
Something like {$email != ""} ?
I tried some syntax but no luck...

Thank you.
Piergiorgio

Hi Piergiorgio,

You need to alter the logic of the code to suit what you need to do

<?php
$dbo = \GCore\Models\MODEL_ID::getInstance()->dbo;
if ( empty($form->data['email']) && empty($form->data['telefono']) ) {
  // both values are empty
  return false;
}
if ( !empty($form->data['email']) && !empty($form->data['telefono']) ) { 
  // both values are set
  $email = $dbo->quote($form->data['email']);
  $telefono = $dbo->quote($form->data['telefono']);
  return array ( ":verifica.email = {$email} OR verifica.telefono = {$telefono} ");
} elseif if ( !empty($form->data['email']) ) ) {
  // email is set
  $email = $dbo->quote($form->data['email']);
  return array ( ":verifica.email = {$email} ");
} else {
  // telefono is set
  $telefono = $dbo->quote($form->data['telefono']);
  return array ( ":verifica.telefono = {$telefono} ");
}
?>

Bob

Hi Bob,

There probably was an error in your code because just with a copy and past (even putting verifica instead of MODEL_ID) the behavior was not as expected.
Anyway you pointed me in the right direction, thank you for that!
Here below my working code:

<?php
    $dbo = \GCore\Models\verifica::getInstance()->dbo;
    $email = $dbo->quote($form->data['email']);
    $telefono = $dbo->quote($form->data['telefono']);
    
    if ( empty($form->data['email']) && empty($form->data['telefono']) ) {
      // both values are empty
      return false;
    }
    elseif ( !empty($form->data['email']) && !empty($form->data['telefono']) ) { 
        // both values are set
        return array ( ":verifica.email = {$email} OR verifica.telefono = {$telefono} ");
    }
    elseif ( !empty($form->data['email']) && empty($form->data['telefono']) )  {
        // only email is set
        return array ( ":verifica.email = {$email} ");
    }
    elseif ( empty($form->data['email']) && !empty($form->data['telefono']) )  {
        // only telefono is set
        return array ( ":verifica.telefono = {$telefono} ");
    }
?> 

As next step I would set different error messages based on the 4 cases above... I'll try do to that. If you have any suggestion on that of course I'll appreciate it a lot :-)

I also noticed that, if just the 'telefono' is provided, I get this alert:
"Mailer Error: You must provide at least one recipient email address."
but the entry is correctly recorded into the database.
I think it's related to the fact that the field type email is expecting to be filled even if it's not mandatory...
I do not know if there is a way to avoid this.

Thank you again, Bob.
Piergiorgio

Hi Piergiorgio,

Sorry, I missed out a couple of 'quote' lines by mistake

$telefono = $dbo->quote($form->data['telefono']);

I've updated my post to include them.

The simplest way to show a message is probably to use Joomla! System messages

...
$app = \JFactory::getApplication();
$app->enqueueMessage('Some message here');
. . .

The Email error is probably because you are sending an email with a Dynamic To and there is no value for that set in the form data.

Bob

Thank you Bob,

I tested and can confirm what you said:
"The Email error is probably because you are sending an email with a Dynamic To and there is no value for that set in the form data."
In order for the sender to receive an email (without the above error on the screen), I added an event switcher action where on success an email is sent:
In the event switcher code box i added:

<?php
if(!empty($form->data['email'])){
return "success";
}
else
{
return "fail";
}
?>

It works great :-)

Need now to investigate as per your suggestion the use Joomla! System messages to show messages depending on the errors.

Thank you Bob.
Piergiorgio