SQL query in CodeIgnitor



1 Inserting Data

1.1 $this->db->insert();

This SQL statement generates an insert string based on the data  supplied. You can either pass an array of data or an object to the function. Example of  using an array in SQL Statement:

$data = array(
   'sn' => 106 ,
   'name' => 'Tapan' ,
   'college' => 'ASCOL'
);

 $this->db->insert('mytablename', $data);

// This SQL Statament produces: INSERT INTO mytablename (sn, name, college) VALUES (106, 'Tapan', 'ASCOL')
The first parameter will contain the name of table, the second is an associative array of values.

Here is an example using an object:

/*
    class Mydata {
        var $sn = 106;
        var $name = 'Tapan';
        var $college = 'ASCOL';
    }
*/

$object = new Mydata;

$this->db->insert('mytablename', $object);

// This SQL statement produces: 

 INSERT INTO mytablename (sn, name, college) VALUES (106, 'Tapan', 'ASCOL');

The first parameter will contain the name of table, the second is an object.

1.2 $this->db->set();

This SQL Statement enables you to set values for insertion or update of data into database.

It can be used instead to pass a data array directly to the insert or update SQL Statement:

$this->db->set('address', $address);
$this->db->insert('mytablename');

// This SQL statement produces: 

 INSERT INTO mytablename (address) VALUES ('{$address}')

we can do multiple update or insert as follows:

$this->db->set('name', $name);
$this->db->set('title', $title);
$this->db->set('status', $status);
$this->db->insert('mytablename');

2 Updating Data

The Update SQL statement is used to update the existing data of the database.

2.1 $this->db->update();


The Update SQL statement runs the query based on the data  supplied. You can pass an array of data or an object to the function. Example of using an array:

$mydata = array(
               'sn' => $sn,
               'name' => $name,
               'address' => $address
            );

$this->db->where('id', $id);
$this->db->update('mytablename', $mydata);

// Produces:
// UPDATE mytablename
// SET sn = '{$sn}', name = '{$name}', address = '{$address}'
// WHERE id = $id

Or you can supply an object:

/*
    class Mydata {
        var $sn = 106;
        var $name = 'Tapan';
        var $college = 'ASCOL';
    }
*/

$object = new Mydata;

$this->db->where('id', $id);
$this->db->update('mytablename', $object);

// Produces:
// UPDATE mytablename
// SET sn = '{$sn}', name = '{$name}', college = '{$college}'
// WHERE id = $id

Note: All values are in SQL Statement are escaped automatically to produce safer queries.

3 Deleting Data

3.1 $this->db->delete();

This SQL statement generates a delete SQL string and runs the SQL query.

$this->db->delete('mytablename', array('id' => $id));

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

The first parameter is the name of table, the second is the where clause in SQL Statement. You can also use the where() or or_where() functions instead of passing the data to the second parameter of the SQL Query:

$this->db->where('id', $id);
$this->db->delete('mytablename');

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

An array of table names can be passed into delete() query if you would like to delete data from more than 1 table.

$tables = array('mytablename1', 'mytablename2', 'mytablename3');
$this->db->where('id', '106');
$this->db->delete($mytablenames);

If you want to delete all data from a table, you can use the truncate() SQL Query, or empty_table() SQL query.

$this->db->empty_table();

This SQL Statement generates a delete SQL string and runs the query.
$this->db->empty_table('mytablename');

// This SQL Statement generates
// DELETE FROM mytablename

$this->db->truncate();

This SQL Statement generates a truncate SQL string and runs the query.

$this->db->from('mytablename');
$this->db->truncate();
// or
$this->db->truncate('mytablename');

// Produce:
// TRUNCATE mytablename
Note: If the TRUNCATE command isn't available, truncate() will execute as "DELETE FROM table".
We keep on updating such tutorials. 

For more such tutorials like our Facebook page. 


Comments

Post a Comment

Popular posts from this blog

INTEGRATE BOOTSTRAP WITH PHP CODEIGNITER FRAMEWORK

Image
This tutorial is about the technique of combining the power of two robust frameworks, PHP CodeIgniter MVC framework and Twitter Bootstrap. You need to properly integrate twitter bootstrap  with CodeIgniter framework for it to work properly.  So this tutorial will show you how to integrate Bootstrap with CodeIgniter to enhance the appearance of your CodeIgniter application. Download the latest versions of the frameworks from the links given below. Download CodeIgniter Framework   Download Twitter Bootstrap  Download jQuery Steps for integration of CodeIgniter framework with bootstrap STEP-1: Download and extract the PHP CodeIgniter Framework  zip file from the above link to your Local Server. Rename it with your application name.  Inside create a folder named "assets" to keep all your bootstrap files.  CodeIgnitor folder structure must be  similar to the below image.  STEP-2:  Extract all the files of boots...
Read more

CodeIgnitor installing guide

Image
Step 1: Downloading CodeIgnitor MVC Framework To install CodeIgnitor MVC Framework, first download the CodeIgnitor zip file of  from  http://www.codeigniter.com/  . Step 2: Installing CodeIgnitor Unzip the CodeIgnitor  zip compressed file  downloaded from http://www.codeigniter.com/ and rename the name of this folder to the application name of your project. Now move the folder inside your local server.  Incase of WAMPP, move this folder inside the www folder of your localhost. Incase of XAMP, move this folder inside the htdocs folder of your localhost. The basic file structure of CodeIgnitor application is as follows. Step 3:Configuring CodeIgnitor Before starting the CodeIgnitor application, you need to configure certain configuration. You need to step up right base url of the application. To setup base_url of the application, open  system/application/config/ c...
Read more

Selecting data from database in CodeIgnitor

Image
PHP and CodeIgnitor SQL queries allow us to  retrieve, insert and update data and information in your database. CodeIgnitor uses Active record database pattern. The values are escaped automatically by the system so it helps to produce the   safer queries . SQL queries in CodeIgnitor helps in retrieval of  data from database. The following SQL query functions allow you to build SQL  SELECT  statements. 1.1 $this->db->get(); This SQL Query is used to retrieve all the records from database. $query = $this->db->get('tableName'); // It produces SELECT * FROM table_name statement The second and third parameters in SQL query enable you to set a limit and offset clause: $query = $this->db->get('tableName', 20, 30); // Produces: SELECT * FROM tableName LIMIT 30, 20 (in MySQL.  Above SQL Query is assigned to a variable named $query, which can be used to show the results: $query = $this->db-...
Read more