2

I am in the same case discussed on https://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table, that is, I need to copy the value of a column from tableA to tableB based on a common column in each table.

I have used the suggested code inside of PHPMyAdmin, and it works perfectly:

UPDATE TableB 
SET TableB.value = (
    SELECT TableA.value 
    FROM TableA
    WHERE TableA.name = TableB.name
);

My problem is that I have been unable to translate this into something that I can use inside of my mod_xxx.php file. On the same Stack Overflow page, there is a suggested code that uses mysql_query and supposedly works, but Joomla doesn´t accept it. I know that any code has to be translated to Joomla's coding standards but I have spent three complete days trying it with no success.

I have tried the following code unsuccessfully:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query ->UPDATE ('#__gruposlocales')
       ->SET ('#__gruposlocales.name') =
             (SELECT ('#__community_groups.name')
              FROM ('#__community_groups')
              WHERE ('#__community_groups.ownerid = #__gruposlocales.id'));
$db->setQuery($query);
$result = $db->execute(); 

As you may guess, I am not knowledgeable on PHP coding, so I am in serious need of your help, please. My question is how can I translate the upper code into something that Joomla will take?

  • Are you trying to execute it as a raw sql query or build it using the query builder? You should be able to run it like this if you know that it works in phpmyadmin $db->setQuery($your_sql); $result = $db->execute(); – jamesgarrett Feb 12 '18 at 21:18
  • Thank you, James. I have tried the following code unsuccessfully: $db = JFactory::getDBO(); $query = $db->getQuery(true); $query ->UPDATE ('#__gruposlocales') ->SET ('#__gruposlocales.name') = (SELECT ('#__community_groups.name') FROM ('#__community_groups') WHERE ('#__community_groups.ownerid = #__gruposlocales.id')); $db->setQuery($query); $result = $db->execute(); – Pablo Castelo Feb 13 '18 at 3:20
1

You can do the following:

$db = JFactory::getDbo();
$sql = "UPDATE TableB SET TableB.value = (SELECT TableA.value FROM TableA WHERE TableA.name = TableB.name);
$db->setQuery($sql);
$db->execute();
  • The common column is id/ownerid, not name. And since this problem can be confusing, I'll try to clarify it a little further. TableB = josok_gruposlocales ; colum to update= name Table A= josok_community_groups ; colum to be copied= name Primary key= id ; foreign key= ownerid lab.latinface.com/images/photos/567/1/Problem-TableA&B.jpg TableB was updated from inside phpmyAdmin using the upper code that uses #__tablenames. But when I try to translate it into terms of $query and #__tablaname to be used inside of the mod_module.php I am creating, the update does not happen. – Pablo Castelo Feb 14 '18 at 19:20
  • 1
    mr octopus meant to write: "$db->setQuery($sql); " If you have a query that works in phpmyadmin then you can assign the raw query to $sql and it should work. – jamesgarrett Feb 14 '18 at 22:17
  • @jamesgarrett Correct! Sorry the query was updated. – itoctopus Feb 14 '18 at 22:24
  • Am I doing something wrong? $db = JFactory::getDbo(); $sql = "UPDATE josok_gruposlocales SET josok_gruposlocales.name = ( SELECT josok_community_groups.name FROM josok_community_groups WHERE josok_community_groups.ownerid = josok_gruposlocales.id )"; $db->setQuery($sql); $db->execute(); – Pablo Castelo Feb 14 '18 at 23:10
  • 1
    I know what happened. This is surely a beginner's mistake but telling about it could be useful for somebody else. The code is not executed as I save my mod_module.php file but when a user logs in and the mod_module is loaded. Thanks again and I am sorry for my lack of expertise. – Pablo Castelo Feb 16 '18 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.