Database Interaction with Codeigniter 1.7

Exclusive offer: get 50% off this eBook here
CodeIgniter 1.7

CodeIgniter 1.7 — Save 50%

Improve your PHP coding productivity with the free compact open-source MVC CodeIgniter framework!

$23.99    $12.00
by Adam Griffiths | April 2010 | Open Source PHP Web Development

Having seen form validation in the previous article, we will now cover database interaction. Databases are the backbone behind any Web application. Without a database, you'd have nowhere to hold all of your data, and SQL queries can become long and cumbersome to type out. Thankfully, CodeIgniter gives us a brilliantly simple way to interact with our Database. The database library also makes changing between database types—from MySQL to Oracle, for example—easier, because it acts as a wrapper and provides many functions for us to use on the database.

In this article by Adam Griffiths, author of CodeIgniter 1.7 Professional Development, you will:

  • Perform database queries with the database library
  • Return query results using the database helper functions
  • Create queries using the active record library
  • Learn how to cache active record queries
  • Modify database tables using database forge

(Read more interesting articles on CodeIgniter 1.7 Professional Development here.)

Loading the library

Loading the Database library is slightly different from loading other libraries. This is because it is large and resides in a different folder, unlike the other libraries.

$this->load->database();

Performing simple queries

Let's dive straight in by starting with the simple stuff. CodeIgniter gives us a function that we can pass a SQL Query to, and the query will be run on the database. Here's how it works:

$this->db->query('PUT YOUR SQL HERE');

This function is incredibly simple to use; you simply use this function in place of any native PHP functions you would use to run queries. This function will return TRUE or FALSE for write queries, and will return a dataset for read queries.

There is another function that you can use for very simple queries; this will only return TRUE or FALSE. It won't let you cache your query or run the query timer. In most cases you won't want to use this function.

$this->db->simple_query('PUT YOUR SQL HERE');

The SQL code that you pass to these functions are database-dependent. Only Active Record queries are independent of any type of Database SQL.

Returning values

You can assign the function $this->db->query() to a variable. You can then run a number of helper functions on the variable in order to return the data in different formats. Take the following example:

$query = $this->db->query('SELECT * FROM 'users'');

Return a result object

In this case, returning the result will return an array of objects, or an empty array if the query failed. You would usually use this function in a foreach loop.

foreach($query->result() as $row)
{
echo $row->username;
echo $row->email;
}

If your query does not return a result, the CodeIgniter User Guide encourages you to check for a failure before using this function.

if($query->num_rows > 0)
{
foreach($query->result() as $row)
{
echo $row->username;
echo $row->email;
}
}

Returning a result array

You are also able to return the result dataset as an array. Typically, you would use this function inside a foreach loop as well.

foreach($query->result_array() as $row)
{
echo $row['username'];
echo $row['email'];
}

Returning a row object

If your query is only expected to return a single result, you should return the row by using the following function. The row is returned as an object.

if($query->num_rows() > 0)
{
$row = $query->row();
echo $row->username;
echo $row->email;
}

You can return a specific row by passing the row number as a digit in the first parameter.

$query->row(2);

Returning a row array

You can return a row as an array, if you prefer. The function is used in the same way as the previous example.

if($query->num_rows() > 0)
{
$row = $query->row_array();
echo $row['username'];
echo $row['email'];
}

You can return a numbered row by passing the digit to the first parameter, also.

$query->row_array(2);

Result helper functions

Besides the helper function that helps to return the dataset in different ways, there are some other more generalized helper functions.

Number of rows returned

Used in the same way as the other helper functions, this will return the total number of rows returned from a query. Take the following example:

echo $query->num_rows();

Number of fields returned

Just like the previous function, this will return the number of fields returned by your query.

echo $query->num_fields();

Free result

This function will remove the resource ID associated with your query, and free the associated memory. PHP will usually do this by default, although when using many queries you may wish to use this to free up memory space.

$query->free_result();

CodeIgniter 1.7 Improve your PHP coding productivity with the free compact open-source MVC CodeIgniter framework!
Published: November 2009
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

(Read more interesting articles on CodeIgniter 1.7 Professional Development here.)

Active Record

The Active Record in CodeIgniter is quite different to the AR you may find in Rails or other frameworks. The way that Active Record in CodeIgniter works is that you build up your queries using different functions. For simple queries, you might only need to use one or two functions, but for some you may need to use more—for example, if you have to look for certain conditionals, such as where a username and password are the same.

Selecting data

All of the functions in this section will build SQL SELECT queries. All of the SQL in this section is MySQL; other database systems may differ slightly.

