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.

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 Database Schema


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.

Question of the Day with CodeIgniter


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

- Question of the day | Tutorialzine Demo [B][/B]