-
Notifications
You must be signed in to change notification settings - Fork 26
[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]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]
- [i]placeholder[/i]
[b]INSERT queries[/b]
- [i]How do I find out the ID of the row I just inserted?[/i]
- [i]placeholder[/i]
[b]UPDATE queries[/b]
- [i]placeholder[/i]
- [i]placeholder[/i]
[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. For example: [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]placeholder[/h3]
[h3]placeholder[/h3]