CodeIgniter gives you access to a Query Builder class. This pattern allows information to be retrieved, inserted, and updated
in your database with minimal scripting. In some cases, only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own class file. It instead provides a more simplified interface. Beyond simplicity, a major benefit to using the Query Builder features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries,
since the values are escaped automatically by the system. The Query Builder is loaded through the The Query Builder is only loaded into memory when you specifically request the class, so no resources are used by default. Selecting DataThe following methods allow you to build SQL SELECT statements. Get$builder->get()Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table: <?php $builder = $db->table('mytable'); $query = $builder->get(); // Produces: SELECT * FROM mytable The first and second parameters enable you to set a limit and offset clause: <?php $query = $builder->get(10, 20); /* * Executes: SELECT * FROM mytable LIMIT 20, 10 * (in MySQL. Other databases have slightly different syntax) */ You’ll notice that the above method is assigned to a variable named $query, which can be used to show the results: <?php $query = $builder->get(); foreach ($query->getResult() as $row) { echo $row->title; } Please visit the getResult*() method page for a full discussion regarding result generation. $builder->getCompiledSelect()Compiles the selection query just like Example: <?php $sql = $builder->getCompiledSelect(); echo $sql; // Prints string: SELECT * FROM mytable The first parameter enables you to set whether or not the query builder query will be reset (by default it will be reset, just like when using <?php echo $builder->limit(10, 20)->getCompiledSelect(false); /* * Prints string: SELECT * FROM mytable LIMIT 20, 10 * (in MySQL. Other databases have slightly different syntax) */ echo $builder->select('title, content, date')->getCompiledSelect(); // Prints string: SELECT title, content, date FROM mytable LIMIT 20, 10 The key thing to notice in the above example is that the second query did not utilize $builder->getWhere()Identical to the <?php $query = $builder->getWhere(['id' => $id], $limit, $offset); Please read about the Select$builder->select()Permits you to write the SELECT portion of your query: <?php $builder->select('title, content, date'); $query = $builder->get(); // Executes: SELECT title, content, date FROM mytable Note If you are selecting all (
<?php $builder->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4) AS amount_paid', false); $query = $builder->get(); RawSqlSince v4.2.0, <?php use CodeIgniter\Database\RawSql; $sql = 'REGEXP_SUBSTR(ral_anno,"[0-9]{1,2}([,.][0-9]{1,3})([,.][0-9]{1,3})") AS ral'; $builder->select(new RawSql($sql)); $query = $builder->get(); Warning When you use $builder->selectMax()Writes a SELECT MAX(field) portion for your query. You can optionally include a second parameter to rename the resulting field. <?php $builder->selectMax('age'); $query = $builder->get(); // Produces: SELECT MAX(age) as age FROM mytable $builder->selectMax('age', 'member_age'); $query = $builder->get(); // Produces: SELECT MAX(age) as member_age FROM mytable $builder->selectMin()Writes a SELECT MIN(field) portion for your query. As with <?php $builder->selectMin('age'); $query = $builder->get(); // Produces: SELECT MIN(age) as age FROM mytable $builder->selectAvg()Writes a SELECT AVG(field) portion for your query. As with <?php $builder->selectAvg('age'); $query = $builder->get(); // Produces: SELECT AVG(age) as age FROM mytable $builder->selectSum()Writes a SELECT SUM(field) portion for your query. As with <?php $builder->selectSum('age'); $query = $builder->get(); // Produces: SELECT SUM(age) as age FROM mytable $builder->selectCount()Writes a SELECT COUNT(field) portion for your query. As with Note This method is particularly helpful when used with <?php $builder->selectCount('age'); $query = $builder->get(); // Produces: SELECT COUNT(age) as age FROM mytable $builder->selectSubquery()Adds a subquery to the SELECT section. $subquery = $db->table('countries')->select('name')->where('id', 1); $builder = $db->table('users')->select('name')->selectSubquery($subquery, 'country'); $query = $builder->get(); // Produces: SELECT `name`, (SELECT `name` FROM `countries` WHERE `id` = 1) `country` FROM `users` From$builder->from()Permits you to write the FROM portion of your query: <?php $builder = $db->table('users'); $builder->select('title, content, date'); $builder->from('mytable'); $query = $builder->get(); // Produces: SELECT title, content, date FROM users, mytable Note As shown earlier, the FROM portion of your query can be specified in the Subqueries$builder->fromSubquery()Permits you to write part of a FROM query as a subquery. This is where we add a subquery to an existing table: <?php $subquery = $db->table('users'); $builder = $db->table('jobs')->fromSubquery($subquery, 'alias'); $query = $builder->get(); // Produces: SELECT * FROM `jobs`, (SELECT * FROM `users`) `alias` Use the <?php $subquery = $db->table('users')->select('id, name'); $builder = $db->newQuery()->fromSubquery($subquery, 't'); $query = $builder->get(); // Produces: SELECT * FROM (SELECT `id`, `name` FROM users) `t` Join
$builder->join()Permits you to write the JOIN portion of your query: <?php $builder = $db->table('blogs'); $builder->select('*'); $builder->join('comments', 'comments.id = blogs.id'); $query = $builder->get(); /* * Produces: * SELECT * FROM blogs JOIN comments ON comments.id = blogs.id */ Multiple method calls can be made if you need several joins in one query. If you need a specific type of JOIN you can specify it via the third parameter of the method. Options are: <?php $builder->join('comments', 'comments.id = blogs.id', 'left'); // Produces: LEFT JOIN comments ON comments.id = blogs.id RawSqlSince v4.2.0, <?php use CodeIgniter\Database\RawSql; $sql = 'user.id = device.user_id AND ((1=1 OR 1=1) OR (1=1 OR 1=1))'; $builder->join('user', new RawSql($sql), 'LEFT'); // Produces: LEFT JOIN "user" ON user.id = device.user_id AND ((1=1 OR 1=1) OR (1=1 OR 1=1)) Warning When you use Looking for Specific DataWhere$builder->where()This method enables you to set WHERE clauses using one of five methods: Note All values passed to this method are escaped automatically, producing safer queries, except when using a custom string. Note
1. Simple key/value method<?php $builder->where('name', $name); // Produces: WHERE name = 'Joe' 2. Custom key/value method
3. Associative array method<?php $array = ['name' => $name, 'title' => $title, 'status' => $status]; $builder->where($array); // Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active' 4. Custom string
5. RawSql
6. Subqueries<?php // With closure $builder->where('advance_amount <', static fn (BaseBuilder $builder) => $builder->select('MAX(advance_amount)', false)->from('orders')->where('id >', 2)); // Produces: WHERE "advance_amount" < (SELECT MAX(advance_amount) FROM "orders" WHERE "id" > 2) // With builder directly $subQuery = $db->table('orders')->select('MAX(advance_amount)', false)->where('id >', 2); $builder->where('advance_amount <', $subQuery); $builder->orWhere()This method is identical to the one above, except that multiple instances are joined by OR: <?php $builder->where('name !=', $name); $builder->orWhere('id >', $id); // Produces: WHERE name != 'Joe' OR id > 50 $builder->whereIn()Generates a WHERE field IN (‘item’, ‘item’) SQL query joined with AND if appropriate: <?php $names = ['Frank', 'Todd', 'James']; $builder->whereIn('username', $names); // Produces: WHERE username IN ('Frank', 'Todd', 'James') You can use subqueries instead of an array of values: <?php // With closure $builder->whereIn('id', static fn (BaseBuilder $builder) => $builder->select('job_id')->from('users_jobs')->where('user_id', 3)); // Produces: WHERE "id" IN (SELECT "job_id" FROM "users_jobs" WHERE "user_id" = 3) // With builder directly $subQuery = $db->table('users_jobs')->select('job_id')->where('user_id', 3); $builder->whereIn('id', $subQuery); $builder->orWhereIn()Generates a WHERE field IN (‘item’, ‘item’) SQL query joined with OR if appropriate: <?php $names = ['Frank', 'Todd', 'James']; $builder->orWhereIn('username', $names); // Produces: OR username IN ('Frank', 'Todd', 'James') You can use subqueries instead of an array of values: <?php // With closure $builder->orWhereIn('id', static fn (BaseBuilder $builder) => $builder->select('job_id')->from('users_jobs')->where('user_id', 3)); // Produces: OR "id" IN (SELECT "job_id" FROM "users_jobs" WHERE "user_id" = 3) // With builder directly $subQuery = $db->table('users_jobs')->select('job_id')->where('user_id', 3); $builder->orWhereIn('id', $subQuery); $builder->whereNotIn()Generates a WHERE field NOT IN (‘item’, ‘item’) SQL query joined with AND if appropriate: <?php $names = ['Frank', 'Todd', 'James']; $builder->whereNotIn('username', $names); // Produces: WHERE username NOT IN ('Frank', 'Todd', 'James') You can use subqueries instead of an array of values: <?php // With closure $builder->whereNotIn('id', static fn (BaseBuilder $builder) => $builder->select('job_id')->from('users_jobs')->where('user_id', 3)); // Produces: WHERE "id" NOT IN (SELECT "job_id" FROM "users_jobs" WHERE "user_id" = 3) // With builder directly $subQuery = $db->table('users_jobs')->select('job_id')->where('user_id', 3); $builder->whereNotIn('id', $subQuery); $builder->orWhereNotIn()Generates a WHERE field NOT IN (‘item’, ‘item’) SQL query joined with OR if appropriate: <?php $names = ['Frank', 'Todd', 'James']; $builder->orWhereNotIn('username', $names); // Produces: OR username NOT IN ('Frank', 'Todd', 'James') You can use subqueries instead of an array of values: <?php // With closure $builder->orWhereNotIn('id', static fn (BaseBuilder $builder) => $builder->select('job_id')->from('users_jobs')->where('user_id', 3)); // Produces: OR "id" NOT IN (SELECT "job_id" FROM "users_jobs" WHERE "user_id" = 3) // With builder directly $subQuery = $db->table('users_jobs')->select('job_id')->where('user_id', 3); $builder->orWhereNotIn('id', $subQuery); Looking for Similar DataLike$builder->like()This method enables you to generate LIKE clauses, useful for doing searches. Note All values passed to this method are escaped automatically. Note All 1. Simple key/value method<?php $builder->like('title', 'match'); // Produces: WHERE `title` LIKE '%match%' ESCAPE '!' 2. Associative array method<?php $array = ['title' => $match, 'page1' => $match, 'page2' => $match]; $builder->like($array); // WHERE `title` LIKE '%match%' ESCAPE '!' AND `page1` LIKE '%match%' ESCAPE '!' AND `page2` LIKE '%match%' ESCAPE '!' 3. RawSql
$builder->orLike()This method is identical to the one above, except that multiple instances are joined by OR: <?php $builder->like('title', 'match'); $builder->orLike('body', $match); // WHERE `title` LIKE '%match%' ESCAPE '!' OR `body` LIKE '%match%' ESCAPE '!' $builder->notLike()This method
is identical to <?php $builder->notLike('title', 'match'); // WHERE `title` NOT LIKE '%match% ESCAPE '!' $builder->orNotLike()This method is identical to <?php $builder->like('title', 'match'); $builder->orNotLike('body', 'match'); // WHERE `title` LIKE '%match% OR `body` NOT LIKE '%match%' ESCAPE '!' $builder->groupBy()Permits you to write the GROUP BY portion of your query: <?php $builder->groupBy('title'); // Produces: GROUP BY title You can also pass an array of multiple values as well: <?php $builder->groupBy(['title', 'date']); // Produces: GROUP BY title, date $builder->distinct()Adds the DISTINCT keyword to a query <?php $builder->distinct(); $builder->get(); // Produces: SELECT DISTINCT * FROM mytable $builder->having()Permits you to write the HAVING portion of your query. There are 2 possible syntaxes, 1 argument or 2: <?php $builder->having('user_id = 45'); // Produces: HAVING user_id = 45 $builder->having('user_id', 45); // Produces: HAVING user_id = 45 You can also pass an array of multiple values as well: <?php $builder->having(['title =' => 'My Title', 'id <' => $id]); // Produces: HAVING title = 'My Title', id < 45 If you are using a database that CodeIgniter escapes queries for, you can prevent escaping content by passing an optional third argument, and setting it to <?php $builder->having('user_id', 45); // Produces: HAVING `user_id` = 45 in some databases such as MySQL $builder->having('user_id', 45, false); // Produces: HAVING user_id = 45 $builder->orHaving()Identical to $builder->havingIn()Generates a HAVING field IN (‘item’, ‘item’) SQL query joined with AND if appropriate: <?php $groups = [1, 2, 3]; $builder->havingIn('group_id', $groups); // Produces: HAVING group_id IN (1, 2, 3) You can use subqueries instead of an array of values: <?php // With closure $builder->havingIn('id', static fn (BaseBuilder $builder) => $builder->select('user_id')->from('users_jobs')->where('group_id', 3)); // Produces: HAVING "id" IN (SELECT "user_id" FROM "users_jobs" WHERE "group_id" = 3) // With builder directly $subQuery = $db->table('users_jobs')->select('user_id')->where('group_id', 3); $builder->havingIn('id', $subQuery); $builder->orHavingIn()Generates a HAVING field IN (‘item’, ‘item’) SQL query joined with OR if appropriate <?php $groups = [1, 2, 3]; $builder->orHavingIn('group_id', $groups); // Produces: OR group_id IN (1, 2, 3) You can use subqueries instead of an array of values: <?php // With closure $builder->orHavingIn('id', static fn (BaseBuilder $builder) => $builder->select('user_id')->from('users_jobs')->where('group_id', 3)); // Produces: OR "id" IN (SELECT "user_id" FROM "users_jobs" WHERE "group_id" = 3) // With builder directly $subQuery = $db->table('users_jobs')->select('user_id')->where('group_id', 3); $builder->orHavingIn('id', $subQuery); $builder->havingNotIn()Generates a HAVING field NOT IN (‘item’, ‘item’) SQL query joined with AND if appropriate <?php $groups = [1, 2, 3]; $builder->havingNotIn('group_id', $groups); // Produces: HAVING group_id NOT IN (1, 2, 3) You can use subqueries instead of an array of values: <?php // With closure $builder->havingNotIn('id', static fn (BaseBuilder $builder) => $builder->select('user_id')->from('users_jobs')->where('group_id', 3)); // Produces: HAVING "id" NOT IN (SELECT "user_id" FROM "users_jobs" WHERE "group_id" = 3) // With builder directly $subQuery = $db->table('users_jobs')->select('user_id')->where('group_id', 3); $builder->havingNotIn('id', $subQuery); $builder->orHavingNotIn()Generates a HAVING field NOT IN (‘item’, ‘item’) SQL query joined with OR if appropriate <?php $groups = [1, 2, 3]; $builder->havingNotIn('group_id', $groups); // Produces: OR group_id NOT IN (1, 2, 3) You can use subqueries instead of an array of values:
<?php // With closure $builder->orHavingNotIn('id', static fn (BaseBuilder $builder) => $builder->select('user_id')->from('users_jobs')->where('group_id', 3)); // Produces: OR "id" NOT IN (SELECT "user_id" FROM "users_jobs" WHERE "group_id" = 3) // With builder directly $subQuery = $db->table('users_jobs')->select('user_id')->where('group_id', 3); $builder->orHavingNotIn('id', $subQuery); $builder->havingLike()This method enables you to generate LIKE clauses for HAVING part or the query, useful for doing searches. Note All values passed to this method are escaped automatically. Note All 1. Simple key/value method<?php $builder->havingLike('title', 'match'); // Produces: HAVING `title` LIKE '%match%' ESCAPE '!' 2. Associative array method<?php $array = ['title' => $match, 'page1' => $match, 'page2' => $match]; $builder->havingLike($array); // HAVING `title` LIKE '%match%' ESCAPE '!' AND `page1` LIKE '%match%' ESCAPE '!' AND `page2` LIKE '%match%' ESCAPE '!' $builder->orHavingLike()This method is identical to the one above, except that multiple instances are joined by OR: <?php $builder->havingLike('title', 'match'); $builder->orHavingLike('body', $match); // HAVING `title` LIKE '%match%' ESCAPE '!' OR `body` LIKE '%match%' ESCAPE '!' $builder->notHavingLike()This method is identical to <?php $builder->notHavingLike('title', 'match'); // HAVING `title` NOT LIKE '%match% ESCAPE '!' $builder->orNotHavingLike()This method is identical to <?php $builder->havingLike('title', 'match'); $builder->orNotHavingLike('body', 'match'); // HAVING `title` LIKE '%match% OR `body` NOT LIKE '%match%' ESCAPE '!' Ordering ResultsOrderBy$builder->orderBy()Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by. The second parameter lets you set the direction of the result. Options are <?php $builder->orderBy('title', 'DESC'); // Produces: ORDER BY `title` DESC You can also pass your own string in the first parameter: <?php $builder->orderBy('title DESC, name ASC'); // Produces: ORDER BY `title` DESC, `name` ASC Or multiple method calls can be made if you need multiple fields. <?php $builder->orderBy('title', 'DESC'); $builder->orderBy('name', 'ASC'); // Produces: ORDER BY `title` DESC, `name` ASC If you choose the <?php $builder->orderBy('title', 'RANDOM'); // Produces: ORDER BY RAND() $builder->orderBy(42, 'RANDOM'); // Produces: ORDER BY RAND(42) Limiting or Counting ResultsLimit$builder->limit()Lets you limit the number of rows you would like returned by the query: <?php $builder->limit(10); // Produces: LIMIT 10 The second parameter lets you set a result offset. <?php $builder->limit(10, 20); // Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax) $builder->countAllResults()Permits you to determine the number of rows in a particular Query Builder query. Queries will accept Query Builder restrictors such as <?php echo $builder->countAllResults(); // Produces an integer, like 25 $builder->like('title', 'match'); $builder->from('my_table'); echo $builder->countAllResults(); // Produces an integer, like 17 However, this method also resets any field values that you may have passed to <?php echo $builder->countAllResults(false); // Produces an integer, like 17 $builder->countAll()Permits you to determine the number of rows in a particular table. Example: <?php echo $builder->countAll(); // Produces an integer, like 25 As is in Union queriesUnion$builder->union()Is used to combine the result-set of two or more SELECT statements. It will return only the unique results. <?php $union = $this->db->table('users')->select('id', 'name'); $builder = $this->db->table('users')->select('id', 'name'); $builder->union($union)->limit(10)->get(); /* * Produces: * SELECT * FROM (SELECT `id`, `name` FROM `users` LIMIT 10) uwrp0 * UNION SELECT * FROM (SELECT `id`, `name` FROM `users`) uwrp1 */ Note For correct work
with DBMS (such as MSSQL and Oracle) queries are wrapped in All union queries will be added after the main query, regardless of the order in which the In some cases, it may be necessary, for example, to sort or
limit the number of records of the query result. The solution is to use the wrapper created via <?php $union = $this->db->table('users')->select('id', 'name')->orderBy('id', 'DESC')->limit(5); $builder = $this->db->table('users')->select('id', 'name')->orderBy('id', 'ASC')->limit(5)->union($union); $this->db->newQuery()->fromSubquery($builder, 'q')->orderBy('id', 'DESC')->get(); /* * Produces: * SELECT * FROM ( * SELECT * FROM (SELECT `id`, `name` FROM `users` ORDER BY `id` ASC LIMIT 5) uwrp0 * UNION * SELECT * FROM (SELECT `id`, `name` FROM `users` ORDER BY `id` DESC LIMIT 5) uwrp1 * ) q ORDER BY `id` DESC */ $builder->unionAll()The behavior is the same as the Query groupingGroupQuery grouping allows you to create groups of WHERE clauses by enclosing them in parentheses. This will allow you to create queries with complex WHERE clauses. Nested groups are supported. Example: <?php $builder->select('*')->from('my_table') ->groupStart() ->where('a', 'a') ->orGroupStart() ->where('b', 'b') ->where('c', 'c') ->groupEnd() ->groupEnd() ->where('d', 'd') ->get(); /* * Generates: * SELECT * FROM (`my_table`) WHERE ( `a` = 'a' OR ( `b` = 'b' AND `c` = 'c' ) ) AND `d` = 'd' */
Note Groups need to be balanced, make sure every $builder->groupStart()Starts a new group by adding an opening parenthesis to the WHERE clause of the query. $builder->orGroupStart()Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with OR. $builder->notGroupStart()Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with NOT. $builder->orNotGroupStart()Starts a new group by adding an opening parenthesis to the WHERE clause of the query, prefixing it with OR NOT. $builder->groupEnd()Ends the current group by adding a closing parenthesis to the WHERE clause of the query. $builder->havingGroupStart()Starts a new group by adding an opening parenthesis to the HAVING clause of the query. $builder->orHavingGroupStart()Starts a new group by adding an opening parenthesis to the HAVING clause of the query, prefixing it with OR. $builder->notHavingGroupStart()Starts a new group by adding an opening parenthesis to the HAVING clause of the query, prefixing it with NOT. $builder->orNotHavingGroupStart()Starts a new group by adding an opening parenthesis to the HAVING clause of the query, prefixing it with OR NOT. $builder->havingGroupEnd()Ends the current group by adding a closing parenthesis to the HAVING clause of the query. Inserting DataInsert$builder->insert()Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the method. Here is an example using an array: <?php $data = [ 'title' => 'My title', 'name' => 'My Name', 'date' => 'My date', ]; $builder->insert($data); // Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date') The first parameter is an associative array of values. Here is an example using an object: <?php class Myclass { public $title = 'My Title'; public $content = 'My Content'; public $date = 'My Date'; } $object = new Myclass(); $builder->insert($object); // Produces: INSERT INTO mytable (title, content, date) VALUES ('My Title', 'My Content', 'My Date') The first parameter is an object. Note All values are escaped automatically producing safer queries. $builder->ignore()Generates an insert ignore string based on the data you supply, and runs the query. So if an entry with the same primary key already exists, the query won’t be inserted. You can optionally pass an boolean to the method. Can also be used on insertBatch, update and delete (when supported). Here is an example using the array of the above example: <?php $data = [ 'title' => 'My title', 'name' => 'My Name', 'date' => 'My date', ]; $builder->ignore(true)->insert($data); // Produces: INSERT OR IGNORE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date') $builder->getCompiledInsert()Compiles the insertion query just like Example: <?php $data = [ 'title' => 'My title', 'name' => 'My Name', 'date' => 'My date', ]; $sql = $builder->set($data)->getCompiledInsert(); echo $sql; // Produces string: INSERT INTO mytable (`title`, `name`, `date`) VALUES ('My title', 'My name', 'My date') The first parameter enables you to set whether or not the query builder query will be reset (by default it will be–just like <?php echo $builder->set('title', 'My Title')->getCompiledInsert(false); // Produces string: INSERT INTO mytable (`title`) VALUES ('My Title') echo $builder->set('content', 'My Content')->getCompiledInsert(); // Produces string: INSERT INTO mytable (`title`, `content`) VALUES ('My Title', 'My Content') The reason the second query worked is that the first parameter is set to Note This method doesn’t work for batch inserts. insertBatch$builder->insertBatch()Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the method. Here is an example using an array: <?php $data = [ [ 'title' => 'My title', 'name' => 'My Name', 'date' => 'My date', ], [ 'title' => 'Another title', 'name' => 'Another Name', 'date' => 'Another date', ], ]; $builder->insertBatch($data); // Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date') The first parameter is an associative array of values. Note All values are escaped automatically producing safer queries. Updating DataUpdate$builder->replace()This method executes a REPLACE
statement, which is basically the SQL standard for (optional) DELETE + INSERT, using PRIMARY and UNIQUE keys as the determining factor. In our case, it will save you from the need to implement complex logics with different combinations of Example: <?php $data = [ 'title' => 'My title', 'name' => 'My Name', 'date' => 'My date', ]; $builder->replace($data); // Executes: REPLACE INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date') In the above example, if we assume that the Usage of the $builder->set()This method enables you to set values for inserts or updates. It can be used instead of passing a data array directly to the insert() or update() methods: <?php $builder->set('name', $name); $builder->insert(); // Produces: INSERT INTO mytable (`name`) VALUES ('{$name}') If you use multiple method called they will be assembled properly based on whether you are doing an insert or an update: <?php $builder->set('name', $name); $builder->set('title', $title); $builder->set('status', $status); $builder->insert();
<?php $builder->set('field', 'field+1', false); $builder->where('id', 2); $builder->update(); // gives UPDATE mytable SET field = field+1 WHERE `id` = 2 $builder->set('field', 'field+1'); $builder->where('id', 2); $builder->update(); // gives UPDATE `mytable` SET `field` = 'field+1' WHERE `id` = 2 You can also pass an associative array to this method: <?php $array = [ 'name' => $name, 'title' => $title, 'status' => $status, ]; $builder->set($array); $builder->insert(); Or an object: <?php class Myclass { public $title = 'My Title'; public $content = 'My Content'; public $date = 'My Date'; } $object = new Myclass(); $builder->set($object); $builder->insert(); $builder->update()Generates an update string and runs the query based on the data you supply. You can pass an array or an object to the method. Here is an example using an array: <?php $data = [ 'title' => $title, 'name' => $name, 'date' => $date, ]; $builder->where('id', $id); $builder->update($data); /* * Produces: * UPDATE mytable * SET title = '{$title}', name = '{$name}', date = '{$date}' * WHERE id = $id */ Or you can supply an object: <?php class Myclass { public $title = 'My Title'; public $content = 'My Content'; public $date = 'My Date'; } $object = new Myclass(); $builder->where('id', $id); $builder->update($object); /* * Produces: * UPDATE `mytable` * SET `title` = '{$title}', `name` = '{$name}', `date` = '{$date}' * WHERE id = `$id` */ Note All values are escaped automatically producing safer queries. You’ll notice the use of the <?php $builder->update($data, 'id = 4'); Or as an array: <?php $builder->update($data, ['id' => $id]); You may also use the UpdateBatch$builder->updateBatch()Generates an update string based on the data you supply, and runs the query. You can either pass an array or an object to the method. Here is an example using an array: <?php $data = [ [ 'title' => 'My title', 'name' => 'My Name 2', 'date' => 'My date 2', ], [ 'title' => 'Another title', 'name' => 'Another Name 2', 'date' => 'Another date 2', ], ]; $builder->updateBatch($data, 'title'); /* * Produces: * UPDATE `mytable` SET `name` = CASE * WHEN `title` = 'My title' THEN 'My Name 2' * WHEN `title` = 'Another title' THEN 'Another Name 2' * ELSE `name` END, * `date` = CASE * WHEN `title` = 'My title' THEN 'My date 2' * WHEN `title` = 'Another title' THEN 'Another date 2' * ELSE `date` END * WHERE `title` IN ('My title','Another title') */ The first parameter is an associative array of values, the second parameter is the where key. Note All values are escaped automatically producing safer queries. Note
$builder->getCompiledUpdate()This works
exactly the same way as For more information view documentation for Note This method doesn’t work for batched updates. Deleting DataDelete$builder->delete()Generates a DELETE SQL string and runs the query. <?php $builder->delete(['id' => $id]); // Produces: DELETE FROM mytable WHERE id = $id The first parameter is the where clause. You can also use the <?php $builder->where('id', $id); $builder->delete(); /* * Produces: * DELETE FROM mytable * WHERE id = $id */ If you want to delete all data from a table, you can use the $builder->emptyTable()Generates a DELETE SQL string and runs the query: <?php $builder->emptyTable('mytable'); // Produces: DELETE FROM mytable $builder->truncate()Generates a TRUNCATE SQL string and runs the query. <?php $builder->truncate(); /* * Produce: * TRUNCATE mytable */ Note If the TRUNCATE command isn’t available, $builder->getCompiledDelete()This works exactly the same way as For more information view documentation for Method ChainingMethod chaining allows you to simplify your syntax by connecting multiple methods. Consider this example: <?php $query = $builder->select('title') ->where('id', $id) ->limit(10, 20) ->get(); Resetting Query BuilderResetQuery$builder->resetQuery()Resetting Query Builder allows you to
start fresh with your query without executing it first using a method like This is useful in situations where you are using Query Builder to generate SQL (e.g., <?php // Note that the second parameter of the ``get_compiled_select`` method is false $sql = $builder->select(['field1', 'field2']) ->where('field3', 5) ->getCompiledSelect(false); // ... // Do something crazy with the SQL code... like add it to a cron script for // later execution or something... // ... $data = $builder->get()->getResultArray(); /* * Would execute and return an array of results of the following query: * SELECT field1, field1 from mytable where field3 = 5; */ Class ReferenceclassCodeIgniter\Database\ BaseBuilder
db ()
Returns the current database connection from resetQuery ()
Resets the current Query Builder state. Useful when you want to build a query that can be cancelled under certain conditions. countAllResults ([$reset = true])
Generates a platform-specific query string that counts all records returned by an Query Builder query. countAll ([$reset = true])
Generates a platform-specific query string that counts all records in the particular table. get ([$limit = null[, $offset = null[, $reset = true]]]])
Compiles and runs getWhere ([$where = null[, $limit = null[, $offset = null[, $reset = true]]]]])
Same as select ([$select = '*'[, $escape = null]])
Adds a selectAvg ([$select = ''[, $alias = '']])
Adds a selectMax ([$select = ''[, $alias = '']])
Adds a selectMin ([$select = ''[, $alias = '']])
Adds a selectSum ([$select = ''[, $alias = '']])
Adds a selectCount ([$select = ''[, $alias = '']])
Adds a selectSubquery (BaseBuilder $subquery, string $as)
Adds a subquery to the selection distinct ([$val = true])
Sets a flag which tells the query builder to add a from ($from[, $overwrite = false])
Specifies the fromSubquery ($from, $alias)
Specifies the join ($table, $cond[, $type = ''[, $escape = null]])
Adds a where ($key[, $value = null[, $escape = null]])
Generates the orWhere ($key[, $value = null[, $escape =
null]])
Generates the orWhereIn ([$key = null[, $values = null[, $escape =
null]]])
Generates a orWhereNotIn ([$key = null[, $values = null[, $escape =
null]]])
Generates a whereIn ([$key = null[, $values = null[, $escape =
null]]])
Generates a whereNotIn ([$key = null[, $values = null[, $escape =
null]]])
Generates a groupStart ()
Starts a group expression, using orGroupStart ()
Starts a group expression, using notGroupStart ()
Starts a group expression, using orNotGroupStart ()
Starts a group expression, using groupEnd ()
Ends a group expression. like ($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch =
false]]]])
Adds a orLike ($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch =
false]]]])
Adds a notLike ($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch =
false]]]])
Adds a orNotLike ($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch =
false]]]])
Adds a having ($key[, $value = null[, $escape = null]])
Adds a orHaving ($key[, $value = null[, $escape =
null]])
Adds a orHavingIn ([$key = null[, $values = null[, $escape = null]]])
Generates a orHavingNotIn ([$key = null[, $values = null[, $escape =
null]]])
Generates a havingIn ([$key = null[, $values = null[, $escape =
null]]])
Generates a havingNotIn ([$key = null[, $values = null[, $escape =
null]]])
Generates a havingLike ($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch =
false]]]])
Adds a orHavingLike ($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch =
false]]]])
Adds a notHavingLike ($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch =
false]]]])
Adds a orNotHavingLike ($field[, $match = ''[, $side = 'both'[, $escape = null[, $insensitiveSearch =
false]]]])
Adds a havingGroupStart ()
Starts a group expression for orHavingGroupStart ()
Starts a group expression for notHavingGroupStart ()
Starts a group expression for orNotHavingGroupStart ()
Starts a group expression for havingGroupEnd ()
Ends a group expression for groupBy ($by[, $escape = null])
Adds a orderBy ($orderby[, $direction = ''[, $escape = null]])
Adds an limit ($value[, $offset = 0])
Adds offset ($offset)
Adds an union ($union)
Adds a unionAll ($union)
Adds a set ($key[, $value = ''[, $escape = null]])
Adds field/value pairs to be passed later to insert ([$set = null[, $escape = null]])
Compiles and executes an insertBatch ([$set = null[, $escape = null[, $batch_size = 100]]])
Compiles and executes batch Note When more than setInsertBatch ($key[, $value = ''[, $escape =
null]])
Adds field/value pairs to be inserted in a table later via update ([$set = null[, $where = null[, $limit = null]]])
Compiles and executes an updateBatch ([$set = null[, $value = null[, $batch_size =
100]]])
Compiles and executes batch Note When more than setUpdateBatch ($key[, $value = ''[, $escape =
null]])
Adds field/value pairs to be updated in a table later via replace ([$set = null])
Compiles and executes a delete ([$where = ''[, $limit = null[, $reset_data =
true]]])
Compiles and executes a increment ($column[, $value = 1])
Increments the value of a field by the specified amount. If the field is not a numeric field, like a decrement ($column[, $value =
1])
Decrements the value of a field by the specified amount. If the field is not a numeric field, like a truncate ()
Executes a Note If the database platform in use doesn’t support emptyTable ()
Deletes all records from a table via a getCompiledSelect ([$reset = true])
Compiles a getCompiledInsert ([$reset = true])
Compiles an getCompiledUpdate ([$reset = true])
Compiles an getCompiledDelete ([$reset = true])
Compiles a Mysqli_fetch_array untuk apa?Fungsi mysql_fetch_array() merupakan salah satu fungsi yang banyak digunakan dalam proses pengambilan data MySQL. Fungsi ini akan menangkap data dari hasil perintah query dan membentuknya ke dalam array asosiatif dan array numerik.
Apa itu mysql_num_rows?Fungsi mysql_num_rows() digunakan untuk mengetahui berapa banyak jumlah baris hasil pemanggilan fungsi mysql_query(). Fungsi ini membutuhkan 1 buah argumen, yakni variabel resources hasil dari fungsi mysql_query().
Apa itu Mysqli_fetch_assoc?Fungsi fetch_assoc() / mysqli_fetch_assoc() digunakan untuk mengambil baris hasil sebagai array asosiatif. Catatan: Nama kolom yang dikembalikan dari fungsi ini peka huruf besar / kecil(Case-sensitive).
Mysqli_query untuk apa?mysql_query atau mysqli_query adalah nama fungsi php untuk menjalankan instruksi atau argumen ke mysql.
|