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

[SOLVED] Need some help with SELECT INTO query & XPDO

    • 40375
    • 12 Posts
    fetchall Reply #1, 14 years, 3 months ago
    How would I go about executing the following query using XPDO?

    SELECT * INTO OUTFILE 'path/to/file.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\'
    FROM myCustomTable

    I have the object for the table and most of the CRUD in a cmp working happily. However I need to be able to save data to a csv file on the server. I' struggling with a few more queries but I'm hoping with an answer to this one I'll be able to sort those out myself.

    I have looked through the xPDO documentation including xPDOQuery and xPDOManager but I can't see how to do this. [ed. note: fetchall last edited this post 14 years, 3 months ago.]
      • 22303 MODX Staff
      • 10,725 Posts
      opengeek Reply #2, 14 years, 3 months ago 
      $modx->exec("SELECT * INTO OUTFILE 'path/to/file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\'
FROM {$modx->getTableName('myCustomClass')}");
        Jason Coward
        Chief Architect @ MODX
        http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
        • 40375
        • 12 Posts
        fetchall Reply #3, 14 years, 3 months ago
        Thank you very much,... sooo simple. Don't know why I couldn't find that myself.
          • 22303 MODX Staff
          • 10,725 Posts
          opengeek Reply #4, 14 years, 3 months ago
          Quote from: fetchall at Jul 05, 2012, 06:42 PM
          Thank you very much,... sooo simple. Don't know why I couldn't find that myself.
          It's not necessarily obvious that all of the PDO methods are available from xPDO since it does not technically extend the PDO class. But it does wrap all available PDO methods with an added feature to create the PDO connection only on demand (i.e. so you can avoid connecting if everything is cached).
            Jason Coward
            Chief Architect @ MODX
            http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
            • 36502 ☆ A M B ☆
            • 491 Posts
            tillilab Reply #5, 12 years, 3 months ago
            Hi, I'm trying to get the same result, but with no success...

            in a external php file it works:

            $dbh = new PDO(connection_par);
$dbh->exec("SELECT * INTO OUTFILE 'path/tmp/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' FROM modx_customtable");


            and that's fine...

            If I create a snippet with:

            $sql="SELECT * INTO OUTFILE 'path/tmp/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' FROM modx_customtable";
$modx->exec($sql);


            it doesn't work and I have no error message...
            what I'm doing wrong?
            thanks
              TilliLab | MODX Ambassador
              website
              • 36502 ☆ A M B ☆
              • 491 Posts
              tillilab Reply #6, 12 years, 3 months ago
              ok, sorry I found the problem...it was a permission issue as stated here:
              http://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold
                TilliLab | MODX Ambassador
                website