SQL in mail doesn't work correctly

Hello together

I have a form with a few SQL statements

DB SAVE -> OK
Custom Code - 4Stellig -> OK

<?php 
$db  =& JFactory::getDBO(); 

$query = "
UPDATE `jos_chronoforms_data_Urlaubsgutschein`
SET `key_01` = LPAD( `cf_id`, 4, '0' ), `status` = '1' where `status` IS NULL
"; 
$db->setQuery($query);   
$db->query(); ?>

Custom code - Key generien -> OK

<?php 
$db  =& JFactory::getDBO(); 
$query = "
UPDATE `jos_chronoforms_data_Urlaubsgutschein` SET `key` = CONCAT (SUBSTRING(cf_created,4,1), SUBSTRING(cf_created,7,1),SUBSTRING(cf_created,9,2), SUBSTRING(cf_uid,1,2), SUBSTRING(key_01,1,4)),  `key` = UPPER(`key`), `status` = 2 WHERE `status` = 1
"; 
$db->setQuery($query);   
$db->query(); 
?>

E-Mail with Code in it -> NOK

<?php 


$db =& JFactory::getDBO();
$query = "SELECT `key`FROM`jos_chronoforms_data_Urlaubsgutschein`WHERE `status` = 2";
$db->setQuery($query);
if($db->loadResult())
{
echo $db->loadResult();
}

The Problem is that it don't get the record. If I have a record in DB with status '2' then it takes these. But if I have no record with '2', only the right on, then i don't get the the key.

I want to send a Mail with the Key to the submitter?!

After this I have a last SQL for Updateing the status - this also work.

Where is my failure?

Is it also possible to work with the cf_uid? With this unique key I don#t need the status thing.

Hi puni_toice,

Just use your code in a "Custom code" action and set the result inside the $form->data array then use the curly brackets formula to get the value in the email.

And make sure that your Query runs ok by testing it in PhpMyAdmin first.

Regards,
Max

Just on last stupid question ...how do I have to change the SQL for it ($form->data array)?

Hi puni_toice,

If you want to generate a Unique ID then you might want to look at my Unique ID [GH] action that does this work for you.

Bob