Illuminate is Laravel’s database engine minus Laravel. It comes bundled with the Eloquent ORM in Laravel. If you would like to build your PHP apps with ORMs and prefer not to use Laravel, this tutorial is for you.
In this tutorial, we are going to build the back end for a Q&A App with PHP, Illuminate Database, and the Eloquent ORM.
Project Dependencies
- PHP: 5.5+
- MYSQL
- Composer
App Capabilities
Our app will perform ten tasks:
- Add a user.
- Add a question.
- Add an answer to a question.
- Upvote an answer.
- Get a question with answers.
- Get all questions and users who asked them.
- Get particular questions, answers, and upvotes.
- Count questions by a particular user.
- Update answer by user.
- Delete a question.
First, we create our project directory and structure.
In the main project directory, we’ll create an app folder, and then in this app folder, we’ll create two folders: models and controllers. In this picture, our main project folder is named eloquent. You should replace it with whatever name you prefer.
Next, we create an index.php file in the main project folder, at the same level as the app folder.
We will use git, so we create a .gitignore file. Note that this step is optional.
Next, we install the dependencies needed for this project to work. In the main project folder, we'll create a composer.json file. Then paste this in our composer.json file.
{
“name”: “illuminate-example/eloquent”,
“description”: “Implementation of Database Queries with illuminate and Eloquent”,
“type”: “project”,
“require”: {}
}
To install the Illuminate database, we add this to our composer.json:“illuminate/database”: “5.1.8”,.
Next, we add psr-4 autoloading for our Models and controllers:
“autoload”: {
“psr-4”: {
“Controllers\\”: “app/controllers/”,
“Models\\”: “app/models/” }
}
Now, our composer.json file should look like this:
{
“name”: “illuminate-example/eloquent”,
“description”: “Implementation of Database Queries with illuminate and Eloquent”,
“type”: “project”,
“require”: {
“illuminate/database”: “5.1.8”},
“autoload”:
{“psr-4”:
{ “Controllers\\”: “app/controllers/”,
“Models\\”: “app/models/"
}
}
}
We will now run these two composer commands in the same location as our composer.json file:
composer install composer dump-autoload -o
This will generate a vendor folder which we can add to gitignore (this is also an optional step).
Let’s add a config file for our database credentials.
In the main project directory, we create a file named config.php and define DB details in the Config.php file. Note that the values should be replaced with your own connection details.
<?php defined(“DBDRIVER”)or define(‘DBDRIVER’,’mysql’); defined(“DBHOST”)or define(‘DBHOST’,’localhost’); defined(“DBNAME”)or define(‘DBNAME’,’eloquent-app’); defined(“DBUSER”)or define(‘DBUSER’,’root’); defined(“DBPASS”)or define(‘DBPASS’,’pass’);
Next, we create the schema for our app.
One thing to note before we create the schema for the tables in our database is that we can add timestamps to our schema.
The Eloquent ORM expects two timestamp columns if we want to enable timestamp operation on a particular table/model. They are the created_at and updated_at columns. If we enable timestamps for a model, Eloquent automatically updates these fields with the time when we create or update a record.
There is a third column called deleted_at. The deleted_at timestamp works differently, though. Eloquent has a soft delete capability which uses the deleted_at column to determine whether a record has been deleted. If you delete a record with the eloquent ‘delete’ function and you enable Soft Delete, the column is updated with the time of deletion. These deleted items can then be retrieved at any time.
In this app, we will be taking advantage of the timestamps, so we’ll use all three in our Schema creation.
Create tables with the following commands in MySQL:
Questions
CREATE TABLE `questions` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `question` tinytext, `user_id` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Answers
CREATE TABLE `answers` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `answer` tinytext, `user_id` int(11) DEFAULT NULL, `question_id` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Upvotes
CREATE TABLE `upvotes` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `answer_id` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Users
CREATE TABLE `users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(100) DEFAULT NULL, `email` varchar(200) DEFAULT NULL, `password` varchar(200) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
We’ll proceed by creating files for models and controllers for our tables in the following locations:
- project_folder/app/models/question.php
- project_folder/app/models/answer.php
- project_folder/app/models/upvote.php
- project_folder/app/models/user.php
- project_folder/app/models/database.php
- project_folder/app/controllers/questions.php
- project_folder/app/controllers/answers.php
- project_folder/app/controllers/upvotes.php
- project_folder/app/controllers/users.php
Open models/database.php with an editor.
First we create the Capsule:
<?php
namespace Models;
use Illuminate\Database\Capsule\Manager as Capsule;
class Database {
function __construct() {
$capsule = new Capsule;
$capsule->addConnection([
‘driver’ => DBDRIVER,
‘host’ => DBHOST,
‘database’ => DBNAME,
‘username’ => DBUSER,
‘password’ => DBPASS,
‘charset’ => ‘utf8’,
‘collation’ => ‘utf8_unicode_ci’,
‘prefix’ => ‘’,
]);
// Setup the Eloquent ORM…
$capsule->bootEloquent();
}
}
In the file above, we initialize and set up the capsule with the constants defined in config.php, and then we boot eloquent.
The next step is to create a start script. This will be a file where everything that has to be run before our app works is run.
We create a start file in the location project_folder/start.php, and then in the file, require the Composer autoload file:
require ‘vendor/autoload.php’;
After that, we require config.php to get the credentials defined: require ‘config.php’;
Then we initialize the database class.
<?php use Models\Database; //Boot Database Connection new Database();
Your start.php should look like this:
<?php require ‘config.php’; require ‘vendor/autoload.php’; use Models\Database; //Initialize Illuminate Database Connection new Database(); ?>
Include start.php in your index.php as this will be our main file.
Our index.php file now looks like this:
<?php require ‘start.php’; ?>
Next, we can start working on our controllers and models. In project_folder/app/models/question.php, we add this:
<?php
namespace Models;
use \Illuminate\Database\Eloquent\Model;
class Question extends Model {
protected $table = ‘questions’;
}
?>
Then in project_folder/app/controllers/questions.php:
<?php
namespace Controllers;
class Questions{
}
?>
In project_folder/app/controllers/answers.php, we do the same:
<?php
namespace Controllers;
class Answers{
}
?>
Task 1: Add a User
In the user model (project_folder/app/models/user.php), we add the following code to define our namespace, extend the Eloquent Model, and define the table name (protected $table) and what fields in the tables can be filled by mass creation (protected $fillable).
<?php
namespace Models;
use \Illuminate\Database\Eloquent\Model;
class User extends Model {
protected $table = ‘users’;
protected $fillable = [‘username’,’email’,’pass’];
}
?>
In the users controller (project_folder/app/controllers/user.php), we define our namespace and class as usual:
<?php
namespace Controllers;
class Users{
}
?>
Then to create a user, in the users controller, we import the user Model namespace, use Models\User;, and then add a function to create the user.
<?php
public static function create_user($username, $email, $password){
$user = User::create(['username'=>$username,'email'=>$email,'password'=>$password]);
return $user;
}
Our user controller now looks like this.
<?php
namespace Controllers;
use Models\User;
class Users {
public static function create_user($username, $email, $password){
$user = User::create(['username'=>$username,'email'=>$email,'password'=>$password]);
return $user;
}
}
?>
Then in index.php we add these lines and run the app to create a new user.
<?php use Controllers\Users; // Import user controller $user = Users::create_user(“user1”,”[email protected]”,”user1_pass”);
Task 2: Add a Question
To add a question we import the Question model namespace in the questions controller, and write a create_question function:
use Models\Question;
Then:
<?php
public static function create_question($question,$user_id){
$question = Question::create(['question'=>$question,'user_id'=>$user_id]);
return $question;
}
We have used Eloquent mass creation models to insert this record, but before it works, we need to permit those fields to be fillable, because Eloquent models guard against mass creation by default.
So we go to the question model and add the protected $fillable property to the class.
protected $fillable = ['question','user_id'];
To run this, import the questions controller in index.php and call the create_question function statically:
use Controllers\Question;
Then create a question with a question and User Id as parameters:
$question = Questions::create_question("Have you ever met your doppelganger?",1);
This returns a model object if successful.
We will now run the index.php script with different entries to add more questions to the database.
Task 3: Add an Answer to a Question
In the answer model, we repeat the steps taken for question and user models by adding the code below:
<?php
namespace Models;
use \Illuminate\Database\Eloquent\Model;
class Answer extends Model {
protected $table = ‘answers’;
protected $fillable = [‘answer’,’user_id’,’question_id’];
}
?>
Then in the answers controller, we write these lines:
<?php
namespace Controllers;
use Models\Answer;
class Answers {
public static function add_answer($answer,$question_id,$user_id){
$answer = Answer::create(['answer'=>$answer,'question_id'=>$question_id,'user_id'=>$user_id]);return $answer;
}
}
?>
Then in index.php, we can create an answer for the question with id 1 we added earlier, with user id 2. Don't forget to import the answers controller to index.php first.
<?php
use Controllers\Answers;
$answers = Answers::add_answer(“This is an answer”,1,2);
To prevent multiple entries, comment all other calls in index.php before running a new one.
Task 4: Upvote an Answer
This is pretty much the same steps we are used to.
So we'll copy this into the Upvote model at project_folder/app/models/upvote.php.
<?php
namespace Models;
use \Illuminate\Database\Eloquent\Model;
class Upvote extends Model {
protected $table = 'upvotes';
protected $fillable = ['answer_id','user_id'];
}
?>
Then in the answers controllers, we import the Upvote Model namespace.
use Models\Upvote;
Then we create an upvote_answer function.
<?php
public static function upvote_answer($answer_id,$user_id){
$upvote = Upvote::create(['answer_id'=>$answer_id,'user_id'=>$user_id]);
return $upvote;
}
In index.php, we can call the function with a dummy User ID to upvote the answer with id 1.
$upvote = Answers::upvote_answer(1,14);
Task 5: Get a Question With Answers
For tasks like this, we can use Eloquent relationships.
Types of relationships include one to one, one to many, many to many, etc.
When using these relations, Eloquent assumes a foreign key in the form modelname_id exists on the models. For this task, the relationship is a one-to-many relationship because a single question can own any amount of answers.
First we define this relationship by adding this function to our question model.
<?php
public function answers()
{
return $this->hasMany('\Models\Answer');
}
Then in the questions controller, we write a function to get questions with answers.
<?php
public static function get_questions_with_answers(){
$questions = Question::with('answers')->get()->toArray();
return $questions;
}
This retrieves the questions with their corresponding answers.
In index.php, we comment all other calls and run:
$all = Questions::get_questions_with_answers();
We can var_dump or print_r the $all variable to see the results.
Task 6: Get All Questions and Users Who Asked Them
This is a one to one relationship because one question has one user, so we add this to the question model.
<?php
public function user()
{
return $this->belongsTo(‘\Models\User’);
}
Then we create a function in the questions controller and use the with function on the question model.
<?php
public static function get_questions_with_users(){
$questions = Question::with('user')->get()->toArray();
return $questions;
}
In index.php, comment all others and run this:
$all_with_users = Questions::get_questions_with_users();
Task 7: Get One Question With Answers and Upvotes
First, we define a relationship between answers and upvotes. An answer has many upvotes, so the relationship is one to many.
So we add the following function to our answer model:
<?php
public function upvotes()
{
return $this->hasMany('\Models\Upvote');
}
Then in the questions controller, we create the function to get this:
<?php
public static function get_question_answers_upvotes($question_id){
$questions = Question::find($question_id)->answers()->with('upvotes')->get()->toArray();
return $questions;
}
As in previous steps, we comment all other calls to index.php and run this:
$one_question = Questions::get_question_answers_upvotes(1);
We can print the $one_question variable to see the results.
Task 8: Count All Questions by a Particular User
First we import the question model in the users controllers:
use Models\Question;
Then we write this function:
<?php
public static function question_count($user_id){
$count = Question::where('user_id',$user_id)->count();
return $count;
}
In index.php, we comment other calls and add this line:
$user_question_count = Users::question_count(1);
This returns an integer which is the number of questions that have been added by a user with id 1.
We can print the $user_question_count variable and run index.php to see the results.
Task 9: Update Answer by User
The concept of updating with the Eloquent ORM is pretty simple. First we find a record, and then we mutate and save.
Now, in the answers controllers, we add this function:
<?php
public static function update_answer($answer_id,$new_answer){
$answer = Answer::find($answer_id);
$answer->answer = $new_answer;
$updated = $answer->save();
return $updated;
}
In index.php, we can comment all other calls, and update answer with id 1 like this:
$update_answer = Answers::update_answer(1,”This is an updated answer”);
This returns a boolean value—true—if the update is successful.
Task 10: Delete a Question (Soft Delete)
In this final task, we'll implement Eloquent SoftDelete.
First we tell the question model to use SoftDeletes by importing the SoftDeletes namespace, and then using the SoftDeletes trait in our class.
use Illuminate\Database\Eloquent\SoftDeletes;
Then after the class declaration line, we add this line:
use SoftDeletes;
Then we add deleted_at to the protected $dates property for the model. These are the required steps.
protected $dates = [‘deleted_at’];
Our question model now looks like this:
<?php
namespace Models;
use \Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
class Question extends Model {
use SoftDeletes;
protected $table = 'questions';
protected $fillable = ['question','user_id'];
protected $dates = ['deleted_at'];
public function answers()
{
return $this->hasMany('\Models\Answer');
}
public function user()
{
return $this->belongsTo('\Models\User');
}
}
?>
Then we create the delete_question function in the questions controller.
<?php
public static function delete_question($question_id){
$question = Question::find($question_id);
$deleted = $question->delete();
return $deleted;
}
Run in index.php:
$delete = Questions::delete_question(1);
Congratulations! You just built a fully functional back end with Illuminate and Eloquent. And we didn't have to write so much code to achieve all this.
The code for this tutorial can be found on GitHub.
Conclusion
Illuminate also comes with the Query Builder which you can use for even more complex database queries and is definitely something you want to experiment with and use in your app.
The only thing missing in the standalone Illuminate Database is database migrations, which are a lovely feature of Laravel, and Lumen, the microframework by Laravel. You should consider using both in your apps to take advantages of the useful features they come with.
You can find out more about Eloquent on the Official Eloquent Documentation Page.
Comments