Blog Details

How to execute Query in Model


                                
                                    <h2>*How to execute Query in Model:</h2>
<h2>        * Selecting Data:-</h2>
<h5>1) $this-&gt;db-&gt;get();</h5>
&nbsp;
<h6>-<strong>Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:</strong></h6>
&nbsp;

$query = $this-&gt;db-&gt;get('mytable');

// Produces: SELECT * FROM mytable

&nbsp;
<h6>-The second and third parameters enable you to set a limit and offset clause:</h6>
&nbsp;

$query = $this-&gt;db-&gt;get('mytable', 10, 20);

// Produces: SELECT * FROM mytable LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)
<h5>2) $this-&gt;db-&gt;where();</h5>
&nbsp;

<strong>2.1) Simple key/value method:</strong>

$this-&gt;db-&gt;where('name', $name);

// Produces: WHERE name = 'Joe'

<strong>2.2) Custom key/value method:</strong>

$this-&gt;db-&gt;where('name !=', $name);
$this-&gt;db-&gt;where('id &lt;', $id);

// Produces: WHERE name != 'Joe' AND id &lt; 45

<strong>2.3)  Associative array method:</strong>

$array = array('name' =&gt; $name, 'title' =&gt; $title, 'status' =&gt; $status);

$this-&gt;db-&gt;where($array);

// Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

<strong>2.4) Custom string:</strong>

$where = "name='Joe' AND status='boss' OR status='active'";

$this-&gt;db-&gt;where($where);
<h5>3) $this-&gt;db-&gt;select();</h5>
&nbsp;

$this-&gt;db-&gt;select('title, content, date');

$query = $this-&gt;db-&gt;get('mytable');

// Produces: SELECT title, content, date FROM mytable
<h5>4) $this-&gt;db-&gt;select_max();</h5>
&nbsp;

$this-&gt;db-&gt;select_max('age');
$query = $this-&gt;db-&gt;get('members');
// Produces: SELECT MAX(age) as age FROM members

$this-&gt;db-&gt;select_max('age', 'member_age');
$query = $this-&gt;db-&gt;get('members');
// Produces: SELECT MAX(age) as member_age FROM members
<h5>5) $this-&gt;db-&gt;select_min();</h5>
&nbsp;

$this-&gt;db-&gt;select_min('age');
$query = $this-&gt;db-&gt;get('members');
// Produces: SELECT MIN(age) as age FROM members
<h5>6) $this-&gt;db-&gt;select_avg();</h5>
&nbsp;

$this-&gt;db-&gt;select_avg('age');
$query = $this-&gt;db-&gt;get('members');
// Produces: SELECT AVG(age) as age FROM members
<h5>7) $this-&gt;db-&gt;select_sum();</h5>
&nbsp;

$this-&gt;db-&gt;select_sum('age');
$query = $this-&gt;db-&gt;get('members');
// Produces: SELECT SUM(age) as age FROM members
<h5>8) $this-&gt;db-&gt;from();</h5>
&nbsp;

$this-&gt;db-&gt;select('title, content, date');
$this-&gt;db-&gt;from('mytable');

$query = $this-&gt;db-&gt;get();

// Produces: SELECT title, content, date FROM mytable
<h5>9) $this-&gt;db-&gt;join();</h5>
&nbsp;

$this-&gt;db-&gt;select('*');
$this-&gt;db-&gt;from('blogs');
$this-&gt;db-&gt;join('comments', 'comments.id = blogs.id');

$query = $this-&gt;db-&gt;get();

// Produces:
// SELECT * FROM blogs
// JOIN comments ON comments.id = blogs.id
<h5>10) $this-&gt;db-&gt;or_where();</h5>
&nbsp;
<h6>-This function is identical to the one above, except that multiple instances are joined by OR:</h6>
$this-&gt;db-&gt;where('name !=', $name);
$this-&gt;db-&gt;or_where('id &gt;', $id);

// Produces: WHERE name != 'Joe' OR id &gt; 50
<h5>11) $this-&gt;db-&gt;where_in();</h5>
&nbsp;

$names = array('Frank', 'Todd', 'James');
$this-&gt;db-&gt;where_in('username', $names);

// Produces: WHERE username IN ('Frank', 'Todd', 'James');
<h5>12) $this-&gt;db-&gt;where_not_in();</h5>
&nbsp;
<h6>-Generates a WHERE field NOT IN ('item', 'item') SQL query joined with AND if appropriate</h6>
$names = array('Frank', 'Todd', 'James');
$this-&gt;db-&gt;where_not_in('username', $names);

// Produces: WHERE username NOT IN ('Frank', 'Todd', 'James')
<h5>13) $this-&gt;db-&gt;group_by();</h5>
&nbsp;

$this-&gt;db-&gt;group_by("title");

// Produces: GROUP BY title
<h6>-You can also pass an array of multiple values as well:</h6>
&nbsp;

$this-&gt;db-&gt;group_by(array("title", "date"));

// Produces: GROUP BY title, date
<h5>14) $this-&gt;db-&gt;distinct();</h5>
&nbsp;

$this-&gt;db-&gt;distinct();
$this-&gt;db-&gt;get('table');

// Produces: SELECT DISTINCT * FROM table
<h5>15) $this-&gt;db-&gt;having();</h5>
&nbsp;

$this-&gt;db-&gt;having('user_id = 45');

