SQL

About this SQL FAQ

CodeIgniter users tend to use a SQL database for many of their projects. While there are a bunch of very good resources on the Intergoogle and in your local book store that describe how to use databases, the CI Forums are sometimes where people come first.

This page is where we should be able to point those people, and hopefully reduce the SQL noise in the forums.

SQL FAQ : Table of Contents (Absent an automagic ToC feature in the CI-wiki, this is here for convenience only. Do NOT assume all the SQL FAQ's that are described here are also in this ToC (people are lazy and often forget to update the ToC when they add a section to the main page). So - please read through the whole page, and use Ctrl-F to to find key words, before posting to the forums.)

Basic trouble-shooting

Something is going wrong

Complex trouble-shooting

How do I limit the memory usage?

SELECT queries

How do I see the raw SQL query that AR is generating?
How do I do a COUNT('foo') using AR?
How do I JOIN two tables, using AR, if both tables have a common column, eg. id?
How do I emulate a BETWEEN clause using AR?

INSERT queries

How do I find out the ID of the row I just inserted?

UPDATE queries

How can I tell if an UPDATE was successful, or how many rows were updated?

DELETE queries

How can I tell if a DELETE was successful, or how many rows were deleted?

Basic trouble-shooting

Something is going wrong

Before posting a message with this level of detail, review the guide on how to ask a good question.

In general, the process is simple: 1. Confirm what SQL is actually being passed to your database (see the FAQ for how to do this) 2. Replicate the SQL using a native interface (eg. the MySQL CLI) to determine where the problem lies

Complex trouble-shooting

How do I limit the memory usage?

You can use $result->free_result() to free the results, but sometimes this isn't the root cause of excessive memory usage. Also, some calls (such as INSERTs) do not return a database object, so you can't free() them. With very many INSERTs this can really bite you, as CI defaults to saving all your queries.

An undocumented CI feature is available to turn this off:

$this->db->save_queries = false;

There may be performance (caching) issues associated with disabling this across the board - ie. caution and experimentation is indicated.

SELECT queries

How do I see the raw SQL query that AR is generating?

Answer 1 Turn on profiling (part of the benchmarking class) - this will show the full detail of all the SQL queries for the page.

Answer 2 You can do this before your query:

$this->db->_compile_select();

.. and then this, once you've run the query:

$this->db->last_query();

How do I do a COUNT('foo') using the Active Record functions?

You need to use the SQL AS feature, where you assign a new name to a piece of data, viz:

$this->db->select("COUNT('foo') AS foo_count", FALSE);
// Run your query, and then use the foo_count variable.

Refer to the CI User Manual's section on Active Record Class for more information.

How do I JOIN two tables, using AR, if both tables have a common column, eg. id?

If you JOIN two tables that share an identically named column, your result set will include only one column with that name.

The simple answer is to use the AS feature, as described for the previous COUNT('foo') AS foo_count question. This works best when you are SELECTING specific columns. For example:

$this->db->select ("foo.id AS foo_id, bar.id AS bar_id, ... ");

// Results will include: foo_id, bar_id

In general terms you should strive to specify all your SELECT fields - it reduces load on the database and network, memory requirements, and (arguably) also contentions and bugs. SELECT() constructs are, by definition, heavier. However there are times when, for various reasons, you want to use a SELECT().

You can work around the problem by redefining your AS casts after the SELECT(*) - these have been shown in separate function calls to highlight what we're doing here.

// A select("*") is the assumed default, but ONLY ABSENT any other ->select() calls
$this->db->select ("*");

// We now append the two renamed-using-AS 'id' fields
$this->db->select ("foo.id AS foo_id");
$this->db->select ("bar.id AS bar_id");

// We do the JOIN here, using whatever criteria you want
$this->db->join ("bar", "bar.thing = foo.otherthing", "LEFT");

// Initiate the actual database query
$query = $this->db->get ("foo");


// The result_array() output from this would look like:
Array
(
    [0] => Array
        (
             [id] => 27           // This SHOULD BE IGNORED (it's *probably* = bar.id)
             [name] => Brian
             [email] => brian@life.of
             . . .
             [foo_id] => 42       // We use and trust this field
             [bar_id] => 27       // We use and trust this field
        )

    [1] => Array
        (
. . .

To clarify:

The [id] field should be ignored - the contents is predictable, as it will reflect the most recent JOINed table with an id column - but using it will inspire ambiguity. We basically just discard it for the sake of coding consistency and clarity.
The [foo_id] and [bar_id] fields are what we rely on here.
You only need to do this trick - forcing the first SELECT("*"), with subsequent AS constructs - when you do not know what specific fields you should SELECT - but this should be an exceptional, rather than your normal use case.

How do I emulate a BETWEEN clause using AR?

With MySQL you can do a query like this:

SELECT ‘whatever’ FROM ‘tablename’ WHERE ‘field_name’ BETWEEN ‘lower_value’ AND ‘higher_value’;

There's two obvious ways you can effect this with AR:

// First
$this->db->where('field_name >=', $lower_value);
$this->db->where('field_name <=', $higher_value);

// Second
$this->db->where('field_name BETWEEN ' . $lower_value. ' AND ' . $higher_value);

INSERT queries

How do I find out the ID of the row I just inserted?

(Just so that people searching for this will be more likely to find it, we'll mention that this is comparable to the native PHP mysql_insert_id() function.)

This is covered in the Query Helper Functions section of the CI User Guide - the function you're looking for is:

$foo = $this->db->insert_id();

UPDATE queries

How can I tell if an UPDATE was successful, or how many rows were updated?

The AR Helpers page suggests this construct will work for you:

$x = $this->db->affected_rows();

Consulting the PHP.net documentation for mysql_affected_rows() provides some cautionary words for MySQL users. Specifically: [quote][i]"When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query." [/i] [/quote]

DELETE queries

How can I tell if a DELETE was successful, or how many rows were deleted?