CodeIgniter Union Query Example

On 8/24/2016 Be the first to comment!

CodeIgniter Union Query: This tutorial will show you how to write union query in codeigniter. First let's set one thing clear. CodeIgniter Active Records doesn't support UNION queries! This doesn't mean you can't fire union queries in codeigniter. Always you can go with native sql statement and the framework supports it. But as good practice don't do this unless you have to.

Here I'll show you a workaround using active records to write union query in code igniter. Still it uses query() function but much better than the sql queries.

How to Write Union Query in CodeIgniter?

As I said earlier, you can't write pure active records query for table unions - because the framework supports only those SQL features which are compatible with all of its supported SQL types. As for UNION, you have to write sql query and execute it with ActiveRecord's query() method like below.

$sql = 'SELECT Name, Email FROM Customers UNION SELECT Name, Email FROM Suppliers';
$query = $this->db->query($sql);

Alternative Method:

This method employs slightly better approach than the previous one. It produces query strings from active records without actually running the queries, then 'union' them with $this->db->query(); function. Here is how to do it.

$this->db->select('Name, Email');
$this->db->from('Customers');
$query1 = $this->db->get_compiled_select();

$this->db->select('Name, Email');
$this->db->from('Suppliers');
$query2 = $this->db->get_compiled_select();

$query = $this->db->query($query1 . ' UNION ' . $query2);

// Produces SQL:
// SELECT Name, Email FROM Customers
// UNION
// SELECT Name, Email FROM Suppliers;

The method $this->db->get_compiled_select(); is introduced in codeigniter v3.0 and compiles active records query without actually executing it. But this is not a completely new method. In older versions of CI it is like $this->db->_compile_select(); but the method has been made protected in later versions making it impossible to call back.

CodeIgniter Union All Query:

To run union all query in codeignitar, just use 'UNION ALL' operator instead of 'UNION' in query function.

$query = $this->db->query($query1 . ' UNION ALL ' . $query2);
Read:

So that explains about writing union queries in codeigniter framework. Meet you in another useful post.

CodeIgniter Download File Example (From URL / Server)

On 8/22/2016 Be the first to comment!

CodeIgniter Download File: This tutorial will show you how to download files in codeigniter. Just like uploading files in codeigniter you can download them too - and the PHP framework packs a nice helper function to force file download. You have to use the function force_download() to download file from server and it takes up two parameters, the first one being the name of downloadable file and second one is the file content itself.

Here is the syntax for the said method:

force_download($file_name, $content);

CodeIgniter Download File from URL/Server:

To download file in codeigniter, use the below given function in any of the controller and from the view interface callback the function with a filename you need to download from server.

function download($filename = NULL) {
    // load download helder
    $this->load->helper('download');
    // read file contents
    $data = file_get_contents(base_url('/uploads/'.$filename));
    force_download($filename, $data);
}

Function file_get_contents() reads the contents of the file into a variable. This step is mandatory if you want to download an existing file from the server.

Then in the view file, add a download button which force file download when it is clicked.

<?php $fname='PHPCookBook.pdf'; ?>
<a href="<?php echo base_url(); ?>/index.php/controller_name/download/<?php echo $fname; ?>">Download PDF</a>
codeigniter-download-file-from-url-server-example

If you keep codeigniter url without index.php then skip the part /index.php from the above href url.

Also Read: CodeIgniter File Upload Example

So, by this way you can download files in codeigniter without hassle. I hope you find this tutorial useful. Keep in touch to get updated with more code igniter tricks.

How to Submit Form using AJAX in CodeIgniter

On 8/18/2016 Be the first to comment!

Hi! This post will show you how to submit form using ajax in codeigniter. Generally when you submit html forms - page will be refreshed. But jQuery's ajax() method makes it possible to submit form, post and receive data without refreshing the page. Yep! A cool feature and so many modern websites use this technique to send & receive asynchronous HTTP request. And here we'll see how to use ajax in code igniter for form submission.

CodeIgniter Submit Form using AJAX Example:

Let's create a demo to submit form using ajax in codeigniter. As for this demo, consider you have a user subscription form with an input box for 'email id' and a 'subscribe' button - this is the form which you want to submit using ajax.

submit-form-using-ajax-in-codeigniter-example

First we'll need to create a controller for the demo and this would be it.

