CRUD Operations in Laravel 4 with MYSQL

1. INTRODUCTION

Sometimes we need to create a CRUD operation for our projects, Create, Read, Update and Delete tables in database using Laravel. I admit it, Laravel is a complex framework, but once you understand how to manage it, it become easy and powerful framework for PHP development.
At the end of this tutorial, you should be able to create a basic application where you can add, edit, delete and list Books.

2. CREATE A LARAVEL PROJECT

Create a project called bookshop

3. CREATE TABLE

Before getting started, be sure to configure a database connection in app/config/database.php Just modify mysql connection.

 
 'mysql' => array(
 'driver'    => 'mysql',
 'host'      => 'localhost',
 'database'  => 'library',
 'username'  => 'root',
 'password'  => 'password',
 'charset'   => 'utf8',
 'collation' => 'utf8_unicode_ci',
 'prefix'    => '',
 ),

Using artisan, lets create a table and after that we will migrate to mysql database. For this, just open the console inside the project and type this line of command:
 php artisan migrate:make create_books_table --create=books
This will create a php file in app/database/migrations.
The file have this name:
"####_##_##_######_create_books_table.php" 
Open the file and add fields (id, isbn, title, author, publisher, language)

 
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateBooksTable extends Migration {
 /**
  * Run the migrations.
  *
  * @return void
  */
 public function up()
 {
        //create table in the database
        Schema::create('books',function(Blueprint $table)
        {
            $table->increments("id");
            $table->string('isbn');
            $table->string('title');
            $table->string('author');
            $table->string('publisher');
            $table->integer('language');
            $table->timestamps();
        });
 }
 /**
  * Reverse the migrations.
  *
  * @return void
  */
 public function down()
 {
        //drop table if exist in the database
        Schema::drop('books');
 }
}

