Querying Data

query

Execute read-only SQL query on the database.

PHP

array query(string $sql, array $bind_parameters)

Parameters

$sql The query string. All data inside the query should be bound with named bind variables or properly escaped.
$bind_parameters An array of key-value pairs with elements that are bound parameters in the SQL statement being executed. Array key names match the named bind variables in the SQL statement.

Return Values

Returns FALSE on failure. For successful queries, it will return an array of data.

getQueryError

Retrieve the last Query Error that was triggered by the query function. When executing invalid SQL the query function returns false. To see the error in the query use this function.

CODE

array getQueryError()

Example: Simple Services Query

PHP

$services = $this->query("SELECT * FROM SERVICES");

Example: Using Heredoc Notation

This example uses PHP heredoc Notation to query for Available Services

PHP

$services = $this->query(<<<SQL
    SELECT S.RECID, S.SERVICE_ID
    FROM SERVICES S
    LEFT JOIN LISTS
        ON S.SERVICE_STATUS_LISTS_RECID = LISTS.RECID
    WHERE LISTS.CODE = "AVAILABLE"
SQL);

Example: Using Bind Variables

This example uses Bind Variables to query for Services with specified (Active and Available) Status Codes. Data can be bound to the query as arrays or single variables.

PHP

$services = $this->query(<<<SQL
    SELECT S.RECID, S.SERVICE_ID
    FROM SERVICES S
    LEFT JOIN LISTS
        ON S.SERVICE_STATUS_LISTS_RECID = LISTS.RECID
    WHERE LISTS.CODE IN (:active, :available)
SQL, [':active' => 'ACTIVE', ':available' => 'AVAILABLE']);

$statusCodes = ['ACTIVE', 'AVAILABLE'];
$services = $this->query(<<<SQL
    SELECT S.RECID, S.SERVICE_ID
    FROM SERVICES S
    LEFT JOIN LISTS
        ON S.SERVICE_STATUS_LISTS_RECID = LISTS.RECID
    WHERE LISTS.CODE IN (:statues)
SQL, [':statues' => $statusCodes);

Example: Retrieving Query Error

This example executes a bad SQL statement and then retrieves the error and sends it to the debug function.

PHP

$invalid = $this->query(<<<SQL
    THIS IS INVALID SQL
SQL);

$error = $this->getQueryError();
$this->debug($error);