SQL

[h2]About this SQL FAQ[/h2] CodeIgniter users [i]tend[/i] 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.

[b][color=green]SQL FAQ : Table of Contents[/color][/b] [color=grey][i](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.)[/color]

[b]Basic trouble-shooting[/b]

• [i]Something is going wrong[/i]

[b]SELECT queries[/b]

• [i]How do I see the raw SQL query that AR is generating?[/i]
• [i]How do I do a COUNT('foo') using AR?[/i]
• [i]How do I emulate a [b]BETWEEN[/b] clause using AR?[/i]

[b]INSERT queries[/b]

• [i]How do I find out the ID of the row I just inserted?[/i]

[b]UPDATE queries[/b]

• [i]How can I tell if an UPDATE was successful, or how many rows were updated?[/i]

[b]DELETE queries[/b]

• [i]How can I tell if a DELETE was successful, or how many rows were deleted?[/i]

[h2]Basic trouble-shooting[/h2] [h3]Something is going wrong[/h3] Before posting a message with this level of detail, review the guide on [url="http://codeigniter.com/wiki/How_to_ask_a_good_question/"]how to ask a good question[/url].

In general, the process is simple - you need to confirm what SQL is actually being passed to your database and then you need to replicate that SQL using the database's normal interface.

There are two easy ways of identifying what is being passed, and these are detailed in the [url="/wiki/FAQ"]FAQ[/url]. A hint - you need to turn on profiling.

[h2]SELECT queries[/h2] [h3]How do I see the raw SQL query that AR is generating?[/h3]

[b]Answer 1[/b] Turn on [url="http://codeigniter.com/user_guide/general/profiling.html"]profiling[/url] (part of the benchmarking class) - this will show the full detail of all the SQL queries for the page.

[b]Answer 2[/b] You can do this before your query: [code] $this->db->_compile_select(); [/code] .. and then this, once you've run the query: [code] $this->db->last_query(); [/code]

[h3]How do I do a COUNT('foo') using the Active Record functions?[/h3] You need to use the SQL [b]AS[/b] feature, where you assign a new name to a piece of data, viz: [code] $this->db->select("COUNT('foo') AS foo_count", FALSE); // Run your query, and then use the foo_count variable. [/code] Refer to the CI User Manual's section on [url="/user_guide/database/active_record.html"]Active Record Class[/url] for more information.

[h3]How do I emulate a [b]BETWEEN[/b] clause using AR?[/h3]

With MySQL you can do a query like this: [code] SELECT ‘whatever’ FROM ‘tablename’ WHERE ‘field_name’ BETWEEN ‘lower_value’ AND ‘higher_value’; [/code]

There's two obvious ways you can effect this with AR: [code] // First $this->db->where('field_name >=', $lower_value); $this->db->where('field_name <=', $higher_value); [/code]

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

[h2]INSERT queries[/h2] [h3]How do I find out the ID of the row I just inserted?[/h3] [i][color=grey](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.)[/color][/i]

This is covered in the [url="http://codeigniter.com/user_guide/database/helpers.html"]Query Helper Functions[/url] section of the CI User Guide - the function you're looking for is: [code] $foo = $this->db->insert_id(); [/code]

[h2]UPDATE queries[/h2] [h3]How can I tell if an UPDATE was successful, or how many rows were updated?[/h3]

The [url="/user_guide/database/helpers.html"]AR Helpers[/url] page suggests this construct will work for you: [code] $x = $this->db->affected_rows(); [/code]

Consulting the [url="http://php.net/manual/en/function.mysql-affected-rows.php"]PHP.net documentation for mysql_affected_rows()[/url] 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]

[h2]DELETE queries[/h2] [h3]How can I tell if a DELETE was successful, or how many rows were deleted?[/h3]

The [url="/user_guide/database/helpers.html"]AR Helpers[/url] page suggests this construct will work for you: [code] $x = $this->db->affected_rows(); [/code]

This page also provides a caveat for MySQL users. The affected_rows() function lives in (system/database/drivers/mysql/mysql_driver.php) and is simply this: [code] function affected_rows() { return @mysql_affected_rows($this->conn_id); } [/code]

Consulting the [url="http://php.net/manual/en/function.mysql-affected-rows.php"]PHP.net documentation for mysql_affected_rows()[/url] reveals the underlying function has its own caveats. Btw, the [i]delete hack[/i] mentioned is located at line 47 of the [b]mysql_driver.php[/b] file (in 1.7.2), and the hack is implemented within _prep_query() in the same file.

Category:Help