Controller: "ajax_demo.php"

<?php
class ajax_demo extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->helper(array('form','url'));
        $this->load->library('form_validation');
    }
    
    // load codeigniter view
    function index(){
        $this->load->view("ajax_demo_view");
    }
    
    // fn to which form 'll be submitted via ajax
    function submit()
    {
        //set validation rule
        $this->form_validation->set_rules('email', 'Email', 'trim|required|valid_email);
        
        if ($this->form_validation->run() == FALSE)
        {   
            //validation fails
            echo validation_errors();
        }
        else
        {
            // get post data
            $emailid = $this->input->post('email');
            
            // write your database insert code here
            ...
            
            echo "<div class='alert'>Thanks for Subscribing! Please stay tuned to get awesome tips...</div>";
        }
    }
?>

Next we have to create view for the controller and add a form & other necessary elements. Then you should include jquery library and add js snippet which will collect the form data (in our case it's just email id) and submit the form via jquery's ajax() method.

View: "ajax_demo_view.php"

<html>
    <head>
        <title>Codeigniter AJAX form Submission Demo</title>
    </head>
    <body>
        ...
        
        <script src="<?php echo base_url("/path/to/jquery-1.10.2.js"); ?>" type="text/javascript"></script>

        <script type="text/javascript">
        $("#submit").click(function(e) {
            e.preventDefault();
            var email_id = $("email").val();
            $.ajax({
                url: "<?php echo site_url('ajax_demo/submit'); ?>",
                method: "POST",
                data: {email: email_id},
                success: function(data) {
                    $("#message").html(data);
                },
                error: function() {
                    alert("Please enter valid email id!");
                }
            });
        });
        </script>

    </body>
</html>

In the above code, #submit specifies the id of the subscribe button and when it is clicked by user, the javascript will be executed and then form data will be submitted using ajax() without refreshing the page.

jQuery's ajax() function contains several options and we have used some of them in our example. Here are the descriptions for what those options stands for.

  • url - contains the URL to which the HTTP request is sent. (in our example, ajax_demo is the controller name and submit is the function to which we submit the form.)
  • method - specifies if the request is GET or POST.
  • data - contains the form data as json format. It can either be a string or object.
  • success - this function will be executed when the http request succeeds.
  • error - and this function will be invoked if the http request fails.

Submitting codeigniter form using ajax would be similar to submitting it with normal submit button. The only difference would be, that you have to load jquery library in codeigniter view and use it's ajax() or post() method to submit the form without refreshing the page. Like any other form, it will be submitted to the controller function and you have to specify it with URL option.

Related Read: CodeIgniter AJAX Form Example with Validation

So that explains how to submit form using ajax in codeigniter. I hope you find post this useful. Stay tuned for more interesting coding tutorials & tips.

CodeIgniter Limit Query Example

On 8/15/2016 Be the first to comment!

Codeingiter Limit Query: Limit clause is used in sql queries to limit the number of rows returned from the query. Fetching large tables with several hundreds of records will impact on performance - besides it's not an easy task for the users to go through such a huge list of records at a time. So your best bet would be to create pagination or multi page results where users will be shown limited set of records on one go.

Limit queries are quite useful for this sort of tasks and we'll see here how to write limit query in codeigniter.

CodeIgniter Limit Query Example:

In code igniter, you have to use active records $this->db->limit(); function to generate LIMIT clause. It should be combined with select query to restrict the number of records returned from the database.

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

The limit() method takes up two parameters - the first one defines the number of records to be fetched and the second is the offset value.

The above statement produces the string, 'LIMIT 5, 10' which returns 10 records starting right from the 5th record.

The second param Offset is optional, so you can simply ignore and pass to limit(), just the no. of records you want the query to return.

For example, this codeigniter limit query will return the first 50 records from the 'Students' table.

$this->db->select('*');
$this->db->from('Students');
$this->db->limit(50);
$query = $this->db->get();

// Produces SQL
// SELECT * FROM Students LIMIT 50;

Limit Query with Order By Clause:

Function limit() can be combined with other sql clauses like where(), like(), group_by(), order_by() etc. Below example shows a limit query which returns the name of the top 10 students who has scored highest marks.

$this->db->select('Name');
$this->db->from('Students');
$this->db->order_by('Total', 'desc');
$this->db->limit(10);
$query = $this->db->get();

// Produces SQL
// SELECT Name FROM Students ORDER BY Total DESC LIMIT 10;

Codeigniter Limit Query with Offset:

Here is another example using LIMIT with offset and LIKE clause.

$this->db->select('*');
$this->db->from('Students');
$this->db->like('Name', 'S');
$this->db->limit(20, 10);
$query = $this->db->get();

// Produces SQL
// SELECT * FROM Students WHERE Name LIKE "%S%" LIMIT 10, 20;

Alternative Way: LIMIT Without limit() Function

There is an alternative way to write limit query in codeigniter. You can limit the number of rows returned without using limit() function itself. Instead you have to pass the no. of rows & offset as second and third parameters to $this->db->get(); function. Obviously the first param should be the table name.

$this->db->get('Students', 30, 10);

// Produces SQL
// SELECT * FROM Students LIMIT 10, 30;

Also Read: CodeIgniter Pagination with Search Filter Example

That was all about codeigniter limit queries. I hope all the above query examples help you to understand limit query in codeigniter. Meet you in the next post :)

How to Use Datepicker in Codeigniter using jQuery | Example

On 8/12/2016 Be the first to comment!

This tutorial will show you how to use datepicker in codeigniter forms using jquery. A date picker is an interactive control which makes it easy for the user to choose the date from a calendar rather typing it manually. Honestly who wants to type into web forms let alone date these days? Besides it's a great way to avoid typos and enhance user experience too.

There are so many ways by which you can add datepicker in codeigniter, but jquery ui datepicker so far is simple and rich in features. This datepicker calendar will be tied to form input and opened in a small overlay beneath the text box. Below we'll see how to use datepicker in codeigniter with jquery ui plug-in.

How to Add Datepicker in CodeIgniter using jQuery UI?

To use jquery datepicker in codeigniter form, first you'll need to download jQuery UI plugin and move the files to your codeigniter application. Make sure to place them along with your other assets like css, js for easier access.

Next you have to include the plug-in's css and js files to codeigniter view. Since the plugin is built over jquery you'll also need to include the library for the datepicker to work.

So include jquery ui style sheet inside <head></head> of your code igniter view.

<head>
    ...
    <!--link jquery ui css-->
    <link type="text/css" rel="stylesheet" href="<?php echo base_url('assets/jquery-ui-1.12.0/jquery-ui.css'); ?>" />
</head>

Next load jquery and jquery ui's js files just above the closing body tag (</body>).

<!--load jquery-->
<script src="<?php echo base_url('assets/js/jquery-1.10.2.js'); ?>"></script>
<!--load jquery ui js file-->
<script src="<?php echo base_url('assets/jquery-ui-1.12.0/jquery-ui.js'); ?>"></script>

Now create a text input to codeigniter form to which you can add datepicker control.

<label for="dob">Date of Birth</label>
<?php $attributes = 'id="dob" placeholder="Date of Birth"';
echo form_input('dob', set_value('dob'), $attributes); ?>

Here is the final step to add datepicker in codeigniter. Add this script just above the </body> tag.

<script type="text/javascript">
$(function() {
    $("#dob").datepicker();
});
</script>

The function datepicker() is used to invoke the month calendar - and the above script makes the datepicker calendar to popup as soon as the dob field got focus.

add-jquery-datepicker-in-codeigniter-example

You can also customize the way the datepicker control behaves using its options. For example its default date format is 'mm/dd/yy'. If you want to change it to the common date format used in databases then set dateFormat option inside datepicker() like this,

$("#dob").datepicker(
    {
        dateFormat: 'yy-mm-dd'
    }
);

This setting will be helpful in case you wish to insert the date input to database. It will save you the additional computing time required to change the date format and store it into db.

Read Also: How to Populate Dropdown from Database in CodeIgniter

Likewise you can easily use datepicker in codeigniter forms using jQuery ui plugin. I hope you find this little codeigniter tutorial useful. Meet you in another interesting post.

CodeIgniter Populate Dropdown from Database Example

On 8/10/2016 Be the first to comment!

Hi! In this tutorial we'll see how to populate dropdown from database in codeigniter and validate the said drop down list with custom form validation. In codeigniter, you have to use the form helper's function form_dropdown() to add dropdown list (combo box) to html forms. The function takes up associative array as argument which will then be added as field options.

To populate the drop down from database in a form, you have to fetch the data from DB, convert it into an array of (key, value) pairs and pass it as an argument to the function. Let's see in detail how to do this in codeigniter.

Populate Dropdown from Database in CodeIgniter:

Consider this MySQL database with tables 'Category' & 'Books'.

codeigniter-dropdown-from-database-example

From the above db diagram it's evident that the table 'Books' contains a foreign key CategoryID which is dependent on the parent table 'Category'. So in any case if we create a form to insert book details to database, the 'Category' field should be kept as a dropdown list in the form which in turn should be populated from the parent table.

Hence we have to fetch all category id & names from tbl 'Category' and add it to the dropdown field making the user to choose any of the category name from the available list. Category id won't be visible to the user, but during db insertion the id will be inserted into database instead of name.

In codeigniter, to populate drop down list from database first create a function getCategory() in model file to fetch category id & name from the database and returns them as an associative array.

Model:

<?php
class demo_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }
    
    function getCategory()
    {
        $query = $this->db->get('category');
        $result = $query->result();

        $cat_id = array('-CHOOSE-');
        $cat_name = array('-CHOOSE-');
        
        for ($i = 0; $i < count($result); $i++)
        {
            array_push($cat_id, $result[$i]->CategoryID);
            array_push($cat_name, $result[$i]->CategoryName);
        }
        return array_combine($cat_id, $cat_name);
    }
}
?>

