Simple Laravel MySQL CRUD Example

Laravel Course

In this tutorial we create and test a simple Laravel CRUD application (Create-Read-Update-Delete) with data stored in MySQL Database, using DB facade, and Raw SQL Queries.
- You can use it as a starting point for a CMS.

• To download the files with the codes presented here, click: Laravel MySQL CRUD Example.

1. We create a resource controller called PagesMysql.
Copy the following code and save it in "app/Http/Controllers/PagesMysql.php".

<?php
namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;

//resource controller to Insert, Update, Show, delete pages in mysql
class PagesMysql extends Controller {
  protected $table ='pages';

  //create the table if not exists
  //@return string
  public function createTable(){
    $sql ='CREATE TABLE IF NOT EXISTS '. $this->table .' (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), description VARCHAR(150), content MEDIUMTEXT, dtreg TIMESTAMP NOT NULL DEFAULT NOW()) CHARACTER SET utf8 COLLATE utf8_general_ci';
    if(DB::statement($sql)) return 'Table: '. $this->table .' successfully created.<br><a href="/mysql">Main Page</a>';
    else return 'Unable to create table: '. $this->table;
  }

  //@return view() with list of pages
  public function index(){
    $pages = DB::select('select id, title from '. $this->table);
    return view('pages.index',['title'=>'Pages List', 'pages'=>$pages]);
  }

  //Show the form for creating a new page
  //@return view
  public function create(){
    return view('pages.form_insert', ['title'=>'Create New Page']);
  }

  //Insert /Store a newly created page
  //@return string
  public function store(Request $request){
    $re ='No valid request';
    if($request->has(['title', 'description', 'content'])){
      $sql ='INSERT INTO '. $this->table .' (title, description, content) VALUES (:title, :description, :content)';
      $vals =['title'=>$request->title, 'description'=>$request->description, 'content'=>$request->content];

      if(DB::insert($sql, $vals)){
        $idpg = DB::getPdo()->lastInsertId(); //auto-increment id for newly created page
        $re ='Record inserted successfully.<br><a href="/page/'. $idpg .'">See the page</a>';
      }
      else $re = 'Unable to insert data in table: '. $this->table;
    }
    return $re;
  }

  //Display the specified page
  //@param int $id
  //@return view
  public function show($id){
    $page = DB::select('select title, description, content from '. $this->table .' where id =:id LIMIT 1', ['id'=>$id]);

    //keep data for variables in view
    $page =[
      'title'=>$page[0]->title,
      'description'=>$page[0]->description,
      'content'=>$page[0]->content
    ];
    return view('pages.page', $page);
  }

  //Show the form for editing the specified page
  //@param int $id
  //@return view
  public function edit($id){
    $page = DB::select('select id, title, description, content from '. $this->table .' where id =:id LIMIT 1', ['id'=>$id]);

    //keep data for variables in view
    $page =[
      'title'=>'Edit page',
      'id'=>$page[0]->id,
      'f_title'=>$page[0]->title,
      'f_description'=>$page[0]->description,
      'f_content'=>$page[0]->content
    ];
    return view('pages.form_update', $page);
  }

  //Update the specified resource in storage.
  //@param Request $request
  //@param int $id
  //@return string
  public function update(Request $request, $id){
    $re ='No valid request';
    if($request->has(['title', 'description', 'content'])){
      $sql ='UPDATE '. $this->table .' SET title=:title, description=:description, content=:content WHERE id=:id';
      $vals =['title'=>$request->title, 'description'=>$request->description, 'content'=>$request->content, 'id'=>$id];

      if(DB::update($sql, $vals)){
        $re ='Record updated successfully.<br><a href="/page/'. $id .'">See the page</a>';
      }
      else $re = 'Unable to update data in table: '. $this->table;
    }
    return $re;
  }

  //Remove the specified page
  //@param int $id
  //@return string
  public function destroy($id){
    $nrd = DB::delete('delete from '. $this->table .' where id =:id LIMIT 1', ['id'=>$id]);
    if($nrd >.0) return 'Record deleteed successfully.<br><a href="/mysql">Click Here</a> to go back.';
    else return 'Unable to delete data in table: '. $this->table;
  }
}

