(SOLVED) Update field in another table on form submission

[mbeley]

I have this Contract list.
The Contract form is only an ID, a date, and a DB join to another form called Employment
The Employment form has a DB join to Candidates and Offers

When user submits one Contract, I want to update candidates__jobstatus to "Employed"

I've looked at the documentation (common PHP tasks) and forum, but I really can't figure out the syntax.
I don't know how to retrieve information from the form I just submitted.

Please help!
Thanks,
Mathieu

 

[cheesegrits]

Which table is 'offers'? Is that the "Employment" form/list?

If you could summarize your table structure a little more accurately, I'll give you the code you need to run in a PHP form plugin.

I tried to use your My Sites info to login to your site to get the details I need, but it tells me that user doesn't exist.

-- hugh

 

[mbeley]

I re-published your super user account (the one that's in My Sites).
But it may be a bit confusing. In this thread, I translated all the table names in English to make it easier for you.
Here it is with the Portuguese names:

Candidatos is the candidate list, Empregos is the job offer list
Emprego is the place where the user assigns one offer to one candidate.
Under Emprego, users can submit a Contracto (if the candidate gets the job).

Contracto has a database join Element: Emprego where contracto_emprego = emprego__id
Employment has a database join Element: Candidato where emprego__candidato = candidatos__id
Employment has a database join Element: Oferta where emprego__oferta = empregos__id

 

[cheesegrits]

OK, I'm in on the backend, and have a better idea of what the query needs to be ... but ... can you rewrite your "pseudo query" form the original post, using the actual table and field names? So I can sanity check and make sure I give you the right query? Much easier if I give you a query you can copy and paste, rather than changing names, which introduces a layer of potential errors.

-- hugh

 

[mbeley]

I will have to write several of these anyway, so I will need to fiddle with them.

Below is the query I was describing.
"contracto" is the table that's being submitted.

$query= UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id JOIN empregos ON emprego.oferta = empregos.id SET candidatos.situacao_laboral="Colocado" WHERE contracto.emprego=emprego.id AND contracto.resultado="Sim"

Thanks,
Mathieu

 

[mbeley]

Hugh, sounds like you forgot me on this one!

Another thread that happened in the meantime (http://fabrikar.com/forums/index.php?threads/fabrik-fields-to-variables-php.38504/) helped me make progress (or at least I believe so).

I am now using the following query, but it's not doing anything (not even generating any error, which surprises me):

$db = JFactory::getDbo();
$emprego = $formModel->getElementData('contract___emprego', true);
$resultado = $formModel->getElementData('contract___resultado', true);
$query = $db->getQuery(true);
$query= UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id JOIN empregos ON emprego.oferta = empregos.id SET candidatos.estatuto="Colocado" WHERE $emprego=emprego.id AND $resultado="Colocado"
$db->setQuery($query);
$found = (int) $db->execute();

 

[troester]

The query must be a string to be valid PHP.
$query= "UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id JOIN empregos ON emprego.oferta = empregos.id SET candidatos.estatuto='Colocado' WHERE $emprego=emprego.id AND $resultado='Colocado'";

But better use the recent syntax
http://fabrikar.com/forums/index.php?wiki/common-php-tasks/#update

To get error messages set Joomla error reporting to maximum.

 

[mbeley]

Thank you for your help Troester.
I simplified my query a bit, in order to figure out the syntax first (and later on I can re-add more joins or more conditions).
I'm having it run onBeforeCalculations

My query is now:

$db = JFactory::getDbo();
$emprego = $formModel->getElementData('contract___emprego', true);
$resultado = $formModel->getElementData('contract___resultado', true);
$query = $db->getQuery(true);
$query= "UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id SET candidatos.estatuto=$resultado WHERE $emprego=emprego.id";
$db->setQuery($query);
$db->execute();

I get the following error: (note how the variables have disapeared)
SQL=UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id SET candidatos.estatuto= WHERE =emprego.id

 

[mbeley]

I just noticed (from an old post at http://fabrikar.com/forums/index.php?threads/updating-a-field-on-a-different-db-table.27195/ ) that the syntax for variables should be '$variable' instead of $variable

So I have the following query:

$db = JFactory::getDbo();
$emprego = $formModel->getElementData('contract___emprego', true);
$resultado = $formModel->getElementData('contract___resultado', true);
$query = $db->getQuery(true);
$query= "UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id SET candidatos.estatuto = '$resultado' WHERE emprego.id = '$emprego'";
$db->setQuery($query);
$db->execute();

This time I do not get an error message (BTW Joomla error reporting was already on max since the beginning of this thread)
But still the update I want does not happen.

 

[troester]

For debugging add
var_dump($emprego,$resultado,$query);exit;
before $db->setQuery($query);

This will show you the variables.

 

[mbeley]

OK I went back to the query that does not return an error message, and added the debugging line
I'm getting this:
string(0) "" string(0) "" string(118) "UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id SET candidatos.estatuto = '' WHERE emprego.id = ''"

 

[mbeley]

It looks like the 2 variables that I'm retrieving from the submitted form are empty.

Which is strange, since:
- contract___emprego is a database join that's automatically filled by a "related table" add button
- contract___resultado is a mandatory field

Below is the list view of the record I created (the same event that showed the empty variables)

 

[mbeley]

I'm trying to use placeholders instead of getelementdata

$db = JFactory::getDbo();
$emprego = '{contract___emprego}'; 
$resultado = '{contract___resultado}';
$query = $db->getQuery(true);
$query= "UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id SET candidatos.estatuto = '$resultado' WHERE emprego.id = '$emprego'";
$db->setQuery($query);
$db->execute();

Again no error message
In debugging mode I'm getting:
string(20) "{contract___emprego}" string(22) "{contract___resultado}" string(160) "UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id SET candidatos.estatuto = '{contract___resultado}' WHERE emprego.id = '{contract___emprego}'"

 

[troester]

As long as $emprego is empty or does contain an "unreplaced" placeholder the query can't work.
Where are you running this code? In a php form plugin?
Can you post a screenshot of your settings?

 

[mbeley]

 

[troester]

$x='{table___element}';
$y=$formModel->getElementData('table___element', true);
are both working on my site with your settings (i.e. the var_dump is showing the text of the form field).
Are contract___emprego and contract___resultado full element names belonging to the form with this php plugin?

 

[mbeley]

The form is contracto and I was using contract! Silly me!
Anyway thanks a lot for your help, Troester!
Now I feel that I understand the syntax and will be able to make my project happen!

 

[mbeley]

In the end I'm using the following query:

$db = JFactory::getDbo();
$emprego = '{contracto___emprego}'; 
$resultado = '{contracto___resultado}';
$query = $db->getQuery(true);
$query= "UPDATE candidatos JOIN emprego ON emprego.candidato = candidatos.id SET candidatos.estatuto = '$resultado' WHERE emprego.id = '$emprego' ";
$db->setQuery($query);
$db->execute();

Should I try and use the recent syntax instead?