Next in a controller file make a call back to the model function created and pass category details as an array to the view file. This array will then be used in the view to populate the dropdown 'category'.

Plus if you want to validate the drop down input, then you must also need to add a custom validation function to the controller which ensures if user selects valid category name or not.

Controller:

<?php
class demo extends CI_Controller
{
    function __construct()
    {
        parent::__construct();
        $this->load->helper(array('form', 'url'));
        $this->load->library('form_validation');
        $this->load->database();
        $this->load->model('demo_model');
    }
    
    function index()
    {
        $data['category'] = $this->demo_model->getCategory();
        $this->form_validation->set_rules('bookname', 'Book Name', 'trim|required');
        $this->form_validation->set_rules('category', 'Category', 'callback_validate_dropdown');
        $this->form_validation->set_rules('author', 'Author', 'trim|required');
        $this->form_validation->set_rules('isbn', 'ISBN', 'trim|required');

        if ($this->form_validation->run() == FALSE)
        {
            // failed validation
            $this->load->view('demo_view', $data);
        }
        else
        {
            // here goes your code to insert into db
            echo "Hooray! Now write down your code to insert book details into database...";
        }
    }
    
    function validate_dropdown($str)
    {
        if ($str == '-CHOOSE-')
        {
            $this->form_validation->set_message('validate_dropdown', 'Please choose a valid %s');
            return FALSE;
        }
        else
        {
            return TRUE;
        }
    }
}
?>