Now we need to migrate all this information into our database in the command line type:
php artisan migrate
Once you have executed the above command, this will create a table called books in our MYSQL database (no need to create manually books table, this is one of the artisan's advantages).

4. Create eloquent books model

The Eloquent ORM included with Laravel provides a beautiful, simple ActiveRecord implementation for working with your database. Each database table has a corresponding "Model" which is used to interact with that table. Now that we have our database, let’s create a simple book Eloquent model so that we can access the books in our database easily.
Let's go to app/models folder and create a Books.php model and add the follow code(later we will add some more code to this model)

 
<?php
    class Book extends Eloquent
    {

    }

5. Create Book Controller

Let's create BookController, we can use artisan command for create it, in the command line type:
php artisan controller:make BookController

 
<?php
class BookController extends \BaseController {
 /**
  * Display a listing of the resource.
  *
  * @return Response
  */
 public function index()
 {
  //
 }
 /**
  * Show the form for creating a new resource.
  *
  * @return Response
  */
 public function create()
 {
  //
 }
 /**
  * Store a newly created resource in storage.
  *
  * @return Response
  */
 public function store()
 {
  //
 }
 /**
  * Display the specified resource.
  *
  * @param  int  $id
  * @return Response
  */
 public function show($id)
 {
  //
 }
 /**
  * Show the form for editing the specified resource.
  *
  * @param  int  $id
  * @return Response
  */
 public function edit($id)
 {
  //
 }
 /**
  * Update the specified resource in storage.
  *
  * @param  int  $id
  * @return Response
  */
 public function update($id)
 {
  //
 }
 /**
  * Remove the specified resource from storage.
  *
  * @param  int  $id
  * @return Response
  */
 public function destroy($id)
 {
  //
 }
}

As we can see it create our BookController controller with all the methods we need, we need to implement it part by part.

6. Create routes for our Book CRUD

What Laravel provides us is that we can simply tap into either a GET or POST request via routes and send it to the appropriate method. Here is an example for that:
Route::get('/register', 'BookController@showBookRegistration');
Route::post('/register', 'BookController@saveBook');
See the difference here is we are registering the same URL, /register, but we are defining its GET method so Laravel can call BookController class' showBookRegistration method. If it's the POST method, Laravel should call the saveBook method of the BookController class.
So let's create our route at /app/routes.php. Add the following line to the routes.php file: Route::resource('books','BookController');

 
<?php
Route::get('/', function()
{
 return View::make('hello');
});
Route::resource('books','BookController');

You can also use the following command to view the list of all the routes in your project from the root of your project, launching command line:
php artisan routes

As you can see, resource Controller really makes your work easy. You don't have to create lots of routes.

7. Create view to show all books

Before create a view for show books, we need to know about Blade. Blade is a templating engine provided by Laravel. Blade has a very simple syntax, and you can determine most of the Blade expressions within your view files as they begin with "@".
Blade syntax: {{ $var }}
PHP syntax:<?php echo $var; ?>
 As we can see blade syntax is compact and easy to write.
In this section we will create blade templates and how to use it; and need to know that all Blade templates should use the .blade.php extension.
Now let's try to fetch books from our database via the Eloquent object. For this let's modify the index method in our app/controllers/BooksController.php.

 
public function index()
{
 //get all Books
        $booksList=Book::all();
        return View::make('books.index',compact('booksList'));
}

Where books are the folder in app/views/ and index will be the main page to show all books
Now let's create our template, create a folder called layouts in app/views and inside the new folder create a new file called book.blade.php and add the following code.

 
<!doctype html>
<html>
<head>
    <title>George's BookShop::Peru</title>
    <meta charset="utf-8">
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css">
   <style>
        table form { margin-bottom: 0; }
        form ul { margin-left: 0; list-style: none; }
        .error { color: red; font-style: italic; }
        body { padding-top: 20px; }
    </style>
</head>
<body>
<div class="container">
    @if (Session::has('message'))
    <div class="flash alert">
        <p>{{ Session::get('message') }}</p>
    </div>
    @endif

    @yield('main')
</div>
</body>
</html>

Now let's create the view for showing books, for this create a folder called books inside app/views and inside the new folder create a file called index.blade.php, and add the following code:

 
@extends('layouts/book')
@section('main')
<h1>Welcome to my bookshop</h1>
<p>{{ link_to_route('books.create', 'Create new book') }}</p>
@if ($booksList->count())
<table class="table table-striped table-bordered">
    <thead>
    <tr>
        <th>Id</th>
        <th>ISBN</th>
        <th>Title</th>
        <th>Author</th>
        <th>Publisher</th>
        <th>Language</th>
    </tr>
    </thead>

    <tbody>
    @foreach ($booksList as $book)
    <tr>
      <td>{{ $book->id }}</td>
      <td>{{ $book->isbn }}</td>
      <td>{{ $book->title }}</td>
      <td>{{ $book->author }}</td>
      <td>{{ $book->publisher }}</td>
      <td>@if ($book->language==1){{'English'}} @else{{'Spanish'}}
                @endif</td>
      <td>{{ link_to_route('books.show', 'Read', array($book->id),
                array('class' => 'btn btn-primary')) }}</td>
      <td>{{ link_to_route('books.edit', 'Update', array($book->id),
                array('class' => 'btn btn-warning')) }}</td>
      <td>
        {{ Form::open(array('method'=> 'DELETE', 'route' =>
              array('books.destroy', $book->id))) }}
        {{ Form::submit('Delete', array('class' => 'btn btn-danger')) }}
        {{ Form::close() }}
      </td>
    </tr>
    @endforeach
    </tbody>
</table>
@else
There are no books
@endif
@stop

If we have followed the complete tutorial until this part so means our index page should work, but first, we need to add a new row in books table, this just for testing, later we will implement "add new book". After this let's start our server with the following command inside our bookshop project:
php artisan serve 
As we can see, our server start to work

Let's open a brower and go to our books view and bravo!!!! It works :P

8. Create new Book

Now as we have listed our books, let's write the code for creating new book. To create a new book, we will need to create the Controller method and bind the view for displaying the new book form, so let's modify our BookController in /app/controllers/BookController.php and add just one line of code in create method:

 
public function create()
{
 //add new book form
       return View::make('books.create');
}

We know that this is calling create view which still not yet implemented, so let's create the a new file called create.blade.php inside /app/views/books and lets add the following code:

 
@extends('layouts.book')
@section('main')
<h1>Create Book</h1>
{{ Form::open(array('route' => 'books.store')) }}
<ul>
    <li>
        {{ Form::label('ISBN', 'ISBN:') }}
        {{ Form::text('isbn') }}
    </li>
    <li>
        {{ Form::label('Title', 'Title:') }}
        {{ Form::text('title') }}
    </li>
    <li>
        {{ Form::label('Author', 'Author:') }}
        {{ Form::text('author') }}
    </li>
    <li>
        {{ Form::label('Publisher', 'Publisher:') }}
        {{ Form::text('publisher') }}
    </li>
    <li>
        {{ Form::label('Language', 'Language') }}
        {{ Form::select('language', array('0' => 'Select a Level', 
           '1' => 'English', '2' => 'Spanish'), Input::old('language'), 
           array('class' => 'form-control')) }}
    </li>
    <li>
        {{ Form::submit('Save', array('class' => 'btn btn-primary')) }}
    </li>
</ul>
{{ Form::close() }}
@if ($errors->any())
<ul>
    {{ implode('', $errors->all('<li class="error">:message</li>')) }}
</ul>
@endif
@stop

Let's modify our Book Model /app/Models/Book.php, because our model needs to have the $fillable variable set, this variable is used to prevent mass assignment. See the documentation on mass-assignment for details.

 
<?php
class Book extends Eloquent{

    protected $fillable = array('isbn', 'title','author','publisher','language');

}

Now let's create our store method to save our book form data into our books table, open /app/controllers/BookController.php and add the following code:

 
public function store()
{
    //create a rule validation
    $rules=array(
          'isbn'=>'required',
          'title'=>'required',
          'author'=>'required',
          'publisher'=>'required',
          'language'=>'required|numeric'
    );
    //get all book information
    $bookInfo = Input::all();
    //validate book information with the rules
      $validation=Validator::make($bookInfo,$rules);
      if($validation->passes())
      {
      //save new book information in the database 
      //and redirect to index page
          Book::create($bookInfo);
          return Redirect::route('books.index')
             ->withInput()
             ->withErrors($validation)
             ->with('message', 'Successfully created book.');
      }
      //show error message
      return Redirect::route('books.create')
           ->withInput()
           ->withErrors($validation)
           ->with('message', 'Some fields are incomplete.');
}

If we make click in Create New Book link we will redirect to create page depicted in the next figure:

9. Update Book information

Now as we learned how easy it is to add and list books. Let's jump into updating a book.In our list books view we have the Update link with the following code:
{{ link_to_route('books.edit', 'Update', array($book->idbook), array('class' => 'btn btn-warning')) }}
Here, the link_to_route function will generate a link /books/<id>/edit, which will call the resourceful Controller book, and Controller will bind it with the edit method, so let's modify edit and update methods in our BookController /app/controllers/BookController:

 
public function edit($id)
 {
        //get the current book by id
        $book = Book::find($id);
        if (is_null($book))
        {
            return Redirect::route('books.index');
        }
        //redirect to update form
        return View::make('books.edit', compact('book'));
}
public function update($id)
{
        //create a rule validation
        $rules=array(
            'isbn'=>'required',
            'title'=>'required',
            'author'=>'required',
            'publisher'=>'required',
            'language'=>'required|numeric'
        );
        $bookInfo = Input::all();
        $validation = Validator::make($bookInfo, $rules);
        if ($validation->passes())
        {
            $book = Book::find($id);
            $book->update($bookInfo);
            return Redirect::route('books.index')
                ->withInput()
                ->withErrors($validation)
                ->with('message', 'Successfully updated Book.');
        }
        return Redirect::route('books.edit', $id)
            ->withInput()
            ->withErrors($validation)
            ->with('message', 'There were validation errors.');
}

Now let's create our book edit view at /app/views/books/edit.blade.php, as follows:

 
@extends('layouts.book')
@section('main')
<h1>Edit Book</h1>
{{ Form::model($book, array('method' => 'PATCH', 
'route' =>array('books.update', $book->id))) }}
<ul>
    <li>
        {{ Form::label('ISBN', 'ISBN:') }}
        {{ Form::text('isbn') }}
    </li>
    <li>
        {{ Form::label('Title', 'Title:') }}
        {{ Form::text('title') }}
    </li>
    <li>
        {{ Form::label('Author', 'Author:') }}
        {{ Form::text('author') }}
    </li>
    <li>
        {{ Form::label('Publisher', 'Publisher:') }}
        {{ Form::text('publisher') }}
    </li>
    <li>
        {{ Form::label('Language', 'Language') }}
        {{ Form::select('language', array('0' => 
          'Select a Level', '1' => 'English', '2' => 'Spanish'), 
           null, array('class' => 'form-control')) }}
    </li>
    <li>
        {{ Form::submit('Update', array('class' => 
                 'btn btn-warning'))}}
        {{ link_to_route('books.show', 'Cancel', $book->
                  id,array('class' => 'btn btn-danger')) }}
    </li>
</ul>
{{ Form::close() }}
@if ($errors->any())
<ul>
    {{implode('',$errors->all('<li class="error">:message</li>'))}}
</ul>
@endif
@stop

After these modification our update function works, let's tried to update book information and we will get the next figure:

10. Delete Book

To delete a book we can use the destroy method. The same like update method If you go to our book lists view, you can find the following delete link's generation code:

 
{{ Form::open(array('method' => 'DELETE', 'route' => 
                 array('books.destroy', $book->id))) }} 
{{ Form::submit('Delete', array('class' => 'btn btn-danger')) }} 
{{ Form::close() }} 

Let's go to BookController at /app/controllers/BookController.php this request will hit the destroy() method as follows:

 
public function destroy($id)
{
 //delete book
        Book::find($id)->delete();
        return Redirect::route('books.index')
            ->withInput()
            ->with('message', 'Successfully deleted Book.');
}

Once we delete one book it will redirect to our index page and show message "Successfully deleted book" similar case in update function.

11. Pagination

We can add pagination to our CRUD book page like depicted in next picture: