[SOLVED] Calc Element SELECT question

[mediaateam]

I am using the calc element on a form. Once they make two dropdown selections (which I'm watching in the calc element), I need it to go grab two dates off of two other tables, then do a difference calculation. I know the php calculation works. Here it is with supplied dates:

$datetime1 = new DateTime('2 Jan 2008');
$datetime2 = new DateTime('5 July 2012');
$interval = $datetime1->diff($datetime2);
echo $interval->format('%y years %m months and %d days');

Here's how I tried to modify it:

$dob = SELECT date_of_birth FROM afab_student WHERE id={afab_application___student_raw};
$yrstart = SELECT day_to_calc_age FROM afab_school_year WHERE id={afab_application___school_year_raw};
$datetime1 = $dob;
$datetime2 = $yrstart;
$interval = $datetime1->diff($datetime2);
return $interval->format('%y years %m months and %d days');

Is there some trick to using SELECT statements in the calc element? Because if I try to just do this, I don't get anything:

$dob = SELECT date_of_birth FROM afab_student WHERE id={afab_application___student_raw};
return $dob;

 

[troester]

1. $dob = SELECT... is no valid php statement, must be at least a string "SELECT..."
2. You should set Joomla error reporting to maximum during development to get error messages.

Here is how to access the DB inside of Joomla
http://fabrikar.com/forums/index.php?wiki/common-php-tasks/#select
Additional remark: you should always quote placeholders to prevent php errors if they are empty.

So your code should be something like

$studid = '{afab_application___student_raw}';
 
$db = JFactory::getDbo();
 
$query = $db->getQuery(true);
$query
    ->select('date_of_birth')
    ->from('afab_student')
    ->where('id = ' . $db->quote($studid));
 
$db->setQuery($query);
$dob = $db->loadResult();
 
$schoolyear = '{afab_application___school_year_raw}';
$query = $db->getQuery(true);
$query
    ->select('day_to_calc_age')
    ->from('afab_school_year')
    ->where('id = ' . $db->quote($schoolyear));
 
$db->setQuery($query);
$yrstart = $db->loadResult();
...

 

[mediaateam]

Troester - Thank you! That allowed me to retrieve those dates I needed! I had to change the difference calculation though, the other way just blanked out my form. Not quite sure why, but I found another method and it works like a charm.
Here's my final code in case it helps someone else:

$studid = '{afab_application___student_raw}';
 
$db = JFactory::getDbo();
 
$query = $db->getQuery(true);
$query
    ->select('date_of_birth')
    ->from('afab_student')
    ->where('id = ' . $db->quote($studid));
 
$db->setQuery($query);
$dob = $db->loadResult();
 
$schoolyear = '{afab_application___school_year_raw}';
$query = $db->getQuery(true);
$query
    ->select('day_to_calc_age')
    ->from('afab_school_year')
    ->where('id = ' . $db->quote($schoolyear));
 
$db->setQuery($query);
$yrstart = $db->loadResult();
 
$diff = abs(strtotime($dob) - strtotime($yrstart));
 
$years = floor($diff / (365*60*60*24));
$months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));
$days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24) / (60*60*24));
 
return sprintf("%d years, %d months, %d days", $years, $months, $days);