Finally in a view file you have to pass the array containing the category details to the function form_dropdown() and populate the combo list with category id, name pairs.

And to display the validation error you have to add form_error() function below the input fields.

View:

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>CodeIgniter Dropdown from Database Example</title>
    <link href="<?php echo base_url("assets/bootstrap/css/bootstrap.css"); ?>" rel="stylesheet" type="text/css" />
    <style>
    .well {
        background: none;
    }
    </style>
</head>
<body>
<div class="container">
<div class="row">
    <div class="col-xs-6 col-xs-offset-3 well">
        <?php $attributes = array("name" => "booksform");
        echo form_open("demo/index", $attributes);?>
        
        <legend>Books Form</legend>

        <div class="form-group">
            <label for="bookname">Book Name</label>
            <?php $attributes = 'id="bookname" placeholder="Enter Book Name" class="form-control"';
            echo form_input('bookname', set_value('bookname'), $attributes); ?>
            <span class="text-danger"><?php echo form_error('bookname'); ?></span>
        </div>

        <div class="form-group">
            <label for="category">Category</label>
            <?php $attributes = 'class="form-control" id="category"';
            echo form_dropdown('category', $category, set_value('category'), $attributes); ?>
            <span class="text-danger"><?php echo form_error('category'); ?></span>
        </div>

        <div class="form-group">
            <label for="author">Author</label>
            <?php $attributes = 'id="author" placeholder="Author" class="form-control"';
            echo form_input('author', set_value('author'), $attributes); ?>
            <span class="text-danger"><?php echo form_error('author'); ?></span>
        </div>

        <div class="form-group">
            <label for="author">ISBN</label>
            <?php $attributes = 'id="isbn" placeholder="ISBN" class="form-control"';
            echo form_input('isbn', set_value('isbn'), $attributes); ?>
            <span class="text-danger"><?php echo form_error('isbn'); ?></span>
        </div>

        <div class="form-group">
            <button name="submit" type="submit" class="btn btn-info">Submit</button>
            <button name="cancel" type="reset" class="btn btn-info">Cancel</button>
        </div>
        <?php echo form_close(); ?>
    </div>
</div>
</div>
</body>
</html>

As already said, form_dropdown('category', $category, set_value('category'), $attributes); function creates a standard html dropdown field.

  • And for the function parameters we have used, the first one 'category' is the name of the dropdown field.
  • The second parameter $category is the associative array to be populated in the dropdown box.
  • The third one is the default value to be selected. Here we used set_value('category') to keep the previous value chosen by the user in case of failed validation.
  • The final parameter $attributes includes any of the additional attributes we want to add to the dropdown.

Once everything is set, run the controller file and you can see a form with dropdown list populated with category names like this,

codeigniter-populate-dropdown-from-database-example

If the user fails to select valid category and submits the form, it will show up error like this,

codeigniter-dropdown-from-database-validation-error

If validation runs successful, you can insert the form details into database which is beyond the scope of this tutorial. But never worry, here this tutorial will teach you how to insert form data into database in codeigniter.

Now that was all about populating dropdown list from database in PHP Codeigniter. I hope you have enjoyed this tutorial. See you in the next post :)

Codeigniter Distinct Query: Select Distinct Column

On 8/08/2016 Be the first to comment!

CodeIgniter Distinct Query: This tutorial will show you how to write distinct query in codeigniter. Database table columns may contain duplicate values but you wish to fetch only unique (different) ones. In such cases you have to use DISTINCT keyword along with SELECT Query to eliminate duplicates and return only unique data.

With the help of Active Records distinct() function you can form select query with distinct operator in code igniter.

How to Write Distinct Query in CodeIgniter?

In CodeIgniter you can write distinct query in two ways. First with active records library function $this->db->distinct(); and the second one using the 'DISTINCT' keyword directly inside the select() function.

Here is a sample mysql table which we'll use to write distinct queries in codeignitor.

write-distinct-query-in-codeigniter
Table - Books

1. Using distinct() Method:

The method adds the keyword 'DISTINCT' to Active Records Select query. If you want to fetch unique values of a specific column then pass it as a parameter to select() function. To fetch unique rows, skip select().

$this->db->select('Category');
$this->db->distinct();
$query = $this->db->get('Books');

// Generates SQL String
// SELECT DISTINCT `Category` FROM `Books`;

Result:

codeigniter-select-distinct-column-example

2. Using DISTINCT Keyword in Select() Method:

This is an alternative way to write distinct query. In this method you can directly pass 'DISTINCT' keyword to select() like this,

$this->db->select('DISTINCT(Category)');
$query = $this->db->get('Books');

// Generates SQL String
// SELECT DISTINCT(Category) FROM `Books`;

Result:

select-distinct-column-in-codeigniter

This one is another example of codeigniter distinct query which counts the number of unique values present in a specific column/field.

$this->db->distinct();
$this->db->select('Category');
$query = $this->db->get('Books');
echo $query->num_rows();

// Outputs, 3

Also keep in mind in case you wish to fetch a distinct column together with non-distinct columns, make sure to group by the distinct column name.

$this->db->select('DISTINCT(Category), BookName');
$this->db->group_by('Category'); 
$query = $this->db->get('Books');

// Generates SQL String
// SELECT DISTINCT(Category), `BookName` FROM `Books` GROUP BY `Category`;
codeigniter-distinct-query-example

Likewise you can easily write distinct query in codeigniter. I hope you find this codeigniter distinct query tutorial useful. See you in the next post:)

Contact Form

Name

Email *

Message *