1. Overview
      1. What It Is
      2. Why I Would Want to Use It
      3. Known Issues
    2. Getting Started
      1. Fundamentals
        1. Design Patterns
        2. xPDO, the Class
          1. As Object and Relational Mapper
          2. As PDO Wrapper
          3. As Service Layer
          4. The xPDO Constructor
            1. Hydrating Fields
      2. Introduction to the xPDO Object Model
        1. Understanding xPDOCriteria and xPDOQuery
      3. Creating a Model With xPDO
        1. Defining a Schema
          1. Defining Relationships
          2. Defining the Database and Tables
            1. Upgrading Models to Schema Version 1.1
          3. More Examples of xPDO XML Schema Files
          4. Validation Rules in your Schema
        2. Generating the Model Code
          1. Domain Classes
          2. O-R Maps
          3. Table Classes
      4. Using Your xPDO Model
        1. Creating Objects
        2. Database Connections and xPDO
        3. Loading Packages
        4. Removing Objects
          1. removeCollection
        5. Retrieving Objects
          1. getCollectionGraph
        6. Setting Object Fields
        7. Working with Related Objects
    3. Extending Your xPDO Model
      1. Overriding Derived Behavior
        1. Using Custom Object Loaders
      2. Working With Transient Object Classes
    4. Advanced Features
      1. Caching
        1. Caching Tutorial - Basic Snippets
        2. Caching Tutorial - Lifetimes
      2. Object Validation
        1. xPDOForeignKeyConstraint
        2. xPDOMaxLengthValidationRule
        3. xPDOMaxValueValidationRule
        4. xPDOMinLengthValidationRule
        5. xPDOMinValueValidationRule
        6. xPDOObjectExistsValidationRule
    5. xPDO Development
      1. Testing
      2. Building
      3. Documentation and Tutorials
    6. Class Reference
      1. xPDO
        1. xPDO.addPackage
        2. xPDO.beginTransaction
        3. xPDO.commit
        4. xPDO.connect
        5. xPDO.fromJSON
        6. xPDO.getCollection
        7. xPDO.getCollectionGraph
        8. xPDO.getCount
        9. xPDO.getDebug
        10. xPDO.getFields
        11. xPDO.getIterator
        12. xPDO.getManager
        13. xPDO.getObject
        14. xPDO.getObjectGraph
        15. xPDO.getOption
        16. xPDO.getTableName
        17. xPDO.loadClass
        18. xPDO.log
        19. xPDO.newObject
        20. xPDO.newQuery
        21. xPDO.query
        22. xPDO.setDebug
        23. xPDO.setLogLevel
        24. xPDO.setLogTarget
        25. xPDO.setOption
        26. xPDO.toJSON
      2. xPDOCacheManager
        1. xPDOCacheManager.copyFile
        2. xPDOCacheManager.copyTree
        3. xPDOCacheManager.delete
        4. xPDOCacheManager.deleteTree
        5. xPDOCacheManager.endsWith
        6. xPDOCacheManager.escapeSingleQuotes
        7. xPDOCacheManager.get
        8. xPDOCacheManager.getCachePath
        9. xPDOCacheManager.getCacheProvider
        10. xPDOCacheManager.matches
        11. xPDOCacheManager.replace
        12. xPDOCacheManager.set
        13. xPDOCacheManager.writeFile
        14. xPDOCacheManager.writeTree
      3. xPDOGenerator
        1. xPDOGenerator.parseSchema
        2. xPDOGenerator.writeSchema
      4. xPDOManager
        1. xPDOManager.createObjectContainer
        2. xPDOManager.createSourceContainer
        3. xPDOManager.removeObjectContainer
        4. xPDOManager.removeSourceContainer
      5. xPDOObject
        1. Configuration Accessors
          1. getOption
          2. setOption
        2. Field Accessors
          1. fromArray
          2. fromJSON
          3. get
          4. set
          5. toArray
          6. toJSON
        3. Metadata Accessors
          1. getFieldName
          2. getFKClass
          3. getFKDefinition
          4. getPK
          5. getPKType
          6. getSelectColumns
        4. Persistence Methods
          1. remove
          2. save
        5. Related Object Accessors
          1. addMany
          2. addOne
          3. getMany
          4. getOne
        6. State Accessors
          1. isDirty
          2. isLazy
          3. isNew
        7. Static Object Loaders
          1. _loadCollectionInstance
          2. _loadInstance
          3. _loadRows
          4. load
          5. loadCollection
          6. loadCollectionGraph
          7. Using Custom Loader Classes
        8. Validation
          1. addValidationRule
          2. getValidator
          3. isValidated
          4. removeValidationRules
          5. validate
      6. xPDOQuery
        1. xPDOQuery.andCondition
        2. xPDOQuery.groupby
        3. xPDOQuery.innerJoin
        4. xPDOQuery.leftJoin
        5. xPDOQuery.limit
        6. xPDOQuery.orCondition
        7. xPDOQuery.rightJoin
        8. xPDOQuery.select
        9. xPDOQuery.setClassAlias
        10. xPDOQuery.sortby
        11. xPDOQuery.where
      7. xPDORevisionControl
      8. xPDOTransport
        1. xPDOTransport.install
        2. xPDOTransport.pack
        3. xPDOTransport.uninstall
      9. xPDOValidator
        1. xPDOValidator.addMessage
        2. xPDOValidator.getMessages
        3. xPDOValidator.hasMessages
        4. xPDOValidator.validate

