$modx->query doesn't seem to execute

Question

As per the documentation here: http://rtfm.modx.com/display/xPDO20/xPDO.query

Which shows the following as an example:

$result = $modx->query("SELECT * FROM modx_users WHERE id=1");
if (!is_object($result)) {
   return 'No result!';
}

I would assume this statement would work:

$checkUnique = $modx->query("SELECT * FROM my_table_name WHERE guid = '$unique' AND used = 0");

//guid already used, or non-existant
if(!is_object($checkUnique)){
  $result = array(
    "result" => false
  );
  return json_encode($result);
}

When I do a var_dump (of $checkUnique), I get this as a result:

object(PDOStatement)#22 (1) { ["queryString"]=> string(70) "SELECT * FROM my_table_name WHERE guid='5114722f24870' AND used=0"}

I know the used column has been set to 1, but it never triggers my if block.

What am I doing wrong?

What's the problem? `$checkUnique` appears to be an object (`PDOStatement` to be precise) so your `if` block will not be executed; `!is_object($checkUnique)` will evaluate to `false` — Phil, Feb 08 '13 at 03:47
@Phil - right, so why is it an object containing a query string? Shouldn't it have executed and turned into a result of some kind? The example at the link suggests it will return a non-object if there isn't a result... — ahren, Feb 08 '13 at 03:49
It's a PDOStatement object. It doesn't just magically turn into a result. You need to fetch it (i.e. fetch() or fetchAll()) — Mike, Feb 08 '13 at 03:51
@ahren Did you not read the documentation you [linked to](http://rtfm.modx.com/display/xPDO20/xPDO.query)? *"Executes an SQL statement, returning a result set as a [PDOStatement](http://php.net/manual/en/class.pdostatement.php) object.*" — Phil, Feb 08 '13 at 03:51
@Phil - updated the question with the example given in the linked documentation. — ahren, Feb 08 '13 at 03:54
It says it returns false on failure. Is an empty data set a failure? — Mike, Feb 08 '13 at 03:57
@Mike - just tried it out with your suggestion (`->fetch()`) and yes, an empty data set does seem to be a failure! Thank you - if you'd write that up as an answer I'll accept. — ahren, Feb 08 '13 at 04:01

Phil · Accepted Answer · 2013-02-08T04:12:24.540

2

It seems the MODx doco could use some help. I just tried this locally and an empty result set still returns a PDOStatement object.

You could attempt to use PDOStatement::rowCount() to check for the number of rows returned

$count = $checkUnique->rowCount();

or alter your query like so

$check = $modx->query("SELECT COUNT(1) FROM my_table_name WHERE guid = '$unique' AND used = 0");
$count = $check->fetchColumn();
if (!$count) {
    // etc

I've raised a bug with the MODx documentation - http://tracker.modx.com/issues/9502

edited Feb 08 '13 at 04:12

answered Feb 08 '13 at 04:03

Phil

157,677
23
242
245

erm... `rowCount()` returns the number of rows affected (i.e. `UPDATE`, `INSERT`, `DELETE`). I admit it was poorly named. You're looking for `count($stmt->fetchAll())` – Mike Feb 08 '13 at 04:05
@Mike it also works for `SELECT` statements in MySQL. Tested and verified – Phil Feb 08 '13 at 04:11
You could be right. The docs don't make it clear which DBMS it would work for: "*If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.*" – Mike Feb 08 '13 at 04:14
@Mike I don't like relying on it either, hence the `SELECT COUNT()` alternative above – Phil Feb 08 '13 at 04:22

$modx->query doesn't seem to execute

1 Answers1