Question of the Day with CodeIgniter and MySQL

In this tutorial we will build a small application with CodeIgniter that will be backed by a MySQL database. It will present questions and give visitors the ability to post answers. There isn't an admin interface for adding or changing questions, but if you follow and understand this tutorial, you will be able to create one yourself.

As this might be the first exposure to CodeIgniter for some of you, we will start with a brief intro. If you know what CI is, you can skip directly to the code.

Note: This tutorial is quite old and doesn't work in PHP7 and above. We are keeping it online only as a reference.

First, what is CodeIgniter?

CodeIgniter is a framework for developing web applications in PHP. It will help you properly organize your code using the model-view-controller (MVC) pattern, and give you useful functionality for getting your site up and running quickly. It hides away the tedious work like building and validating forms, communicating with a database, managing error pages and much more.

There are certainly a lot of frameworks to choose from, but CI has the winning combination of a big community, nice documentation and good performance. Unless you enjoy spending time building sites from scratch (which is not a bad thing), using a framework is the smarter way to go.

You should probably start by reading the CodeIgniter introductory guide, which will give you a better understanding of how CI works. Of course, if you are experienced with PHP you can grab the zip and jump directly to the code.

Let's begin!

The database

Naturally, we will need a table for the questions, and a table for the answers.

The questions table has only two fields - the ID of the question (automatically assigned with auto_increment) and the question body field. The answers also have an ID column, but they also have a qid field for the id of the question they relate to. The rest of the fields are the email and name of the author, along with the answer body and a timestamp (the dt field).

It is possible to create these tables with PHP code (CI gives us the ability to manipulate tables), but we will keep things simple and design them using a database management system like phpMyAdmin. It is even simpler for you, as I've included an export of the tables as an SQL file (tables.sql in the download archive). Import it into your database or copy/paste the code into the SQL tab of phpMyAdmin and you should be ready to go.

Setting up CodeIgniter

At this point you should download CodeIgniter. In this tutorial I am using version 2.1.0, the latest at the time of writing. Extract it in the directory where you are developing your website (move the files outside the CodeIgniter_2.1.0 folder and remove it). We will need to edit a few settings so the framework works the way we want it.

Important: This section assumes that you are downloading a fresh copy of CodeIgniter. If you only want to make the downloaded demo work, you will only need to change your $config['base_url'] setting in config.php, and add your MySQL login details to database.php. The other changes are already made (you still need to create the MySQL tables).

Edit routes.php

The application/config/routes.php file determines which controller is called by default. We need to set this to questions, which we will be creating in a moment.

// Find this line:
$route['default_controller'] = "welcome";
// Replace it with this:
$route['default_controller'] = "questions";

This will show the questions controller as the start page of the application.

Edit config.php

The application/config/config.php file holds much of the configuration settings of the framework.

// Find this line:
$config['base_url'] = '';
// Replace it with the following:
$config['base_url'] = 'http://example.com/';

The above setting, base_url, tells CI where your site is located. This information is used when forming links and including resources like stylesheets and images.

