Obtain the value of a field

[RobertG]

Hi,

In a group I have an element calc '{hr5i3_fb_stay___arrival_date}' that is filled by another element date. No problem here.

I need to calculate in a calc element '{hr5i3_fb_stay___id_week}' a value with the date, searching the date of that element in a table with PHP and a SQL request, .

If I use in the PHP "more" details tab :

return '{hr5i3_fb_stay___arrival_date}';

I always get an empty value (or zero if I add (int)), not the date displayed in the arrival_date element.

What is my error?

 

[RobertG]

I found: I didn't use Ajax and observe the date field, then calculate on load and repeat.
It works now.

 

[RobertG]

I need to use that field in a repeatable group and calculate the week number from another table. I need to compare the dates of each week to get the right value, I thoink it will not be too hard.
The last column in the first image needs to be 708 and 709 (I can calculate the 708 value from 2023-07-09) but actually the test date value is the same instead of 2023-07-09 and 2023-07-16 (first day of each booked week).
But the value of that "id" of week element is not incremented fos the second, third... rows. How could I do?

 

[RobertG]

I have a lot of difficulties with dates.
I created this PHP code and I get the right date but now, I can't get the right query. That one give me "11" as result while the value is not in the table.

date_default_timezone_set('UTC');

$date='{hr5i3_fb_stay___arrival_date}';
$wd_date = date("w",strtotime($date));

if ($wd_date == 6) {
  $my_Date = date('Y-m-d',strtotime($date . "+ 1 day"));
/*return date("Y-m-d",$date);*/
} else {
  $my_Date = date('Y-m-d', strtotime($date));
}

$myDb = Joomla\CMS\Factory::getContainer()->get('DatabaseDriver');
$query = $myDb
    ->getQuery(true)
    ->select ('id')
    ->from($myDb->quoteName('hr5i3_week'))
    ->where ('date_starts = ') . strtotime($my_Date);
return $myDb->loadResult();

I tried ->where ('date_starts = ') . $my_Date; but the result is always 11.
In the table only 1 row contains the date_starts searched value.
Is loadResult() the good manner to get the searched line?

 

[RobertG]

I finally found my mistake : I didn't use setQuery and my query was not well formed

$query = $myDb
    ->getQuery(true)
    ->select ('id')
    ->from($myDb->quoteName('hr5i3_week'))
    ->where ($myDb->quoteName('date_starts') . " = " . $myDb->quote($my_Date));
/*return ($my_Date);*/
$myDb->setQuery($query);
$id_week = $myDb->loadResult();
return $id_week;

Now the problem is : how to repeat and add 1 to the second row, 2 to the third, and so on...