Query

A Query gives you the opportunity to retrieve data from the database. But not only that, you can also manipulate data. The Query uses boringly normal SQL.

$query = new Query("SELECT * FROM `role` WHERE id = 1");

The code above just instantiates a Query object and passes the query-string to the constructor. It has yet not been executed, but the Query analyzes what kind of query had been passed (select, update, delete or insert).

A Query provides two public methods:

• getSingleResult()
• getResultList()

The behavior of both methods depends on the kind of query. On calling one of these methods the query is actually executed on the database.

---

getSingleResult()

If the Query is a kind of select and its execution resulted in more than one row, this method will return the first row as an POPO. If the execution resulted in just one row, then this one row is returned as POPO as well.

There is a difference when the result of the execution has just one column. Then the method returns the value of the single column. Here counts the same rule: If there are more than one rows, the first one will be returned.

Examples:

$query = new Query("SELECT * FROM `role` WHERE id = 1");
$query->getSingleResult();

Returns:
stdClass Object
(
    [id] => 1
    [name] => admin
)

$query = new Query("SELECT count(*) FROM `user`");
$query->getSingleResult();

Returns:
string(1) "3"

The behavior is different on executing an update or delete statement. Here the number of affected rows is returned.

$query = new Query("UPDATE `user` SET password = md5('passme')");
$query->getSingleResult();

Returns:
string(1) "3"

$query = new Query("DELETE FROM `user`");
$query->getSingleResult();

Returns:
string(1) "3"

On insert statements the last inserted id is returned.

$query = new Query("INSERT INTO `role` VALUES (NULL, 'sales')");
$query->getSingleResult();

Returns:
string(1) "4"

---

getResultList()

This method mostly returns an array with POPO's. But on insert, update and delete statements, the behavior is similar to getSingleResult().

Examples:

$query = new Query("SELECT * FROM `role`");
$query->getResultList();

Returns:
Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => admin
        )
    [1] => stdClass Object
        (
            [id] => 4
            [name] => sales
        )
    [2] => stdClass Object
        (
            [id] => 2
            [name] => user
        )
)

// behavior is similar to getSingleResult()
$query = new Query("INSERT INTO `role` VALUES (NULL, 'clerk')");
$query->getResultList();

Returns:
string(1) "4"

---

See also: TypedQuery