2. Create a folder called "pages" in the resources/views/ directory. In the "pages/" folder we'll create five template blade files for this application.

a) Create a view file called resources/views/pages/head.blade.php and copy the following code in that file. This file will be included in the others view files.

<!doctype html>
<html lang="{{app()->getLocale()}}">
<head>
<meta charset="utf-8">
<title>{{$title}}</title>
</head>
<body>
<h1>{{$title}}</h1>

b) Create another view file called resources/views/pages/index.blade.php and copy the following code in that file. It will display a page with a link to the form for adding page data in mysql, and a list with existing pages in database.

@include('pages.head')

<a href="{{route('mysql.create')}}" title='Create New Page'>Create New Page</a>
<div id='pages'>
@if($pages && count($pages)>0)
 <ol>
  @foreach($pages as $page)
   <li><a href="/page/{{$page->id}}">{{$page->title}}</a> - <a href="/mysql/{{$page->id}}/edit">Edit</a> - <form method="post" action="/mysql/{{$page->id}}" style='display:inline'>{{ csrf_field() }} {{method_field('DELETE')}} <input type='submit' value='DELETE'/></form></li>
  @endforeach
 </ol>
@endif
</div>

</body>
</html>

c) Now we create the third view file in "pages/". Copy and save this code in: resources/views/pages/page.blade.php.
- This view will show page-data when a URI: 'page/{id}' is accessed.

@include('pages.head')

Description: {{$description}}
<h3>Content</h3>
{!!$content !!}
</body>
</html>

d) Create the form for inserting data in MySQL. Copy and save this code in: resources/views/pages/form_insert.blade.php.
- This view is returned when it is accessed the URI: /mysql/create

@include('pages.head')

<form method="post" action="/mysql">
{{ csrf_field() }}

Title: <input type='text' name='title' required /><br>
Description: <input type='text' name='description' /><br>
Content:<br>
<textarea name='content'>Content</textarea><br>
<input type='submit' value='Store'/>
</form>

</body>
</html>

e) Create the form for updating data in MySQL. Copy and save this code in: resources/views/pages/form_update.blade.php.
- This view is returned when it is accessed the URI: /mysql/{id}/edit

@include('pages.head')

<form method="post" action="/mysql/{{$id}}">
{{ csrf_field() }}
{{method_field('PUT')}}

 Title: <input type='text' name='title' value='{{$f_title}}' required /><br>
 Description: <input type='text' name='description' value='{{$f_description}}' /><br>
 Content:<br>
 <textarea name='content'>{!!$f_content!!}</textarea><br>
<input type='submit' value='Update'/>
</form>

</body>
</html>

3. Set up the routes; add the following code in the routes/web.php file:

//calls the method to create the table in mysql
Route::get('mysql/createtable','PagesMysql@createTable');

//on /page/{id} request, calls the method to show page data
Route::get('page/{id}','PagesMysql@show')->where('id', '[0-9]+');

/*
This resource handle multiple requests to Insert, Update, Show, delete pages in mysql
Automatically calls methods according to request
*/
Route::resource('mysql','PagesMysql');

Using the CRUD Application

Now, you can use this simple CRUD application to store, edit and delete page data in mysql.

1. First, visit the following URL to create the table in your database: - Output: 2. Visit this URL (or click on the "Main Page" link): - The output will appear as shown in the following image:


3. Click on the Create New Page link. It will open a page with a form for creating a new page; as shown in this image:


4. Add data in form fields, then click the "Store" button to submit the form. It should return a response like this: - If you click on the "See the page" link, it will open the page you just have created.

5. Now, open again the URL: - It will display a link to the page you have created, also, an Edit and Delete button; like in this image:


Now, you can create another page, or edit and delete existing page.

- If you click on the "Edit" link, will show a page with a form like in this image:


• To download the files with the codes presented here, click: Laravel MySQL CRUD Example.

First line ...<br>
Other line...

#id {
  letter-spacing: 2px;
}

var elm = document.getElementById("theID");
var content = elm.innerHTML;
alert(content);

echo "Address URL: http://CoursesWeb.net";