4

Using Illuminate Database With Eloquent in Your PHP App Without Laravel

 1 year ago
source link: https://code.tutsplus.com/tutorials/using-illuminate-database-with-eloquent-in-your-php-app-without-laravel--cms-27247
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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 the following 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

Firstly, let's create our project directory and structure. In the rest of the tutorial, I assume that eloquent is our project root directory name.

In the main project directory, we’ll create anapp 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.

Project Organization

Project Organization

Project Organization

Install the Eloquent Library

Next, let's install the dependencies for our project. In the main project folder, let's create the eloquent/composer.json file. After creating it, paste the following code in our eloquent/composer.json file.

{
2
 "name": "illuminate-example/eloquent",
3
 "description": "Implementation of Database Queries with illuminate and Eloquent",
4
 "type": "project",
5
 "require": {}
6
}

To install the Illuminate database library, let's add  “illuminate/database”: “^7.30”, to our eloquent/composer.json file.

Next, let's add PSR-4 autoloading for our models and controllers:

"autoload": {
2
    "psr-4": {
3
        "Controllers\\": "app/controllers/",
4
        "Models\\": "app/models/" 
5
    }
6
}

Now, our eloquent/composer.json file should look like this:

{
2
    "name": "illuminate-example/eloquent",
3
    "description": "Implementation of Database Queries with illuminate and Eloquent",
4
    "type": "project",
5
    "require": {
6
        "illuminate/database": "^7.30"
7
    },
8
     "autoload": {
9
        "psr-4": {
             "Controllers\\": "app/controllers/",
             "Models\\": "app/models/"
        }
    }
}

Finally, let's run the following two commands in the root of our project directory.

$composer install
2
$composer dump-autoload -o

Set Up Database Tables and Configuration File

Let’s add a config file for our database credentials. In the main project directory, let's create a file named eloquent/config.php and define DB details in the eloquent/config.php file as shown in the following snippet. Note that the values should be replaced with your own connection details.

<?php
2
defined('DBDRIVER') or define('DBDRIVER','mysql');
3
defined('DBHOST') or define('DBHOST','localhost');
4
defined('DBNAME') or define('DBNAME','eloquent-app');
5
defined('DBUSER') or define('DBUSER','root');
6
defined('DBPASS') or define('DBPASS','pass');
7
?>

Next, let's create a 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 or 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.

Let's create tables with the following commands in MySQL.

Questions

Let's use the following SQL statement to create the questions table in our database.