The following setting is optional. CodeIgniter adds index.php to every URL by default (like so: http://example.com/index.php/products/). If you want to hide it (so that the url becomes http://example.com/products/), you will need to edit the index_page setting:

// Find this line:
$config['index_page'] = 'index.php';
// Replace it with the following:
$config['index_page'] = '';

You will also need to create an .htaccess file, which rewrites the URLs so that they still get to index.php, although it is not included explicitly. The .htaccess I use in the demo follows:

.htaccess

Options +FollowSymLinks
Options +Indexes
RewriteEngine On

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

RewriteCond $1 !^(index\.php)
RewriteRule ^(.*)$ index.php/$1 [L]

Now you only need to add your MySQL connection settings so that your application can connect to your database.

Edit database.php

Find these four lines in application/config/database.php and change them to reflect your connection settings:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'user';
$db['default']['password'] = 'pass';
$db['default']['database'] = 'your_database_name';

Great! We are done setting up CodeIgniter, so we can now move on with the code.

Showing the questions

To show the questions we will need to create two files - a controller, which will be called when we visit the /question/ URL, and a view, which outputs the HTML code of the page. The controller will handle selecting records from the database and passing them to view. Note that we are not using any models in this app (the M in MVC). For simple applications like this one, they are purely optional and you can write CI apps without them.

/application/controllers/questions.php

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Questions extends CI_Controller {

    public function index(){

        // Loading only the libraries and helpers that we need.
        // This is one of the things that make CI fast.

        $this->load->database();
        $this->load->helper(array('date','url'));

        // Get the id of the last question

        $res = $this->db->
                select_max('id')->
                get('qod_questions')->
                result_array();

        $id = $res[0]['id'];

        $this->show($id);
    }

    public function show($id = -1){

        $this->load->database();
        $this->load->helper(array('date','url'));

        // Select the question

        $q = $this->db->
                where(array('id'=>$id))->
                get('qod_questions')->
                result_array();

        if(empty($q)){
            // Show an error page
            show_404();
        }

        $a = $this->db->
                where(array('qid'=>$id))->
                order_by('id','desc')->
                get('qod_answers')->
                result_array();

        // Get the ids of the previous
        // and next questions

        $prev = 0;
        $next = 0;

        $res = $this->db->
                select_min('id')->
                where("id > $id")->
                get('qod_questions')->
                result_array();

        if(!empty($res)){
            $next = $res[0]['id'];
        }

        $res = $this->db->
                select_max('id')->
                where("id < $id")->
                get('qod_questions')->
                result_array();

        if(!empty($res)){
            $prev = $res[0]['id'];
        }

        $this->load->view('question_of_the_day',array(
            'question'  => $q[0]['question'],
            'answers'   => $a,
            'previous'  => $prev,
            'next'      => $next,
            'id'        => $id
        ));
    }
}

The controller is basically a class that extends CI_Controller and resides in the controllers folder. The class methods are called by the framework depending on the URL. For example http://example.com/questions/ will execute the index method, and http://example.com/questions/show/213 - the show method. In the latter case, 213 will be passed as the id parameter.

In the show method we select the question with the passed ID and display it in the view. What the index method does, is to select the id of the newest question, and also call the show method. Because it is set as the default controller, this would mean that when you visit the start page of the application (the base url you set in the previous section), the latest question will be shown.

Read more about the URLs, controllers, views, helpers and the database class of CodeIgniter in the docs.

Now let's see how the view works. When we load it, we pass an array. Each of the array keys (question, answers, previous, next and id) will be extracted and transformed into a variable. Instead of a class, the view is a simple PHP file.

/application/views/question_of_the_day.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8" />
        <title><?php echo $question?> - Question of the day | Tutorialzine Demo</title>

        <!-- Our CSS stylesheet file -->
        <link rel="stylesheet" href="http://fonts.googleapis.com/css?family=Rancho" />
        <link rel="stylesheet" href="<?php echo base_url()?>assets/css/styles.css" />

        <!--[if lt IE 9]>
          <script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
        <![endif]-->
    </head>

    <body>

        <header id="blackboard">
            <h1><?php echo $question?></h1>
            <?php if($previous) echo anchor("questions/show/$previous",'&laquo;','class="arrow left"')?>
            <?php if($next) echo anchor("questions/show/$next",'&raquo;','class="arrow right"')?>
        </header>

        <?php 

        // This method will create a link to the answer form. It is given relative
        // to your index page, but will be transformed depending on your base in config.php

        echo anchor("answers/add/$id",'Add your answer','class="addAnswer"');

        ?>

        <ul id="answers">

        <?php foreach ($answers as $ans): ?>

            <li>
                <?php
                    // Forming the URL of the gravatar image.
                    $gravatar = 'http://www.gravatar.com/avatar/'.md5( strtolower( trim($ans['email']) ) ).'?d=mm';
                ?>

                <span class="avatar" style="background-image:url(<?php echo $gravatar?>)"></span>
                <span class="meta">
                    <a href="#" class="author"><?php echo $ans['name']?></a>
                    <?php
                        // We are using the built-in timestamp method. We are using
                        // array slice to limit the shown time divisions.
                        $ts = explode(',', timespan(strtotime($ans['dt'])));
                        $t = implode(',',array_slice($ts,0,2));
                    ?>
                    <i><?php echo $t?> ago</i>
                </span>
                <p class="answer"><?php echo $ans['answer']?></p>
            </li>

        <?php endforeach; ?>

        </ul>

    </body>
</html>

The script loops through the $answers array and outputs a li element for each answer. It assembles a gravatar URL by generating an md5 hash of the email and includes it as the background-image of a rounded span element.

This gives us a functional answer browsing page. But how do we let people answer these questions?

Posting answers

We will again need to write a controller and a view for adding answers. Here we will use a few more of the neat CodeIgniter features - generating forms and validating them. The generation is handled by the form helper, and the validation - by the form validation class.

You can see the answers controller below.

/application/controllers/answers.php

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Answers extends CI_Controller {

    public function add($id = -1){

        $this->load->database();
        $this->load->helper(array('url','form'));
        $this->load->library('form_validation');

        // Check if there is such a question

        $q = $this->db->
                where(array('id'=>$id))->
                get('qod_questions')->
                result_array();

        if(empty($q)){
            // Show an error page
            show_404();
        }

        // Adding validation rules.

        $this->form_validation->set_rules('name', 'Name', 'required|min_length[2]|max_length[32]');
        $this->form_validation->set_rules('email', 'Email', 'required|valid_email');
        $this->form_validation->set_rules('answer', 'Answer', 'required|min_length[5]|max_length[255]');

        // If there are errors, show the form
        if ($this->form_validation->run() == FALSE){

            $this->load->view('add_answer',array(
                'question'  => $q[0]['question'],
                'qid'       => $q[0]['id']
            ));

        }
        else{

            // Otherwise insert the answer to the database

            $this->db->insert('qod_answers', array(
                'qid'   => $q[0]['id'],
                'email' => htmlspecialchars($this->input->post('email')),
                'name'  => htmlspecialchars($this->input->post('name')),

                // preserving new lines:
                'answer'=> nl2br(htmlspecialchars($this->input->post('answer')))
            ));

            redirect('questions/show/'.$q[0]['id']);
        }

    }
}

With the help of the form validation class, we are able to add rules by which the data should be validated. We will insert the answer in the database only when presented with correct data. I am using the htmlspecialchars() PHP function to escape any HTML code that might have been typed in the form fields. When the answer is inserted, we redirect to the question page. If everything went as it should, the newly added answer should be at the top.

Equally interesting is how the answer form is generated. For this we will take a look at the view file.

/application/views/add_answer.php

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8" />
        <title><?php echo $question?> - Add Answer | Tutorialzine Demo</title>

        <!-- Our CSS stylesheet file -->
        <link rel="stylesheet" href="http://fonts.googleapis.com/css?family=Rancho" />
        <link rel="stylesheet" href="<?php echo base_url()?>assets/css/styles.css" />

        <!--[if lt IE 9]>
          <script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
        <![endif]-->
    </head>

    <body>

        <header id="blackboard">
            <h1><?php echo $question?></h1>
        </header>

        <?php 

        // This will print a link to the question page
        echo anchor("questions/show/$qid",'Back to the question','class="addAnswer"');

        // Creating the form using the form helper

        echo form_open('');

        echo form_label('Name','name');
        echo form_input(array(
          'name'        => 'name',
          'id'          => 'name',
          'value'       => set_value('name'),
          'maxlength'   => '64'
        ));

        echo form_label('Email (gravatar enabled)','email');
        echo form_input(array(
          'name'        => 'email',
          'id'          => 'email',
          'value'       => set_value('email'),
          'maxlength'   => '128'
        ));

        echo form_label('Answer','answer');
        echo form_textarea(array(
          'name'        => 'answer',
          'id'          => 'answer',
          'value'       => set_value('answer'),
        ));

        echo form_submit('subm', 'Add Answer!'); 

        // Showing the validation errors
        $validation = validation_errors(); 

        if($validation):
        ?>
            <div class="errorMessages">
                <?php echo $validation?>
            </div>

        <?php endif;?>

        <?php echo form_close()?>

    </body>
</html>

As we included the form helper in the controller, we have access to all of its functions here, in the view. These functions make it easier to create forms and form fields. Of course, you can directly write the HTML of the forms, but you will have to update the code if you change the URL of your site.

At the bottom of the file we are accessing the validate_errors() function which will return a string with error messages, generated by the validation class. If there are any errors, we show them in a .errorMessages div.

That is it! All that is left is to add a pretty stylesheet and some images.

The styling

To finish this application, we need to add a stylesheet and images. Where we put them is more a matter of personal preference. I have chosen to go with an assets folder in the base directory of the application (you can see it in the zip file).

In the views I have specified the URL of the stylesheet like this: <?php echo base_url()?>assets/css/styles.css which gives us the correct location. base_url() is a function defined in the URL helper of the framework, which we've included in the controller.

As the tutorial is already quite lengthy, I won't be discussing the styling here. You can see it for yourself by opening /assets/css/styles.css in your code editor.

We're done!

I am hoping that you learned a lot in this tutorial. If you decide to go with CodeIgniter for your next website, it will be a useful experience for you. Although there is a learning curve, once you understand the basics, you will appreciate just how much time you can save. And with Sparks, you will become even more productive by adding easy to use open source modules to your projects.