ExpressionEngine Docs

# Raw Queries and Metadata

- Raw Queries
- Query Helpers
- Metadata

## Raw Queries

- `query($sql[, $binds = FALSE])`
- `simple_query($sql)`
- `protect_identifiers($item[, $prefix_single = FALSE])`
- `escape($str)`
- `escape_str($str[, $like = FALSE])`
- `escape_like_str($str)`

### `query($sql[, $binds = FALSE])`

Parameter | Type | Description
--- | --- | ---
$sql | `String` | The SQL query to run
$binds | `Array` | Array of query binding data
Returns | `CI_DB_result` | The query result

To submit a query, use the following function:

    ee()->db->query('YOUR QUERY HERE');

The query() function returns a database result object when “read” type queries are run, which you can use to show your results. When “write” type queries are run it simply returns `TRUE` or `FALSE` depending on success or failure. When retrieving data you will typically assign the query to your own variable, like this:

    $query = ee()->db->query('YOUR QUERY HERE');

Query Bindings

Bindings enable you to simplify your query syntax by letting the system put the queries together for you. Consider the following example:

    $sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
    ee()->db->query($sql, array(3, 'live', 'Rick'));

The question marks in the query are automatically replaced with the values in the array in the second parameter of the query function.

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don’t have to remember to manually escape data; the engine does it automatically for you.

### `simple_query($sql)`

Parameter | Type | Description
--- | --- | ---
$sql | `String` | The SQL query to run
Returns | `PDOStatement`/`FALSE` | A `PDOStatement` object on success, `FALSE` otherwise

This is a simplified version of the `query()` method. It DOES NOT return a database result set, nor does it set the query timer, or compile bind data, or store your query for debugging. It simply lets you submit a query. Most users will rarely use this function.

It returns whatever the database drivers’ “execute” function returns. That typically is `TRUE`/`FALSE` on success or failure for write type queries such as `INSERT`, `DELETE` or `UPDATE` statements (which is what it really should be used for) and a resource/object on success for queries with fetchable results.

    if (ee()->db->simple_query('YOUR QUERY'))
    {
        echo "Success!";
    }
    else
    {
        echo "Query failed!";
    }

### `protect_identifiers($item[, $prefix_single = FALSE])`

Parameter | Type | Description
--- | --- | ---
$item | `Mixed` | The item to escape
$prefix_single | `Boolean` | Set to `TRUE` to add the prefix to the table name
Returns | `String` | The escaped item

In many databases it is advisable to protect table and field names - for example with backticks in MySQL. Query Builder queries are automatically protected, however if you need to manually protect an identifier you can use:

    ee()->db->protect_identifiers('table_name');

Important: Although the Query Builder will try its best to properly quote any field and table names that you feed it, note that it is NOT designed to work with arbitrary user input. DO NOT feed it with unsanitized user data.

This function will also add a table prefix to your table, assuming you have a prefix specified in your database config file. To enable the prefixing set `TRUE` (boolean) via the second parameter:

    ee()->db->protect_identifiers('table_name', TRUE);

### `escape($str)`

Parameter | Type | Description
--- | --- | ---
$str | `String` | The string to escape
Returns | `String` | The escaped string

This function determines the data type so that it can escape only string data. It also automatically adds single quotes around the data so you don’t have to:

    $sql = "INSERT INTO table (title) VALUES(".ee()->db->escape($title).")";

### `escape_str($str[, $like = FALSE])`

Parameter | Type | Description
--- | --- | ---
$str | `String` | The string to escape
$like | `Boolean` | Set to `TRUE` to escape `LIKE` condition wildcards
Returns | `String` | The escaped string

This function escapes the data passed to it, regardless of type. Most of the time you’ll use the above function rather than this one. Use the function like this:

    $sql = "INSERT INTO table (title) VALUES('".ee()->db->escape_str($title)."')";

### `escape_like_str($str)`

Parameter | Type | Description
--- | --- | ---
$str | `String` | The string to escape
Returns | `String` | The escaped string

This is just like `escape_str()` with the second parameter set as `TRUE` to escape `LIKE` condition wildcards:

    $search = '20% raise'; $sql = "SELECT id FROM table WHERE column LIKE '%".ee()->db->escape_like_str($search)."%'";

## Query Helpers

- `insert_id()`
- `affected_rows()`
- `count_all($table)`
- `last_query()`
- `insert_string($table, $data)`
- `update_string($table, $data, $where)`

### `insert_id()`

Parameter | Type | Description
--- | --- | ---
Returns | `Int` | The ID number of the row just inserted

The insert ID number when performing database inserts.

### `affected_rows()`

Parameter | Type | Description
--- | --- | ---
Returns | `Int` | The number of affected rows

Displays the number of affected rows, when doing “write” type queries (insert, update, etc.).

Note: In MySQL `DELETE FROM TABLE` returns 0 affected rows. The database class has a small hack that allows it to return the correct number of affected rows.

### `count_all($table)`

Parameter | Type | Description
--- | --- | ---
$table | `String` | The table to check
Returns | `Int` | The number of rows in a particular table

Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:

    echo ee()->db->count_all('my_table');  // Produces an integer, like 25