CREATE TABLE `questions` (
2
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
3
 `question` tinytext,
4
 `user_id` int(11) DEFAULT NULL,
5
 `created_at` timestamp NULL DEFAULT NULL,
6
 `updated_at` timestamp NULL DEFAULT NULL,
7
 `deleted_at` timestamp NULL DEFAULT NULL,
8
 PRIMARY KEY (`id`)
9
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Advertisement

Answers

Let's use the following SQL statement to create the answers table in our database.

CREATE TABLE `answers` (
2
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
3
 `answer` tinytext,
4
 `user_id` int(11) DEFAULT NULL,
5
 `question_id` int(11) DEFAULT NULL,
6
 `created_at` timestamp NULL DEFAULT NULL,
7
 `updated_at` timestamp NULL DEFAULT NULL,
8
 `deleted_at` timestamp NULL DEFAULT NULL,
9
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes

Let's use the following SQL statement to create the upvotes table in our database.

CREATE TABLE `upvotes` (
2
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
3
 `answer_id` int(11) DEFAULT NULL,
4
 `user_id` int(11) DEFAULT NULL,
5
 `created_at` timestamp NULL DEFAULT NULL,
6
 `updated_at` timestamp NULL DEFAULT NULL,
7
 `deleted_at` timestamp NULL DEFAULT NULL,
8
 PRIMARY KEY (`id`)
9
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Users

Let's use the following SQL statement to create the users table in our database.

CREATE TABLE `users` (
2
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
3
 `username` varchar(100) DEFAULT NULL,
4
 `email` varchar(200) DEFAULT NULL,
5
 `password` varchar(200) DEFAULT NULL,
6
 `created_at` timestamp NULL DEFAULT NULL,
7
 `updated_at` timestamp NULL DEFAULT NULL,
8
 `deleted_at` timestamp NULL DEFAULT NULL,
9
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So we've done with database set up now. Next, we'll create model files that are required in our application.

Advertisement

Setting Up Eloquent Models

Firstly, we need to create a common Database class for setting up the database connection.

The Database Class

Create the eloquent/app/models/database.php file with your favorite text editor and add the following contents.

<?php
2
namespace Models; 
3
4
use Illuminate\Database\Capsule\Manager as Capsule;
5
6
class Database {
7
    public function __construct() 
8
    {
9
        $capsule = new Capsule;
        $capsule->addConnection([
             'driver' => DBDRIVER,
             'host' => DBHOST,
             'database' => DBNAME,
             'username' => DBUSER,
             'password' => DBPASS,
             'charset' => 'utf8',
             'collation' => 'utf8_unicode_ci',
             'prefix' => '',
        ]);
20
21
        // Setup the Eloquent ORM… 

22
        $capsule->bootEloquent();
23
    }
24
}

In the above file, we've initialized the Capsule class. Next, we've used the addConnection method to create a new MySQL connection. Finally, we've used the bootEloquent method to initialize Eloquent model.

The User Model

Let's create the eloquent/app/models/User.php file with the following contents.

<?php
2
namespace Models;
3
4
use \Illuminate\Database\Eloquent\Model;
5
6
class User extends Model {
7
    protected $table = 'users';
8
    protected $fillable = ['username', 'email', 'password'];
9
}
?>

The Question Model

Let's create the eloquent/app/models/Question.php file with the following contents.

<?php
2
namespace Models;
3
4
use \Illuminate\Database\Eloquent\Model;
5
6
class Question extends Model {
7
    protected $table = 'questions';
8
    protected $fillable = ['question','user_id'];
9
}
?>

The Answer Model

Let's create the eloquent/app/models/Answer.php file with the following contents.

<?php
2
namespace Models;
3
use \Illuminate\Database\Eloquent\Model;
4
5
class Answer extends Model {
6
    protected $table = 'answers';
7
    protected $fillable = ['answer','user_id','question_id'];
8
}
9
?>

The Upvote Model

Let's create the eloquent/app/models/Upvote.php file with the following contents.

<?php 
2
namespace Models;
3
4
use \Illuminate\Database\Eloquent\Model;
5
6
class Upvote extends Model {
7
    protected $table = 'upvotes';
8
    protected $fillable = ['answer_id', 'user_id'];
9
}
?>

And that's it for setting up model classes.

Create Front Controller Files

In this section, we'll create core bootstrap and front controller files.

The bootstrap.php File

It's common file which is used to bootstrap our application. Let's create the bootstrap.php file in the root directory of our application.

<?php
2
require './config.php';
3
require './vendor/autoload.php';
4
5
use Models\Database;
6
7
// initialize Illuminate database connection

8
new Database();
9
?>

We have also set up a database connection by instantiating the Database class.

The index.php File

It's a front controller of our application, so basically it's an entry point of our application.

Let's create the index.php file in the root of our application.

<?php
2
require 'bootstrap.php';
3
4
// our example code goes here...

5
?>

In fact, we'll use the index.php file to test all our use-cases in the rest of this article.

Now, we've set up models as well. From the next section on wards, we'll start testing our use-cases.

Task 1: Add a User

Firstly, let's create the eloquent/app/controllers/Users.php controller file with the following contents.

<?php
2
namespace Controllers;
3
use Models\User;
4
5
class Users {
6
    public static function create_user($username, $email, $password)
7
    {
8
        $user = User::create(['username'=>$username,'email'=>$email,'password'=>$password]);
9
        return $user;
    }
}
?>

In our Users controller class, we've created the create_user method which is used to create a new user. We've used the create method of the Eloquent model to create a new user.

Let's call it from our index.php file to test it as shown in the following snippet. Please make sure you encrypt the password strongly before you actually store it. It's never recommended to store plain text passwords in db.

<?php
2
require 'bootstrap.php';
3
4
use Controllers\Users; 
5
6
$user = Users::create_user("user1", "[email protected]", "user1_pass");
7
?>

Once you run the index.php file, it should create a new user in the users table.

Task 2: Add a Question

Firstly, let's create the eloquent/app/controllers/Questions.php controller file with the following contents.

<?php
2
namespace Controllers;
3
use Models\Question;
4
5
class Questions{
6
    public static function create_question($question,$user_id)
7
    {
8
        $question = Question::create(['question'=>$question,'user_id'=>$user_id]);
9
        return $question;
    }
}
?>

In our Questions controller class, we've created the create_question method which is used to create a new question. We've used the create method of the Eloquent model to create a new question.

Let's call it from our index.php file to test it as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
4
use Controllers\Questions; 
5
6
$question = Questions::create_question("Have you ever met your doppelganger?", 1);
7
?>

Once you run the index.php file, it should create a new question in the questions table.

Task 3: Add an Answer to a Question 

In this section, we'll see how to add answers to our questions.

Firstly, let's create the eloquent/app/controllers/Answers.php controller file with the following contents.

<?php
2
namespace Controllers;
3
use Models\Answer;
4
5
class Answers {
6
     public static function add_answer($answer,$question_id,$user_id)
7
     {
8
        $answer = Answer::create(['answer'=>$answer,'question_id'=>$question_id,'user_id'=>$user_id]);
9
        return $answer;
    }
}
?>

In our Answers controller class, we've created the add_answer method which is used to create a new answer.

Let's call it from our index.php file to test it as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
4
use Controllers\Answers; 
5
6
$answers = Answers::add_answer("This is an answer", 1, 2);
7
?>

Once you run the index.php file, it should create a new answer in the answers table.

Task 4: Upvote an Answer

This is pretty much the same steps we did earlier.

Let's add the following method in the eloquent/app/controllers/Answers.php controller.

...
2
...
3
public static function upvote_answer($answer_id,$user_id)
4
{
5
    $upvote = Upvote::create(['answer_id'=>$answer_id,'user_id'=>$user_id]);
6
    return $upvote;
7
}
8
...
9
...

You also need to import the Upvote model in the eloquent/app/controllers/Answers.php controller file by adding the following code.

use Models\Upvote;

Finally, we call it from our index.php file to test it as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
4
use Controllers\Answers; 
5
6
$upvote = Answers::upvote_answer(1, 14);
7
?>

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.

Firstly, let's define this relationship by adding the following function to our Question model.

...
2
...
3
public function answers()
4
{
5
    return $this->hasMany('\Models\Answer');
6
}
7
...
8
...

After that, in the eloquent/app/controllers/Questions.php controller file, let's add the following function to get questions with answers.

...
2
...
3
public static function get_questions_with_answers()
4
{
5
    $questions = Question::with('answers')->get()->toArray();
6
    return $questions;
7
}
8
...
9
...

It retrieves the questions with their corresponding answers.

Let's test it with the index.php file as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
use Controllers\Questions; 
4
5
$all = Questions::get_questions_with_answers();
6
?>

You can use the var_dump or print_r function to print the $all variable to see the results.

Task 6: Get All Questions and Users Who Asked Them

It's going to be a one to one relationship because one question has one user, so let's add the following method to the Question model.

...
2
...
3
public function user()
4
{
5
    return $this->belongsTo('\Models\User');
6
}
7
...
8
...

After that, in the eloquent/app/controllers/Questions.php controller file, let's add the following function.

...
2
...
3
public static function get_questions_with_users()
4
{ 
5
    $questions = Question::with('user')->get()->toArray();
6
    return $questions; 
7
}
8
...
9
...

Let's test it with the index.php file as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
use Controllers\Questions; 
4
5
$all_with_users = Questions::get_questions_with_users();
6
?>

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.

Let's add the following function to our Answer model:

...
2
...
3
public function upvotes()
4
{
5
    return $this->hasMany('\Models\Upvote');
6
}
7
...
8
...

In the eloquent/app/controllers/Questions.php controller file, let's create the following function.

...
2
...
3
public static function get_question_answers_upvotes($question_id)
4
{ 
5
    $questions = Question::find($question_id)->answers()->with('upvotes')->get()->toArray();
6
    return $questions;
7
}
8
...
9
...

Let's test it with the index.php file as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
4
use Controllers\Questions; 
5
6
$one_question = Questions::get_question_answers_upvotes(1);
7
?>

We can print the $one_question variable to see the results.

Task 8: Count All Questions by a Particular User

Firstly, let's import the Question model in the eloquent/app/controllers/Users.php controller:

use Models\Question;

After importing it, let's add the following function in the same file.

...
2
...
3
public static function question_count($user_id)
4
{
5
    $count = Question::where('user_id', $user_id)->count();
6
    return $count;
7
}
8
...
9
...

Finally, let's test it with the index.php file as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
use Controllers\Users; 
4
5
$user_question_count = Users::question_count(1);
6
?>

It returns the count of the number of questions that have been added by a user with id 1

Task 9: Update Answer by a User

The concept of updating with the Eloquent ORM is pretty simple. First we find a record, and then we mutate and save.

In the eloquent/app/controllers/Answers.php controller, let's add this function:

...
2
...
3
public static function update_answer($answer_id,$new_answer)
4
{
5
    $answer = Answer::find($answer_id);
6
    $answer->answer = $new_answer;
7
    $updated = $answer->save();
8
    return $updated;
9
}
...
...

Finally, let's test it with the index.php file as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
use Controllers\Answers; 
4
5
$update_answer = Answers::update_answer(1, "This is an updated answer");
6
?>

This returns a boolean value—true—if the update is successful.

Task 10: Delete a Question (Soft Delete)

Finally, we'll implement the Eloquent SoftDelete feature.

Firstly, let's import the Illuminate\Database\Eloquent\SoftDeletes trait in the Question model with the following statement.

use Illuminate\Database\Eloquent\SoftDeletes;

After it's imported, we can use it like this.

use SoftDeletes;

Finally, let's add deleted_at to the protected $dates property of the model. These are the required steps. 

protected $dates = ['deleted_at'];

Our Question model now looks like this:

<?php
2
namespace Models;
3
4
use \Illuminate\Database\Eloquent\Model;
5
use Illuminate\Database\Eloquent\SoftDeletes;
6
7
class Question extends Model {
8
    use SoftDeletes;
9
    protected $table = 'questions';
    protected $fillable = ['question','user_id'];
    protected $dates = ['deleted_at'];
    public function answers()
    {
        return $this->hasMany('\Models\Answer');
    }
    public function user()
20
    {
21
        return $this->belongsTo('\Models\User');
22
    }
23
}
24
?>

Next, go ahead and create the delete_question method in the eloquent/app/controllers/Questions.php controller.

...
2
...
3
public static function delete_question($question_id)
4
{
5
    $question = Question::find($question_id);
6
    $deleted = $question->delete();
7
    return $deleted; 
8
}
9
...
...

Finally, let's test it with the index.php file as shown in the following snippet.

<?php
2
require 'bootstrap.php';
3
use Controllers\Questions; 
4
5
$delete = Questions::delete_question(1);
6
?>

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.

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 micro-framework 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.

References

This post has been updated with contributions from Sajal Soni. Sajal belongs to India and he loves to spend time creating websites based on open source frameworks.


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK