[Solved] Calculation with a value from another table

[georgie]

Hi

Please, I would, in a table A, to do a calcul using a value from another table B.
I already recovered in the table A the id of the table B.

Is it possible ?

Maybe is it necessary to recover first the simple value from table B, in a field in the table A ; and then to do the calculation in a another field in table A ? But I do not know why.

Maybe it will be better do do this in a php form plugin ?

What do you think about ? Thanks you for all advices.

 

[cheesegrits]

You can do it either in a calc or a submission plugin.

In a calc ...

$tablebId = '{tablea___tablebid_raw}';
if (!empty($tablebId)) {
   $myDb = JFactory::getDbo();
   $myQuery = $myDb->getQuery();  
   $myQuery->select('yourfield')->from('tableb')->where('id = ' . $myDb->quote($tablebId));
   $myDb->setQuery($myQuery);
   $myResult = $myDb->loadResult();
   // do your calculation on $myresult and return it here
}
else {
   return '';
}

-- hugh

 

[georgie]

Hell

Thanks but I have an error message:

500 Fabrik has generated an incorrect query for the list Bookings: <br />

Here's what I did exactly, in a calc element:

$tablebId = '{MyTableA___FieldIdTableB_raw}';

if (!empty($tablebId)) {
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery();
$myQuery->select('FieldTableBToUseInCalc')->from('TableB')->where('id = ' . $myDb->quote($tablebId));
$myDb->setQuery($myQuery);
$myResult = $myDb->loadResult();

// do your calculation on $myresult and return it here, for now just a test to display the value from the concerned field, to use in calculation
return  $myResult;
}

else {
   return '';
}

Anything Wrong?

THX

 

[troester]

Are MyTableA___FieldIdTableB_raw, FieldTableBToUseInCalc, TableB real names?

Enable fabrikdebug in Fabrik Options and append &fabrikdebug=1 to your list URL (?fabrikdebug=1 if it's the first parameter)
Then the error message should show the complete MySQL error.

 

[georgie]

Thanks, no it is not the real names.

Now, for test, I try this code (with real names):

$tableId = '{app_booking_rooms___room_raw}';

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery();
$myDb->setQuery($myQuery);
$myQuery->select('price_day')->from('app_rooms')->where('id = ' . $myDb->quote($tableId));
$myResult = $myDb->loadResult();

// do your calculation on $myresult and return it here, for now just a test to display the value from the concerned field
return  $myResult;

Indeed with debug, I have an error message more explicit:

<br /><br /><pre>SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous</pre>

But I do not understand why, because I have tried other syntaxes, without success.

What do you think about it please ?

 

[troester]

Try
$myQuery=$myDb->getQuery(true);

Maybe it's not clearing an old query without (true)

 

[cheesegrits]

Ooops, yes, that would be the problem.

-- hugh

 

[georgie]

I have added "(true)", I have now a new error message:

SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty

My actual code is this:

$tableId = '{app_booking_rooms___room_raw}';

if (!empty($tableId)) {

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myDb->setQuery($myQuery);
$myQuery->select('price_day')->from('app_rooms')->where('id = ' . $myDb->quote($tableId));
$myResult = $myDb->loadResult();

// do your calculation on $myresult and return it here, for now just a test to display the value from the concerned field
return  $myResult;

}

else {
   return '';
}

What do you think about?

 

[troester]

setQuery(... must be after select (...


gesendet mit Tapatalk

 

[georgie]

Hi

Thanks it works perfectly now, sorry...

Please some questions about yours remarks:

- In Fabrikdebug, what is the difference between &fabrikdebug=1 and ?fabrikdebug=1? What do you mean by "first parameter"?

- Why the "if_else" seems necessary in this case?

Thank you

 

[cheesegrits]

No difference, it's just whether that's the only (or first) query string argument on the URL. The URL and the query string has to be separate by a ?, then indivudal query string args separated by & ... so ... say you are using SEF, it would probably be the first arg ...

index.php/fabrik/form/2?fabrikdebug=1

... if not it'd probably not be the first arg ...

index.php?option=com_fabrik&view=form&formid=2&fabrikdebug=1

The if/else isn't strictly necessary, I'm just in the habit of checking to see if a given form input actually has any data in it before trying to use it, rather than firing off a query with an empty WHERE clause.

-- hugh

 

[georgie]

Hi

Thanks for tips about &fabrikdebug=1 or ?fabrikdebug=1, I understand (consequence and uses, not really the cause, that probably exceeds my competences).
Indeed I use SEF URL (later I wish to use the SEF Fabrik option about lists and forms, but later).
An example: http://demo-booking.com/bookings?fabrikdebug=1

Is this a good practice for a Fabrik website under construction, for future debugs?

Thank you for good practice about given form input and if/else !

### Fab...

 

[cheesegrits]

Yes.

Just make sure you turn Fabrik debug off before you make the site live.

During development, you might also want to set the error reporting in J!'s global settings to maximum, and enabled J! debugging. That will give you the most feedback possible about any lurking issues on your site.

-- hugh

 

[georgie]

OK thanks !
I will use it!