Print field from databasejoin element in Email

[BigStef]

Hi Fabrikars, I need some advice on how to process in this case...
I have 2 list :

• audition-session
• audition-inscription

In the audition-session liste i Have:

• name of the session, (field)
• date beginning of the session, (jdate)
• date ending of the session, (jdate)
• price of the session, (field)

In the audition-inscription I have:

• a preselect audition-session, (databasejoin)
• name of the susbscriber, (field)
• email of the subscriber, (field)

When the subsciber has finish sending the form he recieve an email with the info he sent.

I would like to have some info from the audition-session in this email.
If I put {auditions_inscriptions___session} in the email, i get the name of the session (this is how the databasejoin element is parameted).
But how to call the price {auditions_session___price} of the session and/or other information in THIS email ?

Thanks in advance for any advice

 

[troester]

With the placeholder you can only show information which is in the form.
You can

• set the dbjoin to use "or concat label" for showing more elements of audition-session record
• or use a php template where you can combine whatever informations via database queries etc.

 

[BigStef]

Hi troester, thanks for the advice.
I will try the PHP template which I'm already using.
I will probably come back to correct my query

 

[BigStef]

I have teested this Query in a calc element and it worked.
Now when I try it in my php template, the value is empty... Any one has an idea ?

<?php
$id ='{abs_auditions_inscriptions___session_raw}';

$db = FabrikWorker::GetDbo();
$query = $db->getQuery(true);
$query->select('prix');
$query->from($db->quoteName('asb_auditions_sessions'));
$query->where($db->quoteName('id').' = '.$db->quote($id));
$db->setQuery($query);
$prix = $db->loadResult();
return $prix; // I cut this part in my PHP Template

?>

 

[troester]

I think in a php template you can't use placeholders (only in HTML/article template or code fields).

Try with the examples given in the php form plugin

PHP form plugin

Introduction Sometimes you are going to need to run some specific code that is not provided for by one of the other form submission plug-ins. At this point the highly flexible Run PHP plug-in is a godsend. Options Process scripts - State where in the form submission process you would like...

fabrikar.com

e.g.
$regions = $formModel->formData['countries___regions'];
or
$formModel->data['table___element']

var_dump or dump to see what you get.

 

[BigStef]

Hi troester,
I've tried using placeholders in PHP templates because of this topic.
Maybe i did not understood everything there ? But When I tried this code in my PHP template (nothing to do here, but just to show that it works) :

<?php
// Prepare parent or actor's name
$nomParent = $this->data['abs_auditions_inscriptions___parent_nom'] ;
$prenomActeur = $this->data['abs_auditions_inscriptions___prenom'] ;
?>
<p style="font-family: Arial, sans-serif; font-size: 16px;">Bonjour
    <!-- Echo Parent's or actor's name -->
    <?php
    if(empty($nomParent)) {
        echo $prenomActeur;
    } else {
        echo $nomParent ;
    }
    ?>
</p>

It works...
So I looked at your answer and changed my code with this one :

<?php
$id = $this->data['abs_auditions_inscriptions___session_raw'];

$db = FabrikWorker::GetDbo();
$query = $db->getQuery(true);
$query->select('prix');
$query->from($db->quoteName('asb_auditions_sessions'));
$query->where($db->quoteName('id').' = '.$db->quote($id));
$db->setQuery($query);
$prix = $db->loadResult();

?>

If I make a var dump on $id, i get this in my email ->

Var_dump = array(1) { [0]=> string(1) "3" }

But I can't get the price with it :

<?php echo $prix ?>

is empty... Any advice ?

 

[achartier]

Based on the var_dump $id is an array, so maybe $db->quote($id[0])

 

[BigStef]

Hi @achartier and @troester
I have add a PHP plugin on my form.
For all my tests below i choose the location "getTopContent".

So... With this code :

$id ='{abs_auditions_inscriptions___session_raw}';

$db = FabrikWorker::GetDbo();
$query = $db->getQuery(true);
$query->select('prix');
$query->from($db->quoteName('asb_auditions_sessions'));
$query->where($db->quoteName('id').' = 3');
$db->setQuery($query);
$prix = $db->loadResult();

return '<div class="uk-text-large">ID de la session est <span class=" uk-text-bold uk-text-danger">'.$id.'</span> et le prix de la même session est : <span class=" uk-text-bold uk-text-success">'.$prix.'</div>';

I can get my Price - Hourra, but the ID is not dynamic here, I put it manually :

But as soon i try a dynamic solution, it is not working (though the same code work in CALC element).
So with this code

$id ='{abs_auditions_inscriptions___session_raw}';

$db = FabrikWorker::GetDbo();
$query = $db->getQuery(true);
$query->select('prix');
$query->from($db->quoteName('asb_auditions_sessions'));
$query->where($db->quoteName('id').' = '.$db->quote($id));
$db->setQuery($query);
$prix = $db->loadResult();

return '<div class="uk-text-large">ID de la session est <span class=" uk-text-bold uk-text-danger">'.$id.'</span> et le prix de la même session est : <span class=" uk-text-bold uk-text-success">'.$prix.'</div>';

or this one :

$id ='{abs_auditions_inscriptions___session_raw}';

$db = FabrikWorker::GetDbo();
$query = $db->getQuery(true);
$query->select('prix');
$query->from($db->quoteName('asb_auditions_sessions'));
$query->where($db->quoteName('id').' = '.$db->quote($id[0]));
$db->setQuery($query);
$prix = $db->loadResult();

return '<div class="uk-text-large">ID de la session est <span class=" uk-text-bold uk-text-danger">'.$id.'</span> et le prix de la même session est : <span class=" uk-text-bold uk-text-success">'.$prix.'</div>';

I'm now sure there's an issue in my DBquery but can't find which one. If anyone would have a suggestion ?

 

[wezetel]

Did you try:

$query->where($db->quoteName('id').' = '.$id);

 

[troester]

var_dump/dump your variables to see what you get and have a look at the string length.
I assume it's showing something like $id= "3" string(42).

There are places where you can't use element placeholders in php code because the replacement is only done afterwards.
So I assume in the php code (query and return string) it's always ....'{abs_auditions_inscriptions___session_raw}'..., so $prix is not found but the id is replaced afterwards.
(May be very confusing, I had to learn it, too.)

https://fabrikar.com/forums/index.php?wiki/php-form-plugin/#accessing-form-data-8203

 

[BigStef]

Hi @wezetel - No sorry, I get an 1064 error with that one
Hi @troester - You are right : When a I do a var_dump on $id assigned with :

$id ='{abs_auditions_inscriptions___session_raw}';

I get "string(42) "3""
And when I do a var_dump on $id assigned with :

$id = $formModel->getElementData('abs_auditions_inscriptions___session', true);

I get "string(0) """
I'm a bit lost and open to any advice

 

[troester]

Try the other options for accessing form data

To quickly see the structure of your form's data set this as your code:
PHP:

echo "<pre>";print_r($formModel->data);exit;
or
echo "<pre>";print_r($formModel->formData);exit;
or
echo "<pre>";print_r($data);exit;

....
$formModel->_data; // for getTopContent, getBottomContent, getEndContent - use full element names: 'table___element'
....
I would use var_dump instead of print_r.

Sometimes you can also use just $data (don't ask...)

 

[wezetel]

Another try: cast your $id to an integer:
$id =(int)'{abs_auditions_inscriptions___session_raw}';
and use my propsed staement for the query