We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Complex Upadate Query

    • 4971
    • 964 Posts
    charliez Reply #1, 17 years ago
    How do I change this:

    UPDATE A.value = 'InTeam'
FROM `modx_site_tmplvar_contentvalues` AS A, `modx_site_tmplvar_contentvalues` AS B 
WHERE A.`contentid` = B.`contentid` AND A.`tmplvarid`='27' AND B.`value`='Stevens' AND B.`tmplvarid` = '8'


    using the MODx DBAPI, to this:

    $modx->db->update("fields", "table_name", "where value");


    TIA
      Website: www.mercologia.com
      MODX Revo Tutorials:  www.modxperience.com

      MODX Professional Partner
      • 9207 ☆ A M B ☆
      • 2,475 Posts
      Everett Reply #2, 17 years ago
      My reading of
      http://svn.modxcms.com/docs/display/MODx096/update

      Says it’d be something like:
      $where = "'modx_site_tmplvar_contentvalues.`contentid` = modx_site_tmplvar_contentvalues.`contentid` AND modx_site_tmplvar_contentvalues.`tmplvarid`='27' AND modx_site_tmplvar_contentvalues.`value`='Stevens' AND modx_site_tmplvar_contentvalues.`tmplvarid` = '8'";

$rows_affected = $modx->db->update("A.value = 'InTeam'", "modx_site_tmplvar_contentvalues", $where);
        Hosting stuff: http://fireproofsocks.com/
        Coding stuff: http://craftsmancoding.com/
        Blog stuff: http://tipsfor.us/
        • 4971
        • 964 Posts
        charliez Reply #3, 17 years ago
        Yeah, I have read the new wiki and the old wiki and I have also used SELECT and UPDATE in
        the normal way... my problem is with the tmpvar_contentvalues table where all the info is
        in the Value field and so the need to use the same table as A.xxx and B.xxx.

        This works fine in the TVs using the @ binding but I dont know with the DBAPI

        The problem with your where is that for example...
        ’modx_site_tmplvar_contentvalues.`contentid` = modx_site_tmplvar_contentvalues.`contentid`
        is comparing exactly the same value and then you ask for A.value in $rows_affected...

        somehow we have to tell modx that we are using the same table as A.xxx and B.xxx...

        maybe is as simple as just transcribing the names just as they appear in the phpmyadmin query
        that works...
          Website: www.mercologia.com
          MODX Revo Tutorials:  www.modxperience.com

          MODX Professional Partner
          • 4172
          • 5,888 Posts
          Bruno17 Reply #4, 17 years ago
          I think in this case you have to use

          $modx->db->query("UPDATE A.value = 'InTeam'
FROM `modx_site_tmplvar_contentvalues` AS A, `modx_site_tmplvar_contentvalues` AS B 
WHERE A.`contentid` = B.`contentid` AND A.`tmplvarid`='27' AND B.`value`='Stevens' AND B.`tmplvarid` = '8'");
            -------------------------------

            you can buy me a beer, if you like MIGX

            http://webcmsolutions.de/migx.html

            Thanks!
            • 4971
            • 964 Posts
            charliez Reply #5, 17 years ago
            Thanks Bruno, yes, I was thinking the same this morning, clearer head...
            I will try it and process with getRow or getValue and the like... thanks...
              Website: www.mercologia.com
              MODX Revo Tutorials:  www.modxperience.com

              MODX Professional Partner
              • 9207 ☆ A M B ☆
              • 2,475 Posts
              Everett Reply #6, 17 years ago
              I’m looking at this more carefully now... this query joins the table on itself, yes?

              I think you need to be careful with a JOIN statement like that... I’m pretty sure that’s of the type that works on MySQL 4, but fails in 5. My advice to always use an "ON" clause to identify the keys being joined, e.g. FROM pages JOIN site ON page.id=site.id

              In the end, though I think the MODx API is just doing something like this:

              $modx->db->update($fields, $table, $where);

              // Translates to a query like this:
              UPDATE $fields
              FROM $table
              WHERE $where

                Hosting stuff: http://fireproofsocks.com/
                Coding stuff: http://craftsmancoding.com/
                Blog stuff: http://tipsfor.us/
                • 4971
                • 964 Posts
                charliez Reply #7, 17 years ago
                Yes, I had the same reservation, FROM does not work in an UPDATE in MySQL and had
                to test WHERE too.

                So this works fine in phpmyadmin and should work in the $modx->db->query() function...

                UPDATE `modx_site_tmplvar_contentvalues` AS A, `modx_site_tmplvar_contentvalues` AS B 
SET A.`value` = '$flag' 
WHERE A.`contentid` = B.`contentid` AND A.`tmplvarid`='27' AND B.`value`='$ivalue' AND B.`tmplvarid` = '8'
                  Website: www.mercologia.com
                  MODX Revo Tutorials:  www.modxperience.com

                  MODX Professional Partner
                  • 9207 ☆ A M B ☆
                  • 2,475 Posts
                  Everett Reply #8, 17 years ago
                  Sorry, yes, I meant SET. Update queries don’t have FROM’s.
                    Hosting stuff: http://fireproofsocks.com/
                    Coding stuff: http://craftsmancoding.com/
                    Blog stuff: http://tipsfor.us/