SQL in mail doesn't work correctly

puni_toice 22 Jan, 2012
Hello together

I have a form with a few SQL statements

Custom Code - 4Stellig -> OK
$db  =& JFactory::getDBO(); 

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

Custom code - Key generien -> OK
$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

E-Mail with Code in it -> NOK

$db =& JFactory::getDBO();
$query = "SELECT `key`FROM`jos_chronoforms_data_Urlaubsgutschein`WHERE `status` = 2";
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.
Max_admin 22 Jan, 2012
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.

ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
puni_toice 23 Jan, 2012
Just on last stupid question do I have to change the SQL for it ($form->data array)?
GreyHead 23 Jan, 2012
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.

This topic is locked and no more replies can be posted.