[SOLVED] Email Template : Query to display values from other table + Solution

[marcq]

Happy New Year !

Hi,

I'm creating my first email template (please see EMAILTEMPLATECONF.txt attached) which works almost fine, when I'm displaying values with {placeholders}.

I'm now trying to creating a query in order to display a value from another table :

$candidateid = JRequest::getVar('gprh_fabrik_user_enrollment___id');
$db = JFactory::getDbo();
   $query = $db->getQuery(true);
   $query
      ->select('gprh_fabrik_user_enrollment.id, gprh_fabrik_user_training.documents_a_fournir')
      ->from('gprh_fabrik_user_enrollment')
      ->leftJoin('gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id')
      ->leftJoin('gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id')
      ->leftJoin('gprh_fabrik_user_training ON gprh_fabrik_user_training_124_repeat.parent_id = gprh_fabrik_user_training.id')
      ->having('gprh_fabrik_user_enrollment.id = ' . $db->quote($candidateid));
   $db->setQuery($query);
   // var_dump((string)$query);
   $docscandidat = $db->loadResult();

I'm trying to display the "gprh_fabrik_user_training.documents_a_fournir" value with :

<p><span style="font-family: arial, helvetica, sans-serif; font-size: 11pt;"><?php echo $docscandidat; ?></span></p>

Instead of displaying some text, the query is displaying the "gprh_fabrik_user_enrollment.id" value "80" (Please see Confirmation d'inscription.pdf attached, second paragraph following "PROCEDURE DE VALIDATION DE VOTRE INSCRIPTION").

Would appreciate if someone could explain me why and how to solve this.

Thanks a lot in advance.

Cheers,

Marc

 

[cheesegrits]

You are using loadResult() ...

https://docs.joomla.org/Selecting_data_using_JDatabase#loadResult.28.29

... which only returns the first field of the first row in the selection. As you don't need the id, just don't specify it in the select().

The alternative would be to use something like loadObject(), and access whatever fields you then need from the returned object ...

$row = $db->loadObject();
$docscandidat = $row->documents_a_fournir;

-- hugh

 

[marcq]

Thank you Hugh,

It is working and returning one value.

Now I would like to display all value that belong to a specific "gprh_fabrik_user_enrollment___id" since if the candidate have enrolled for 2 trainings (gprh_fabrik_user_enrollment_repeat_choix_formation), we will also have 2 "documents ? fournir" (gprh_fabrik_user_training.documents_a_fournir), one for each training.

I guess I should use the Single Column Results "Option" in order to display all the records belonging to this specific ID, so I tried to adapt my query :

<?php
$candidateid = JRequest::getVar('gprh_fabrik_user_enrollment___id');
$db = JFactory::getDbo();
   $query = $db->getQuery(true);
   $query
      ->select (array('gprh_fabrik_user_training.documents_a_fournir'))   
      ->from('gprh_fabrik_user_training')
      ->leftJoin('gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id')
      ->leftJoin('gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id')
      ->leftJoin('gprh_fabrik_user_training ON gprh_fabrik_user_training_124_repeat.parent_id = gprh_fabrik_user_training.id')
      ->having('gprh_fabrik_user_enrollment.id = ' . $db->quote($candidateid));
   $db->setQuery($query);
   $column= $db->loadColumn();
   $docscandidat = $column->documents_a_fournir;
?>

The following error occurred :

Erreur
Not unique table/alias: 'gprh_fabrik_user_training' SQL=SELECT gprh_fabrik_user_training.documents_a_fournir FROM gprh_fabrik_user_training LEFT JOIN gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id LEFT JOIN gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id LEFT JOIN gprh_fabrik_user_training ON gprh_fabrik_user_training_124_repeat.parent_id = gprh_fabrik_user_training.id HAVING gprh_fabrik_user_enrollment.id = '86'

 

[marcq]

Solution to whom it might interest :

Ok it is perhaps not the state of the art, but I could solve it like this :

<?php
$candidateid = JRequest::getVar('gprh_fabrik_user_enrollment___id');
$db = JFactory::getDbo();
   $query = $db->getQuery(true);
   $query
      ->select (array('gprh_fabrik_user_enrollment.id', 'gprh_fabrik_user_training.documents_a_fournir'))  
      ->from('gprh_fabrik_user_enrollment')
      ->leftJoin('gprh_fabrik_user_enrollment_repeat_choix_formation ON gprh_fabrik_user_enrollment.id = gprh_fabrik_user_enrollment_repeat_choix_formation.parent_id')
      ->leftJoin('gprh_fabrik_user_training_124_repeat ON gprh_fabrik_user_enrollment_repeat_choix_formation.choix_formation = gprh_fabrik_user_training_124_repeat.id')
      ->leftJoin('gprh_fabrik_user_training ON gprh_fabrik_user_training_124_repeat.parent_id = gprh_fabrik_user_training.id')
      ->having('gprh_fabrik_user_enrollment.id = ' . $db->quote($candidateid));
   $db->setQuery($query);
   // $column= $db->loadColumn();
   // $docscandidat = $column->documents_a_fournir;
   $row = $db->loadObjectList();
   // $docscandidat = $row->documents_a_fournir;
   foreach ($row as $item)
   {
      echo $item->documents_a_fournir;
   }
?>

 

[cheesegrits]

The first version of that, with the "not a unique .." error was because you were trying to select from and join to the same table, without using an alias.

When you need to use a table more than once, you have to give it an alias, then use that alias instead of the table name for refencing any fields from it ...

$query->select('table1.foo, t3.bar')
->from('table1')
->leftJoin('table2 on table2.t1id = table1.id')
->leftJoin('table1 AS t3 on table2.something_else = t3.id')

-- hugh

 

[marcq]

The first version of that, with the "not a unique .." error was because you were trying to select from and join to the same table, without using an alias.

When you need to use a table more than once, you have to give it an alias, then use that alias instead of the table name for refencing any fields from it ...

$query->select('table1.foo, t3.bar')
->from('table1')
->leftJoin('table2 on table2.t1id = table1.id')
->leftJoin('table1 AS t3 on table2.something_else = t3.id')

-- hugh

Thank you for your explanations and your time, Marc