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

SQL query, how to do it?

    • 9359
    • 128 Posts
    FabryShock Reply #1, 14 years, 11 months ago
    Hello, excuse the obvious question for many ...
    I need to make a query directly to db, but I do not know how to do it.

    for example, if I wanted to get, through sql query, all the children of a resource beginning with the letter a? How to do it without using Ditto or Wayfinder?

    many thanks
      "Destiny is not a matter of chance, it's a matter of choice"
      • 24732 ☆ A M B ☆
      • 300 Posts
      redtoad Reply #2, 14 years, 11 months ago
      how to compose a snippet:

      http://sottwell.com/create-snippets.html

      DB API
      http://wiki.modxcms.com/index.php/API:DBAPI:query


      or I think theoretically you can use an @SELECT TV to do what you want.
      http://sottwell.com/tv-bindings.html

      hope some of that helps.



      [ed. note: redtoad last edited this post 14 years, 11 months ago.]
        ________

        Anne
        Toad-in-Chief
        Red Toad Media - Web Design, Louisville KY
        Hear me tweet: http://www.twitter.com/redtoadmedia
        "Bring on the imperialistic condiments." - Rory Gilmore
        • 36404
        • 307 Posts
        virtualbear Reply #3, 14 years, 11 months ago
        hi,

        in the case you're describing, i would not use an sql query but modx document parser object
        http://rtfm.modx.com/display/Evo1/getDocumentChildren

        as it returns an array of what you want, you'll end in manipulating data in an array, something php does fast and easily

        in case you absolutely want to do your own queries you acn either use $modx->db->query or the usual mysql_query smiley

        have swing
          réfléchir avant d'agir
          • 9359
          • 128 Posts
          FabryShock Reply #4, 14 years, 11 months ago
          thx at all,
          i have tried to create a snippet like this:
          <?php
$output = '';
$result = $modx->db->query( 'SELECT id, pagetitle, joined FROM `Modx_site_content`' );
 
while( $row = $modx->db->getRow( $result ) ) {
	$output .= '<br /> ID: ' . $row['id'] . '<br /> Name: ' . $row['pagetitle'] . '<br /> Joined: ' . $row['joined']
	. '<br />---------<br />';
}
echo $output;
?>


          but when i try to execute it:

          « MODx Parse Error »
          MODx encountered the following error while attempting to parse the requested resource:
          « Execution of a query to the database failed - Table 'isola2.Modx_site_content' doesn't exist »
          SQL: SELECT id, pagetitle, joined FROM `Modx_site_content`
          [Copy SQL to ClipBoard]

          (My db is named isola2)... Any help for me?
          thx
            "Destiny is not a matter of chance, it's a matter of choice"
            • 16278
            • 928 Posts
            kp52 Reply #5, 14 years, 11 months ago
            The API function getFullTableName('site_content') will ensure you have the right name for your database table.

            You could also use the db->select() function 
            $table = $modx->getFullTableName('site_content');
$parent = 99;  // id of parent resource
$result = $modx->db->select( "id, pagetitle, joined", $table, "parent=$parent" );
// etc.


            The PHP double-quote syntax is useful for strings in queries, both to parse the variables and to avoid confusion with MySql's own use of single quotes.

            :) KP