Database_Wrapper_Creole

Description

The Creole Wrapper class provides methods for interacting with Creole easily and securely.

Class Methods

select ( string statement [, array placeholder] )

mixed
Executes the SQL SELECT statement in a PreparedStatement object and returns the ResultSet generated by the query.

insert ( string table_name, array record [, bool on_duplicate_key_update] )

mixed
Executes the SQL INSERT statement in a PreparedStatement object.

update ( string table_name, array record, array where_clause )

mixed
Executes the SQL UPDATE statement in a PreparedStatement object.

delete ( string table_name, array where_clause )

mixed
Executes the SQL DELETE statement in a PreparedStatement object.

selectAndPaginate ( string statement, int current_page [, int results_per_page] )

mixed
Executes the SQL SELECT statement in a PreparedStatement object, add the LIMIT clause and returns the resultset generated by the query.

getTotalRows ()

int
Returns the total number of rows returned by the last executed query.

getInsertId ()

int
Returns the last inserted id.

getLastQuery ()

string
Returns the last executed query.

General Usage

Example: SELECT prepared statement

$statement = "SELECT * FROM `news_items` WHERE `year` = ? AND `group_id` = ?";
$placeholder = array("2007", 4);

// Returns a resultset on success or false on failure
$this->db()->select($statement, $placeholder);

You should always use the “?” place holder to add values to a statement, as this will prevent SQL injection attacks by ensuring that data is converted to the proper type, and when appropriate escaped and quoted.

Example: INSERT prepared statement

$fields = array(
        "user_id" => 12,
        "activation_key" => "38su2373js92"
);

$onDuplicateKeyUpdate = true;

// Returns the affected rows on success or false on failure
$this->db()->insert("activation_code", $field, $onDuplicateKeyUpdate);

Example: UPDATE prepared statement

$fields = array(
        "password" => $userId,
        "salt" => $activationCode
);

$where = array(
        "statement" => "`user_id` = ?",
        "placeholder" => array(12)
);

// Return the affected rows on success or false on failure
$this->db()->update("user", $fields, $where);

Example: DELETE prepared statement

$where = array(
        "statement" => "`user_id` = ? AND `user_id` = ?",
        "placeholder" => array(12, 13)
);

// Returns the affected rows on success or false on failure
$this->db()->delete("users", $where);

Getting Column Values

The default method to get column values is to use the appropriate get*() method for the column data type. E.g. use getString() to return data from a CHAR/VARCHAR/TEXT column. Creole will perform any unescaping and type conversions. For example:

while($rs->next()) {
         // returns PHP string
        echo "n" . $rs->getString("name");

         // returns PHP int/long
        echo "n" . $rs->getInt("id");

         // returns formatted date
        echo "n" . $rs->getTimestamp("stamp", "m/d/y H:i:s");
}

You can also use the generic !ResultSet::get() method, if you do not want to perform any type conversions on the data you are returning:

while($rs->next()) {
        print $rs->get("name") . " (" . $rs->get("id") . ")";
}

And finally, you can also use the getRow() to return an associative array of the current row:

while ($rs->next()) {
        $row = $rs->getRow();
        foreach ($row as $key => $value) {
                echo $key . ": " . $value;
        }
}

Scrolling ResultSet Methods

Scrolling ResultSet features are supported well in MySQL, PostgreSQL, and SQLite. For Oracle, however, this behaviour has to be emulated. For this reason, reverse scrolling is not currently supported in Oracle.

// Move to first record
$rs->first();

// Move ahead 3 rows
$rs->relative(3);

// Scroll backwards
$rs->previous();

// Move to last position
$rs->last();

ResultSetIterator

In addition to using the traditional scrolling functions, you can also use an SPL Iterator to iterate over the query results. Some drivers (e.g. SQLite) have optimized iterators that will be as fast or faster than the traditional scrolling methods.

foreach ($rs as $row) {
        // $row is an assoc array
        print_r($row);
}

This method of iterating over a ResultSet exists for convenience, and does not provide any type-conversion methods.