xPDO.query
Last updated Apr 20th, 2021 | Page history | Improve this page | Report an issue
Support the team building MODX with a monthly donation.
The budget raised through OpenCollective is transparent, including payouts, and any contributor can apply to be paid for their work on MODX.
Backers
Budget
$306 per month—let's make that $500!
Learn morexPDO::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 https://api.modx.com/revolution/2.2/db_core_xpdo_xpdo.class.html#\xPDO::query()
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.
$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 includesPDO::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
: combinesPDO::FETCH_BOTH
andPDO::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¶
- Retrieving Objects
- xPDO.getObject
- xPDO.getObjectGraph
- xPDO.getCollection
- xPDO.getCollectionGraph
- xPDO.getIterator
- xPDO
Support the team building MODX with a monthly donation.
The budget raised through OpenCollective is transparent, including payouts, and any contributor can apply to be paid for their work on MODX.
Backers
Budget
$306 per month—let's make that $500!
Learn more