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();
| improve this answer | |
  • 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
0

Joomla should be able to understand this... You must let Joomla's query building functions render the "child query"/subquery before placing it inside of the "parent query"/main query.

Here are some other answers of mine that may prove helpful while writing/testing your own query:

Code: (untested)

$subquery = $db->getQuery(true)
    ->select("cg.name")
    ->from("#__community_groups AS cg")
    ->where("cg.ownerid = gl.id");

$query = $db->getQuery(true)
    ->update("#__gruposlocales AS gl")
    ->set("gl.name = ({$subquery})");

$db->setQuery($query);
$db->execute();

I am deliberately omitting all quoting techniques in my answer because they are unnecessary fir stability/security in this snippet and would only serve to make the snippet more difficult to read.

As for discussions on how/when/why to quote query entities, how to view the rendered query with dump(), how to write a try{}catch{} block to aid in debugging, and how to check for affected rows -- please see my other mysql-tagged answers in this community.

| improve this answer | |

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.