[SOLVED] Form PHP plugin: function to read and store data between two tables

[pier67]

I have two tables in separate db.
I have a fabrik form with a field plugin where users can store in the first table the id of a related record in the second table.
I need to set-up a php function in the module php plugin that on form submission will look (using submitted id) at a filename stored in the second table and set it as a filepath in the first table with a concat function.
I have tried to set this code, but nothing happened:

mysql_query("
      UPDATE db1.caduti
      SET db1.caduti.foto_ok =
        CONCAT('/images/joomgallery/originals/deceduti_sul_fronte_russo_3/', db2.xxx_joomgallery.imgfilename)
      WHERE db2.xxx_joomgallery.id = db1.caduti.id_joomgallery");

I think that I need to use value from module, because this way it will update all the records that match the id from the two tables. I need to update only the record that I am editing. Any help?

 

[cheesegrits]

Are these on different databases?

-- hugh

 

[pier67]

Yes, but on the same server.

 

[cheesegrits]

OK. Cross database joins only work if both database use the same credentials, so the username and password are the same for both of them.

Assuming that's the case, you could probably do ...

$galleryId = '{caduti___id_joomgallery_raw}';

if (!empty($galleryId)) {
   $myDb = JFactory::getDbo();
   $myQuery = "
      UPDATE db1.caduti
      SET db1.caduti.foto_ok =
        CONCAT('/images/joomgallery/originals/deceduti_sul_fronte_russo_3/', db2.xxx_joomgallery.imgfilename)
      WHERE db2.xxx_joomgallery.id = '$galleryId'
";
   $myDb->setQuery($myQuery);
   $myDb->execute();
}

I'm assuming db1 is the one your form is on, and that the full element name with the ID is caduti___id_joomgallery.

-- hugh

 

[pier67]

Thank you Hugh.
I have tried your code, but nothing happens. Maybe the problem is with db numbering as I think that db1 is not the one where the form is on. In my case db1 is the database where the id and the filepath and all the other data will be stored from form. But the form itself, is stored in the joomla system db that is db2. I have ckecked that both db are connected with the same user, so I presume they use the same credentials. Is there something more to look at? Thank you.

 

[pier67]

I'm still trying to set-up the php code.
My improvement was to get a working sql query.
I'll made a test with a value (2716) instead of $galleryID, and this is the working code:

   UPDATE db1.table1
   SET filepath = CONCAT("/path/", (SELECT imgfilename  FROM db2.tabele2  WHERE id=2716))
   WHERE  id_joomgallery = 2716

db1.table1

is the table to be updated and

db2.table2

is a table from joomla database.
But still, when i put the sql query in your php code and submit the form, no value is added to the database.

 

[cheesegrits]

Dump the query and see what your placeholder is getting set to.

var_dump($myQuery);exit;

... as the last line. Submit the form, and you should just get some debug output on an otherwise blank page.

-- hugh

 

[pier67]

It seems that the plugin isn't working at all. I have follow your steps, but no blank page nor debug output are appeared. I have tried also to activate fabrik debug in fabrik options, but still no way. Form is regularly updating data and it ends with a successful message. I'm using the form in a modal popup with aiax.

 

[pier67]

I have also inspected in mozilla console and there is a server error 500 on form submission.

 

[cheesegrits]

That usually means there's an error in your PHP.

In dev tools where you see the 500, if you look at the Network tab for that, and the Response, do you see an actual error message?

-- hugh

 

[pier67]

In the Network tab I can only see that origin is js xhr and in the Response i see an html code where I can see near the end that appear a generic error message (an error has occourred).

 

[cheesegrits]

Paste your exact PHP here.

-- hugh

 

[pier67]

$galleryId = '{caduti___id_joomgallery_raw}';
if (!empty($galleryId)) {
   $myDb = JFactory::getDbo();
   $myQuery = "
  UPDATE unirrit_caduti.caduti
    SET foto_ok= CONCAT("/images/joomgallery/originals/deceduti_sul_fronte_russo_3/",
    (SELECT imgfilename
        FROM unirrit_jos2.unr_joomgallery
        where id=$galleryId)) WHERE
    id_joomgallery = $galleryId
  ";
   $myDb->setQuery($myQuery);
   $myDb->execute();
};
var_dump($myQuery);exit;

 

[pier67]

In the meantime I've found on cpanel error log the following PHP errors:

PHP Warning: Division by zero in /home/mysite/public_html/plugins/fabrik_form/php/php.php(485) : eval()'d code on line 11, referer: http://www.mysite.it/administrator/index.php?option=com_fabrik&task=list.view&listid=20
PHP Warning: Division by zero in /home/mysite/public_html/plugins/fabrik_form/php/php.php(485) : eval()'d code on line 6, referer: http://www.mysite.it/administrator/index.php?option=com_fabrik&task=list.view&listid=20

 

[cheesegrits]

That's because you used double quotes around your file path, which terminates the quotes around the query itself, and the next character is a /. So it's trying to divide your query string up till the second double quote by the text of your file path.

Note that in the code I gave, I used single quotes around the file path, so the quotes didn't clash.

Three choices ...

1) Use single quotes within the main string.

2) Escape double quotes like \"

3) Use a "heredoc":

   $myQuery <<< EOT
  UPDATE unirrit_caduti.caduti
    SET foto_ok= CONCAT("/images/joomgallery/originals/deceduti_sul_fronte_russo_3/",
    (SELECT imgfilename
        FROM unirrit_jos2.unr_joomgallery
        where id=$galleryId)) WHERE
    id_joomgallery = $galleryId
EOT;

My prefered is 3 to avoid the problem entirely.

-- hugh

 

[pier67]

Ok, I have put your new code inside PHP and now the server error is gone, but there is still no update in db table as I expected. I check placeholder in the var_dump results and it seem to report the correct value.
Maybe I am still trying to update the wrong db?

 

[cheesegrits]

So if you uncomment that var_dump at the end, then copy and paste that query into something like phpMyAdmin, does it work?

-- hugh

 

[pier67]

If I put the update query in phpmyadmin and use real value instead of $galleryID variable, the query work as expected and in the db I can see that field updated.
So, for example, if I choose to update a a record where id_joomgallery is set to 2712, I can use this sql query:

UPDATE unirrit_caduti.caduti
    SET foto_ok= CONCAT("/images/joomgallery/originals/deceduti_sul_fronte_russo_3/",
    (SELECT imgfilename
        FROM unirrit_jos2.unr_joomgallery
        where id=2712)) WHERE
    id_joomgallery = 2712

The query update one row and in that row I can see the correct filepath in column foto_ok
But if I use the PHP code with that sql inside, there is no update. I have also changed the sql query for testing purpose, and tried to update a field on the same joomla db, but it also doesn't work.

 

[cheesegrits]

No, I mean do what I said ... uncomment the var_dump, and copy and paste the query the debug info shows.

-- hugh

 

[pier67]

Sorry, I misundersood. But I can't find any debug info to show. I am on Firebug > console > all > response, and on form submission appear only a post that says {"msg":"Record added\/updated","url":"index.php?option=com_fabrik&task=list.view&listid=20","baseRedirect":true,"rowid":"46119","suppressMsg":false}.
Fabrik debug mode is on. Did I miss something?