### `last_query()`

Parameter | Type | Description
--- | --- | ---
Returns | `String` | The last query as SQL

Returns the last query that was run (the query string, not the result):

    $str = ee()->db->last_query();

    // Produces:  SELECT * FROM sometable....

### `insert_string($table, $data)`

Parameter | Type | Description
--- | --- | ---
$table | `String` | The table for the query
$data | `Array` | The data for the query
Returns | `String` | A SQL string that has not been executed, use `query()` to run it

This function simplifies the process of writing database inserts. It returns a correctly formatted SQL insert string. Example:

    $data = array('name' => $name, 'email' => $email, 'url' => $url);

    $str = ee()->db->insert_string('table_name', $data);

The first parameter is the table name, the second is an associative array with the data to be inserted. The above example produces:

    INSERT INTO table_name (name, email, url) VALUES ('Rick', 'rick@example.com', 'example.com')

### `update_string($table, $data, $where)`

Parameter | Type | Description
--- | --- | ---
$table | `String` | The table for the query
$data | `Array` | The data for the query
$where | `Array` | The data for the `WHERE` clause
Returns | `String` | A SQL string that has not been executed, use `query()` to run it

This function simplifies the process of writing database updates. It returns a correctly formatted SQL update string:

    $data = array('name' => $name, 'email' => $email, 'url' => $url);

    $where = "author_id = 1 AND status = 'active'";

    $str = ee()->db->update_string('table_name', $data, $where);

The first parameter is the table name, the second is an associative array with the data to be updated, and the third parameter is the `WHERE` clause. The above example produces:

    UPDATE table_name SET name = 'Rick', email = 'rick@example.com', url = 'example.com' WHERE author_id = 1 AND status = 'active'

## Metadata

- `list_tables()`
- `table_exists($table_name)`
- `list_fields($table_name)`
- `field_exists($field_name, $table_name)`
- `field_data($table_name)`
- `platform()`
- `version()`

### `list_tables()`

Parameter | Type | Description
--- | --- | ---
Returns | `Array` | Array of table names

Returns an array containing the names of all the tables in the database you are currently connected to. Example:

    $tables = ee()->db->list_tables();

    foreach ($tables as $table)
    {
        echo $table;
    }

### `table_exists($table_name)`

Parameter | Type | Description
--- | --- | ---
$table_name | `String` | The name of the table to check
Returns | `Boolean` | `TRUE` if the `$table_name` exists, `FALSE` otherwise

Sometimes it’s helpful to know whether a particular table exists before running an operation on it. Returns a boolean `TRUE`/`FALSE`. Usage example:

    if (ee()->db->table_exists('table_name'))
    {
        // some code...
    }

### `list_fields($table_name)`

Parameter | Type | Description
--- | --- | ---
$table_name | `String` | The name of the table to check
Returns | `Array` | Array of field names

Returns an array containing the field names. This query can be called two ways:

1. You can supply the table name and call it from the `ee()->db->` object:

       $fields = ee()->db->list_fields('table_name');

       foreach ($fields as $field)
       {
           echo $field;
       }

2. You can gather the field names associated with any query you run by calling the function from your query result object:

       $query = ee()->db->query('SELECT * FROM some_table');

       foreach ($query->list_fields() as $field)
       {
           echo $field;
       }

### `field_exists($field_name, $table_name)`

Parameter | Type | Description
--- | --- | ---
$field_name | `String` | The name of the field to look for
$table_name | `String` | The name of the table to look in
Returns | `Boolean` | `TRUE` if the `$field_name` exists within `$table_name`, `FALSE`

Sometimes it’s helpful to know whether a particular field exists before performing an action. Returns a boolean `TRUE`/`FALSE`. Usage example:

    if (ee()->db->field_exists('field_name', 'table_name'))
    {
        // some code...
    }

### `field_data($table_name)`

Parameter | Type | Description
--- | --- | ---
$table_name | `String` | The name of the table
Returns | `CI_DB_result` | Object containing the following field data: `name` - column name `max_length` - maximum length of the column `primary_key` - 1 if the column is a primary key `type` - the type of the column

Returns an array of objects containing field information.

Sometimes it’s helpful to gather the field names or other metadata, like the column type, max length, etc.

Usage example:

    $fields = ee()->db->field_data('table_name');

    foreach ($fields as $field)
    {
        echo $field->name;
        echo $field->type;
        echo $field->max_length;
        echo $field->primary_key;
    }

If you have run a query already you can use the result object instead of supplying the table name:

    $query = ee()->db->query("YOUR QUERY");
    $fields = $query->field_data();

### `platform()`

Parameter | Type | Description
--- | --- | ---
Returns | `String` | The name of the database platform you are running

Outputs the database platform you are running:

    echo ee()->db->platform();

Note: This will only display MySQL since that’s what ExpressionEngine requires, but is included for completeness.

### `version()`

Parameter | Type | Description
--- | --- | ---
Returns | `String` | The version of the database you’re running

Outputs the database version you are running:

    echo ee()->db->version();