// Produces: HAVING user_id = 45
<h6>-You can also pass an array of multiple values as well:</h6>
&nbsp;

$this-&gt;db-&gt;having(array('title =' =&gt; 'My Title', 'id &lt;' =&gt; $id));

// Produces: HAVING title = 'My Title', id &lt; 45
<h5>16) $this-&gt;db-&gt;order_by();</h5>
&nbsp;
<h6>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 <kbd>asc</kbd> or <kbd>desc</kbd>, or <kbd>random</kbd>.</h6>
&nbsp;

$this-&gt;db-&gt;order_by("title", "desc");

// Produces: ORDER BY title DESC
<h5>17) $this-&gt;db-&gt;count_all();</h5>
&nbsp;

echo $this-&gt;db-&gt;count_all('<var>my_table</var>');

// Produces an integer, like 25
<h2>   *Inserting Data:-</h2>
<h5>1) $this-&gt;db-&gt;insert();</h5>
&nbsp;

$data = array(
'title' =&gt; 'My title' ,
'name' =&gt; 'My Name' ,
'date' =&gt; 'My date'
);

$this-&gt;db-&gt;insert('mytable', $data);

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date')

-The first parameter will contain the table name, the second is an associative array of values.
<h5>2) $this-&gt;db-&gt;insert_batch();</h5>
&nbsp;

$data = array(
array(
'title' =&gt; 'My title' ,
'name' =&gt; 'My Name' ,
'date' =&gt; 'My date'
),
array(
'title' =&gt; 'Another title' ,
'name' =&gt; 'Another Name' ,
'date' =&gt; 'Another date'
)
);

$this-&gt;db-&gt;insert_batch('mytable', $data);

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')

-The first parameter will contain the table name, the second is an associative array of values.
<h5>3) $this-&gt;db-&gt;set();</h5>
&nbsp;

<strong>-It can be used instead of passing a data array directly to the insert or update functions:</strong>

&nbsp;

$this-&gt;db-&gt;set('name', $name);
$this-&gt;db-&gt;insert('mytable');

// Produces: INSERT INTO mytable (name) VALUES ('{$name}')

&nbsp;

-<strong>If you use multiple function called they will be assembled properly based on whether you are doing an insert or an update:</strong>

&nbsp;

$this-&gt;db-&gt;set('name', $name);
$this-&gt;db-&gt;set('title', $title);
$this-&gt;db-&gt;set('status', $status);
$this-&gt;db-&gt;insert('mytable');
<h4>  *Updating Data:-</h4>
&nbsp;
<h5>1) $this-&gt;db-&gt;update();</h5>
&nbsp;

$data = array(
'title' =&gt; $title,
'name' =&gt; $name,
'date' =&gt; $date
);

$this-&gt;db-&gt;where('id', $id);
$this-&gt;db-&gt;update('mytable', $data);

// Produces:
// UPDATE mytable
// SET title = '{$title}', name = '{$name}', date = '{$date}'
// WHERE id = $id

&nbsp;
<h4>2) $this-&gt;db-&gt;where();</h4>
&nbsp;

$this-&gt;db-&gt;update('mytable', $data, "id = 4");
<h5>3) $this-&gt;db-&gt;update_batch();</h5>
&nbsp;

- Generates an update string based on the data you supply, and runs the query. You can either pass an <strong>array</strong> or an <strong>object</strong> to the function. Here is an example using an array:

array(
'title' =&gt; 'My title' ,
'name' =&gt; 'My Name 2' ,
'date' =&gt; 'My date 2'
),
array(
'title' =&gt; 'Another title' ,
'name' =&gt; 'Another Name 2' ,
'date' =&gt; 'Another date 2'
)
);

$this-&gt;db-&gt;update_batch('mytable', $data, 'title');

&nbsp;
<h2>   * Deleting Data:-</h2>
&nbsp;
<h5>1) $this-&gt;db-&gt;delete();</h5>
&nbsp;

$this-&gt;db-&gt;where('id', $id);
$this-&gt;db-&gt;delete('mytable');

// Produces:
// DELETE FROM mytable
// WHERE id = $id

&nbsp;
<h6>-An array of table names can be passed into delete() if you would like to delete data from more than 1 table.</h6>
&nbsp;

$tables = array('table1', 'table2', 'table3');
$this-&gt;db-&gt;where('id', '5');
$this-&gt;db-&gt;delete($tables);
<h5>2) $this-&gt;db-&gt;empty_table();</h5>
&nbsp;

-If you want to delete all data from a table, you can use the <strong><dfn>empty_table()</dfn></strong> function.

$this-&gt;db-&gt;empty_table('mytable');

// Produces
// DELETE FROM mytable
<h4>3) $this-&gt;db-&gt;truncate();</h4>
&nbsp;

-If you want to delete all data from a table, you can use the truncate() function.

$this-&gt;db-&gt;from('mytable');
$this-&gt;db-&gt;truncate();
// or
$this-&gt;db-&gt;truncate('mytable');

// Produce:
// TRUNCATE mytable
                                    


Categories

CodeIgnitor

Latest posts

13.05.14
Implementing CRUD Operations ¶

<ul> <li>Now comes the fun part. We would like t

13.05.14
View

<ol> <li><a href="http://www.yiiframework.com/do

13.05.14
Creating Model

<ol> <li><a href="http://www.yiiframework.com/do

Get Free Quote