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?

Improve this question
2
  • 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(); Commented Feb 12, 2018 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(); Commented Feb 13, 2018 at 3:20

2 Answers 2

Reset to default
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
8
  • 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. Commented Feb 14, 2018 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. Commented Feb 14, 2018 at 22:17
  • @jamesgarrett Correct! Sorry the query was updated. Commented Feb 14, 2018 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(); Commented Feb 14, 2018 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. Commented Feb 16, 2018 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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.