PHP update with join ?

[Incremental]

Hi,
When clicking Edit in a Contact list row, it opens a Form. In the onBeforeLoad() PHP code of the form, I'd like to perform an update with a joined table : Status table (contactsstatus) like this :
ID Status
1 New
2 Read
3 Closed

I want to update the Status field in Contact table (contacts) with the contactsstatus ID corresponding to Status = "Read"

I tried :

  $query
      -> update('contacts AS c')
      -> join('INNER','contactsstatus AS s ON s.Status = Read')
      -> set('c.Status = s.id')
      -> where(c.id = '. $IDinput);

but the code execution is stucked.
What could be the correct syntax ?

Thanks by advance

 

[pastvne]

I need to learn more about this object class but off the top of my head, it looks like you may be missing a ' mark in your where clause.

 

[Incremental]

Wow you're right !
I changed the code to

  $query
      -> update('contacts AS c')
      -> join('INNER','contactsstatus AS s ON s.Status = 'Read'')
      -> set('c.Status = s.id')
      -> where('c.id = '. $IDinput);

SQL execution is OK.

BUT I would like to have this code executed before the Form display.
onBeforeLoad() is not sufficient

Is there a better way than to set programatically the changed field in the form ?
for exemple 'refresh' the form ?
Thanks for ideas

 

[Incremental]

Friendly bump

 

[pastvne]

Afraid I don't know the answer to that part. Perhaps someone else more familiar with Fabrik events could help. Sorry.

 

[cheesegrits]

Can you be a little more specific about "onBeforeLoad is not sufficient"?

I'm not clear on what the problem is.

Are you saying that you are successfully modifying the data in your joined table, but the data for that join being displayed in the form isn't the updated values?

-- hugh

 

[cheesegrits]

If that's the case, I seem to recall that there is a "chicken and egg" situation with the form data and the onBeforeLoad hook, in that we have to load the form's data before we run that hook, because we need to do some ACL checks prior to running onBeforeLoad plugins which involve having access to the form data.

So I *think* if you modify the actual underlying table data in your plugin, you either have to modify the same data in $formModel->data accordingly, or ...

unset($formModel->data);

... as the last thing you do in your code, to force us to re-read it from the database when we call getData() just after the onBeforeLoad hook runs, rather than using the cached $formModel->data from before your plugin ran.

NOTE - if $formModel->data doesn't exist, try $model->data, I can't remember offhand which name is used when running that plugin. if in doubt, just ...

var_dump($formModel);exit;

... in your code, and see if it pukes up a huge data object, or NULL. If null, try $model.

-- hugh

 

[Incremental]

Thanks a lot Cheesegrit you're a god !
I used :

unset($formModel->_data);

and it works.

I just added it in the Wiki...

BUT I have a new problem now
If you look at my site : menu "Liste Contacts"
When my contact record is new, the List displays correctly the status 'New'

1. If I click on "Editer", the popup opens and execute the code. Status is set to 'Read' and correctly displayed in the popup --> OK.
2. If I close the popup with the red 'X', the List is not Refreshed and status is still set to 'New' (it is 'Read' in database !).
3. If I press F5 or CTRL-F5, the page seems to be reloaded, but the List is still displayed with 'New' !
4. If I select the menu 'Liste Contacts', the status is then correctly displayed to 'Read'.

How should I force a REAL refreshwhen closing the popup ?
Of course if I click on the popup 'Save' button, the display is correctly updated in the List.
Thanks for an idea...

 

[rob]

I went through steps 1-4 but for me step 4 the status is still set to 'new' - so perhaps its not updating the record at all when you open it?
Can you post the full PHP code you are using please?

 

[Incremental]

Hi Rob,
my code is

$IDinput = '{rowid}';  // Seulement l'ID courant.
$Status = '{contacts___Status}';
 
// Si Status = New le change en Read
if ($Status == "New") {
  // Get the db object
  $db = JFactory::getDbo();
 
  // Quote the values for security reasons
  $IDinput = $db->quote($IDinput);
 
  // Build the query ? Tables names = CASE SENSITIVES
  $query = $db->getQuery(true);
 
  $query
      -> update('contacts AS c')
      -> join('INNER','contactsstatus AS s ON s.Status = 'Read'')
      -> set('c.Status = s.id')
      -> where('c.id = '. $IDinput);
 
  echo "query : ".$query;
  $db->setQuery($query);
 
  // Run the query
  $found = (int) $db->LoadResult();
}

For me it's OK with step 4) : If I select the menu 'Liste Contacts', the status is then correctly displayed to 'Read'.
I would hope that closing the popup with 'X' would have the same effect as click on menu 'Liste Contacts'

 

[Incremental]

Friendly bump

 

[Incremental]

Friendly bump

 

[Incremental]

Friendly bump

 

[rob]

erm :S what are you expecting to see with an update AND a JOIN ? Pretty sure that isn't valid sql

 

[cheesegrits]

It is valid. I think the only thing you can't do in an update is use the target table in subquery.

http://dev.mysql.com/doc/refman/5.0/en/update.html

... says 'table_reference', which includes joins.

Here's an example of a stack Overflow Q&A about it:

http://stackoverflow.com/questions/15209414/mysql-update-join

-- hugh

 

[Incremental]

Do you mind if I add this code in the Wiki ?

 

[cheesegrits]

Go ahead, we encourage people to add to the wiki.

-- hugh

 

[Incremental]

OK it's done in :
http://fabrikar.com/forums/index.php?wiki/common-php-tasks