xPDO.query

Last edited by Thomas Jakobi on Nov 27, 2017.

xPDO::query

Executes an SQL statement, returning a result set as a PDOStatement object.

Tip
This can be a good way to issue reporting queries without having to worry about the complex syntax normally required by xPDO.

Syntax

API Docs: see http://php.net/manual/en/pdo.query.php

xPDOObject|false query (string $statement)

$statement

The SQL statement to prepare and execute. Data inside the query should be properly escaped.

Examples

Select a Single Record

Here's a simple query to fetch one row from the database. Note that you would normally use getObject or getCollection to fetch a data from built-in MODX tables.

xPDOObject|false query (string $statement)

$statement

The SQL statement to prepare and execute. Data inside the query should be properly escaped.

Examples

Select a Single Record

Here's a simple query to fetch one row from the database. Note that you would normally use getObject or getCollection to fetch a data from built-in MODX tables.

$result = $modx->query("SELECT * FROM modx_users WHERE id=1");
if (!is_object($result)) {
   return 'No result!';
}
else {
   $row = $result->fetch(PDO::FETCH_ASSOC);
   return 'Result:' .print_r($row,true);
}

Use the PDO::FETCH_ASSOC will force the result to be an associative array:

Array
(
    [id] => 1
    [username] => my_user
    [password] => xxxxxxxxxxxxxxxxxxx
    // ...
)

Without it, the results are a mix of an associative and a regular array:

Array
(
    [id] => 1
    [0] => 1
    [username] => my_user
    [1] => my_user
    [password] => xxxxxxxxxxxxxxxxxxxxxxx
    [2] => xxxxxxxxxxxxxxxxxxxxx
    // ...  
)
No One-Liners!
The one-line method-chaining available to PDO is not possible with xPDO. The following will not work:
$row = $modx->query("SELECT * FROM cms_users WHERE id=1")->fetch();

Selecting Multiple Records

PDO uses a lazy-loader, so you can't simply print out all of the results at once. Instead, you iterate over each result in the set using a loop, e.g.

$results = $xpdo->query("SELECT * FROM some_table");
while ($r = $results->fetch(PDO::FETCH_ASSOC)) {
        print_r($r); exit;
}

Quoting Inputs

For single queries that rely on user input, you should manually quote the input strings.

$username = $modx->quote($username);
$sql = "SELECT * FROM modx_users WHERE username = $username";
$result = $modx->query($sql);
$row = $result->fetch(PDO::FETCH_ASSOC);
return print_r($row,true);

The [quote] function can take a 2nd argument, which you can use to quote integers specifically

  • PDO::PARAM_INT for quoting integers
  • PDO::PARAM_STR for quoting strings (default)
$id = $modx->quote(1, PDO::PARAM_INT);
$sql = "SELECT * FROM cms_users WHERE id = $id";
$result = $modx->query($sql);
$row = $result->fetch(PDO::FETCH_ASSOC);
return print_r($row, true);

Select a Collection

Here's a simple query to fetch multiple rows from the database. Note that you would normally use getObject to retrive data from MODX tables.

$output = '';
$sql = "SELECT * FROM modx_users";
foreach ($modx->query($sql) as $row) {
    $output .= $row['username'] .'<br/>';
}
return $output;

You can also use the fetchAll() method to return an array of arrays (i.e. a recordset):

$output = '';
$sql = "SELECT * FROM modx_users";
$result = $modx->query($sql);
$data = $result->fetchAll(PDO::FETCH_ASSOC);
return $data;

Fetch Style

From http://php.net/manual/en/pdostatement.fetch.php, these are the available constants that affect how your results are returned:

  • PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
  • PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set
  • PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method
  • PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class. If fetch_style includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column.
  • PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class
  • PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed
  • PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0
  • PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set

Prepared Statements

See

See Also

Comment Policy

Comments intended to help other users with this document are welcome! Have a suggestion to improve this document? Suggest an edit (requires GitHub account). Have suggestions for code changes or feature requests? Submit to the xPDO project at GitHub.

Suggest an edit to this page on GitHub (Requires GitHub account. Opens a new window/tab) or become an editor of the MODX Documentation.