$this->db->get();

The simplest query that you can do with Active Record is to select a full database table. This is done with one single function:

$this->db->get();`

This would create the SQL query:

SELECT * FROM 'table_name'

This function has three parameters. The first is the name of the database table. The second lets you set a limit, and the third lets you set an offset.

$query = $this->db->get('table_name', 10, 20);

This would then produce the SQL query:

SELECT * FROM `table_name` LIMIT 20, 10

$this->db->get_where();

This function works in much the same way as the previous function. The only difference is that the second parameter should be passed as an array. The array should have the name of the field and the value to use to fill in the WHERE part of your query.

$query = $this->db->get('table_name', array('id' => $id), 10, 20);

This would produce the following SQL query:

SELECT * FROM 'table_name' WHERE 'id' = $id LIMIT 10, 20

$this->db->select();

This function allows you to write the SELECT portion of your query. Take a look at the following example:

$this->db->select('name, username, email');
$query = $this->db->get('users');

The SQL query produced from this function will be:

SELECT name, username, email FROM 'users'

You should take note that when using this function, and any of the other functions that let you write a portion of your query, that you still need to use the get() function to actually produce and run the query. If you are selecting everything form your database (*) then you do not need to use this function as CodeIgniter assumes that you mean to select everything.

$this->db->from();

This function allows you to write the FROM portion of your query. This is basically the same as using the get() function, although it is slightly more readable. You can use whichever method you prefer.

$this->db->from('table_name');
$query = $this->db_>get();

$this->db->join();

This function lets you write the JOIN part of your query. Here's an example:

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

You can specify a different type of join in the third parameter. You can choose from left, right, outer, inner, left outer, and right outer.

$this->db->join('comments', 'comments.id = blogs.id', 'left');

$this->db->where();

This function is used to build the WHERE portion of your query. This function can be used in a variety of ways.

Single key or value method

$this->db->where('name', $name);

This will produce the following SQL query. Note that the equals sign has been added for your convenience.

WHERE 'name' = '$name'

Multiple key or value method

$this->db->where('name', $name);
$this->db->where('email', $email);

These two clauses will be appended to each other, and the word AND will be placed in between them.

Custom key or value method

$this->db->where('name !=', $name);
// Produces WHERE 'name' != '$name'

Associative array method

You can pass values to the where() function by using an associative array. Take a look at the following example:

$array = array('name' => $name, 'email' => $email);
$this->db->where($array);

You can include operators in the array, just as you would use in the first parameter.

$array = array('name !=' => $name, 'email' => $email);
$this->db->where($array);

Custom string

You can write your own custom WHERE clauses by passing a string to the first parameter.

$where = "name = 'Billy' AND job_title = 'MD'";
$this->db->where($where);

The where() function includes an optional third parameter. When set to FALSE, CodeIgniter will not try to protect your queries by adding backticks.

$this->db->like();

This function allows you to write the LIKE portion of your query, and functions in almost the exact same way as the where() method.

Single key or value method

This method is the same as the where() method, when used as follows:

$this->db->like('name', $name);

Multiple key or value method

This method is also the same as the where() method. Multiple calls will be chained together.

$this->db->like('name', $name);
$this->db->like('email', $email);

This function actually has three parameters. The first is the title of the field, the second is the match, and the third specifies where you want the wildcard to be placed. Your options are before, after and both (both is the default).

Associative array method

You can pass values to the like() function by using an associative array. Take a look at the following example:

$array = array('name' => $name, 'email' => $email);
$this->db->like($array);

$this->db->group_by();

This function lets you write the GROUP BY portion of your query.

$this->db->group_by('name');
$this->db->group_by(array('name', 'title'));

$this->db->order_by();

This lets you write the ORDER BY portion of your query. The first parameter is for your field name. The second is the type of order that you want to use, and can be asc, desc, or random.

Random sorting is not currently supported in Oracle or MSSQL Drivers. These will default to asc.

$this->db->order_by('name', 'desc');

You can also pass a string as the first parameter.

$this->db->order_by('name desc, title asc');

Multiple function calls can also be used, as for other functions.

$this->db->order_by('name', 'desc');
$this->db->order_by('title', 'asc');

$this->db->limit();

This function lets you add a LIMIT specification to your query. The first parameter will be the number to limit to, and the second parameter let's you set an offset.

$this->db->limit(10, 20);

Inserting data

Inserting data using Active Record is a very simple process, and there are just two functions that you may need to use in order to insert data into your database.

$this->db->insert();

This will generate an insert string based upon the data that you supply to it. The first parameter is the name of the table that you want to add the data to, and the second parameter can either be an array or an object of the data.

$data = array('name' => 'Bob Smith',
'email' => 'bob@smith.com');
$this->db->insert('table_name', $data);

This would then produce the following SQL statement:

INSERT INTO mytable (name, email)
VALUES ('Bob Smith', 'bob@smith.com')

All values will be properly escaped before being inserted into the database.

$this->db->set();

This function lets you set data for inserts or updates to your table. This can be used in place of passing an array of data to the insert or update function.

$this->db->set('name', 'Bob Smith');
$this->db->insert('table_name');

If you use multiple function calls they will be properly formatted, depending on whether you are performing an update or an insert.

This function also supports a third parameter. When set to FALSE, this third parameter will prevent data from being escaped.

You can also pass an associative array to this function.

$array = array('name' =>'Bob Smith',
'email' => 'bob@smith.com');
$this->db->set($array);
$this->db->insert('table_name');

Updating data

Updating data is a highly important part of any web application. CodeIgniter makes this really simple to do. The update function works in largely the same way as the insert function.

$this->db->update();

This will generate an update string based upon the data that you supply to it. The first parameter is the name of the table you want to add the data to, and the second parameter can either be an array or an object of the data. The third, optional parameter enables you to set the WHERE clause of your SQL query.

$data = array('name' => 'Bob Smith',
'email' => 'bob@smith.com');
$this->db->where('id', 5);
$this->db->update('table_name', $data);

This would then produce the following SQL statement:

UPDATE mytable SET name = 'Bob Smith', email = 'bob@smith.com'

All values will be properly escaped before being inserted into the database.

You can optionally use the where() method to set the where clause of the query. Here's how you would use the third parameter to set the WHERE clause:

$data = array('name' => 'Bob Smith',
'email' => 'bob@smith.com');
$this->db->update('table_name', $data, 'id = 5');

Just as for inserting data, you may use the set() method in place of an array.

Deleting data

You can delete data from your tables in a variety of ways. You can either delete fields from a database or empty a database.

$this->db->delete();

This function accepts two parameters. The first is the name of the table, and the second should be an array from which to build the WHERE clause.

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

You can also use the where() function to build the WHERE clause:

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

An array of table names can be passed into this function, if you wish to delete more than one table.

$tables = array('table1', 'table2', 'table3');
$this->db->where('id', '5');
$this->db->delete($tables);

$this->db->empty_table();

This function provides an easy way to delete all of the data from a table. Simply pass the name of the table to the first parameter, to empty it.

$this->db->empty_table('table_name');

$this->db->truncate();

This function will generate a TRUNCATE command and run it. It can be used in two ways:

$this->db->from('table_name');
$this->db->truncate();

or

$this->db->truncate('table_name');

Use whichever you feel more comfortable with, or the one that you find is most readable.

Active Record caching

Although the Active Record caching functionality provided by CodeIgniter is not true caching, it enables you to save a query for use later on in your script execution. Usually, an SQL query is reset after it has been completed. With Active Record caching you can prevent this reset and reuse queries easily.

The three caching functions available are listed next.

$this->db->start_cache();

This function must be called in order to begin the caching process. Not all queries can be cached. The cacheable queries are as follows:

  • SELECT
  • FROM
  • JOIN
  • WHERE
  • LIKE
  • GROUPBY
  • HAVING
  • ORDERBY
  • SET

$this->db->stop_cache();

This function must be called in order to stop caching.

$this->db->flush_cache();

This function will delete all items from the Active Record cache.

Here's an example of Active Record caching:

$this->db->start_cache();
$this->db->select('field_1');
$this->db->stop_cache();
$this->db->get('tablename');
// Produces: SELECT 'field_1' FROM ('tablename')
$this->db->select('field_2');
$this->db->get('tablename');
// Produces: SELECT 'field_1', 'field_2' FROM ('tablename')
$this->db->flush_cache();
$this->db->select('field_2');
$this->db->get('tablename');
// Produces: SELECT 'field_2' FROM ('tablename')

Method chaining

Method chaining is available to everyone using PHP5. It enables you to vastly simplify your syntax by connecting multiple functions. Take a look at this example

$this->db->select('name, email')->from('users')->
where('id', 5)->limit(10, 20);
$query = $this->db->get();

This would produce the following SQL statement:

SELECT name, email FROM 'users' WHERE 'id' = 5 LIMIT 10, 20

Manipulating databases with Database Forge

The Database Forge class includes functions that enable you to perform operations on your databases and create new database schemas.

Loading the Forge class

Loading the Forge class is similar to loading the Database class.

$this->load->dbforge();

Once loaded, you can use all of the functions shown in this section.

Creating a database

Creating a database by using dbforge is fairly simple. It only takes a single function. The function will return TRUE or FALSE, depending upon the success or failure of the call.

if ($this->dbforge->create_database('my_db'))
{
echo 'Database created!';
}

Dropping a database

Dropping a database is much like creating a database. It, too, only takes a single function, which returns TRUE or FALSE depending upon the success or failure of the call.

if ($this->dbforge->drop_database('my_db'))
{
echo 'Database deleted!';
}

Adding fields

To add fields to your database table, you use the function $this->dbforge->add_field();. Once the fields have been defined, a call to the create_table function should be made.

You can add fields by using a multi-dimensional array. Here's an example:

$fields = array(
'users' =>
array(
'type' => 'VARCHAR',
'constraint' => '100',
)
);

When adding a field like this, each defined field must have a 'type' key. This relates to the data type of the field. Some types require a constraint key. In this example the VARCHAR is limited to 100 characters.

Creating an ID field has it's own exception. To create an ID, you need to use the following function. ID fields are automatically assigned as an INT(9) auto_incrementing Primary Key.

$this->dbforge->add_field('id');

Creating a table

After fields and keys have been defined, you can create a table by using the following function:

$this->dbforge->create_table('table_name');

An optional second parameter will add IF NOT EXISTS when set to TRUE.

$this->dbforge->create_table('table_name', TRUE);

Dropping a table

Dropping a table is made very simple with the Forge Class. Just a single function is needed:

$this->dbforge->drop_table('table_name');

Renaming a table

Renaming a table takes one function. The first parameter is the old table name, and the second is the new table name.

$this->dbforge->rename_table('old_table_name', 'new_table_name');

Modifying a table

There are a few functions that you can use in order to modify a table. You can add, modify, and drop columns.

$this->dbforge->add_column();

This function accepts two parameters. The first is the name of the table that you wish to add to, and the second is an array of the columns that you wish to add.

$fields = array(
'preferences' =>
array('type' => 'TEXT')
);
$this->dbforge->add_column('table_name', $fields);

$this->dbforge->drop_column();

This function is used to drop a column from a table. There are two parameters: the first should be the table name and the second should be the name of the table that you wish to drop.

$this->dbforge->drop_column('table_name', 'column_to_drop');

$this->dbforge->modify_column();

This function is identical to that of the add_column function, except that it renames the column.

$fields = array(
'old_name' => array(
'name' => 'new_name',
'type' => 'TEXT',
),
);
$this->dbforge->modify_column('table_name', $fields);

Summary

That's it. There you have it; you've now learned how to validate your forms and use the Database library properly.


If you have read this article you may be interested to view:


CodeIgniter 1.7 Improve your PHP coding productivity with the free compact open-source MVC CodeIgniter framework!
Published: November 2009
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Adam Griffiths

Adam Griffiths is a student and freelance CodeIgniter Developer based in the United Kingdom. He has five years web development experience, the last two being largely influenced by CodeIgniter. He has worked on many websites, both large and small, from small blogs to large multi-national companies. He is well versed in development techniques and how to squeeze that little bit more from an application. He has also made a number of contributions to the CodeIgniter Community, most notably The Authentication Library, a very simple-to-use but full-featured Authentication Library for CodeIgniter.

When CodeIgniter and PHP aren't spiralling around his head, Adam enjoys practising card and mentalist tricks, mainly sleight of hand and card handling. He has performed at local and formal functions for hundreds of people. He is also a guitar player and enjoys playing acoustically at pubs and small gigs. Moving back towards computing, he has a deep interest in Cryptography. He loves finding patterns in data and loves using pen and paper to decipher any cipher text he may find around the web.

Books From Packt


jQuery 1.4 Reference Guide
jQuery 1.4 Reference Guide

Magento 1.3: PHP Developer's Guide
Magento 1.3: PHP Developer's Guide

Expert PHP 5 Tools
Expert PHP 5 Tools

Joomla! 1.5: Beginner's Guide
Joomla! 1.5: Beginner's Guide

WordPress 2.8 Themes Cookbook
WordPress 2.8 Themes Cookbook

Plone 3 Multimedia
Plone 3 Multimedia

Django 1.1 Testing and Debugging
Django 1.1 Testing and Debugging

AJAX and PHP: Building Modern Web Applications 2nd Edition
AJAX and PHP: Building Modern Web Applications 2nd Edition


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software