In this tutorial we create and test a
simple Laravel CRUD application (
Create-Read-Update-Delete) with
Ajax, Admin authentication system and Pagination. With data
stored in MySQL Database, using Query Builder.
Details are in the comments in code.
- You can use it as a starting point for a CMS.
• To see and test this Laravel script, click:
Demo: Laravel Ajax MySQL CRUD.
• To download the files with the codes presented here, click:
Download: Laravel Ajax MySQL CRUD.
1. We create manually a resource controller called
CrudAjax.
Copy the following code and save it in "
app/Http/Controllers/CrudAjax.php" file.
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
//resource controller to Insert, Update, Show, delete pages in mysql
class CrudAjax extends Controller {
//HERE add your Name and Password for Admin authentication; and the table name
protected $admin_name ='admin';
protected $admin_pass ='pass';
protected $table ='pages';
function __construct(Request $request){
$this->middleware(function ($request, $next){
//check if admin logged-in; redirect to '/crud' if not session 'admin_logg'
//else, if table not exist in database, display link to create it
if($request->path() !='crud' && $request->path() !='crud/adminlogg' && $request->path() !='crud/adminloggout'){
if(!session()->has('admin_logg') || session()->get('admin_logg') != ($this->admin_name .$this->admin_pass)){
return redirect('/crud')->send();
}
}
else if(session()->has('admin_logg') && !Schema::hasTable($this->table)){
exit('<h4>The table: '. $this->table .' not exist in your database.</h4><a href="/crud/createtable">Click Here to Create the Table</a>');
}
return $next($request);
});
}
//responds to /crud/adminlogg
//sets session with 'admin_logg'
//@return redirect to /crud
public function adminLogg(Request $request){
$resp ='Not valid request';
if($request->has(['name', 'pass'])){
if($request->name ==$this->admin_name && $request->pass ==$this->admin_pass){
$request->session()->put('admin_logg', ($this->admin_name .$this->admin_pass));
$resp ='Welcome Admin';
}
else $resp ='Not correct Name and Password.';
}
return redirect('/crud')->with('resp', $resp);
}
//responds to /crud/adminloggout
//delete session with 'admin_logg'
//@return redirect to /crud
public function adminLoggOut(Request $request){
if($request->session()->has('admin_logg')){
$request->session()->forget('admin_logg');
$resp ='By-By Admin';
}
else $resp ='Not Admin logged';
return redirect('/crud')->with('resp', $resp);
}
//responds to /crud/createtable
//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 redirect('/crud')->with('resp', 'Table: '. $this->table .' successfully created');
else return 'Unable to create table: '. $this->table;
}
//responds to post: /crud/pages
//@return json with pages data
public function getPages(){
$pages = DB::table($this->table)->get();
return response()->json($pages);
}
//@return view() with list of pages
public function index(){
$pages = session()->has('admin_logg') ? DB::table($this->table)->select(DB::raw('*, DATE_FORMAT(dtreg, "%Y-%m-%d") as dtreg'))->paginate(20) :[];
return view('crud.index',['title'=>'Ajax CRUD', 'pages'=>$pages]);
}
//Not needed with Ajax
//@return void
public function create(){
//
}
//Insert /Store a newly created page
//@return array
public function store(Request $request){
$re['er'] ='No valid request';
if(session()->has('admin_logg') && $request->has(['title', 'description', 'content'])){
$vals =['title'=>$request->title, 'description'=>$request->description, 'content'=>$request->content];
$id = DB::table($this->table)->insertGetId($vals);
if($id >0) $re =['re'=>'Data saved, - <a href="/crud/'.$id.'" target="_blank">See the page</a>'];
else $re['er'] ='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::table($this->table)->where('id', $id)->get();
//keep data for variables in view
$page =[
'title'=>$page[0]->title,
'description'=>$page[0]->description,
'content'=>$page[0]->content
];
return view('crud.page', $page);
}
//Not needed with Ajax
//@param int $id
//@return void
public function edit($id){
//
}
//Update the specified resource in storage.
//@param Request $request
//@param int $id
//@return array
public function update(Request $request, $id){
$re['er'] ='No valid request';
if($request->has(['id', 'title', 'description', 'content', 'dtreg'])){
$vals =['title'=>$request->title, 'description'=>$request->description, 'content'=>$request->content, 'dtreg'=>$request->dtreg];
$nr = DB::table($this->table)->where('id', $request->id)->update($vals);
if($nr >=0) $re =['re'=>$nr];
else $re['er'] ='Unable to update data in table: '. $this->table;
}
return $re;
}
//Remove the specified page data
//@param int $id
//@return array
public function destroy($id){
$nr = DB::table($this->table)->where('id', $id)->delete();
if($nr >0) return ['re'=>$nr.' row deleted'];
else return ['er'=>'Unable to delete data in table: '. $this->table];
}
}
In this controller Edit the properties: $admin_name, $admin_pass, $table with the values you want to use.
2. Create a folder called "
crud" in the
resources/views/ directory. In the "crud/" folder we'll create four template blade files for this application:
index.blade.php, form_logg.blade.php, body.blade.php, page.blade.php.
- Copy and Save the following codes in the specified files.
a)
resources/views/crud/index.blade.php - the main page.
<!doctype html>
<html lang="{{app()->getLocale()}}">
<head>
<meta charset="utf-8">
<title>{{$title}}</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<style>
body {background:#f7f8fe;margin:1px 1%;padding:0;position:relative;text-align:center;font-family:'Calibri',sans-serif;}
.bg_b{background:#e0e0f0}
.bg_g{background:#99ee99}
.cl_r{color:#ee0000}
h4:empty, div:empty{display:none;}
/* Form to authentication admin */
#admin_logg {
position:relative;
width:13em;
margin:8% auto 2em auto;
background:#fefefe;
padding:1em;
border:2px solid #bbb;
font-size:1.2em;
font-weight:700;
}
#admin_logg label {
display:block;
margin:.2em 1px;
text-align:left;
}
#admin_logg #name, #admin_logg #pass {
width:10.7em;
background:#ff1;
padding:.1em;
font-style:oblique;
}
#admin_logg #name:focus, #admin_logg #pass:focus {
background:#d1e0fb;
}
#admin_logg #name {
margin-left:2.2em;
}
#admin_logg #submit {
margin:1em auto .5em auto;
}
/* End */
h1{
font-size:22px;
margin:3px auto 8px auto;
}
#loggout{
margin:0;
font-size:18px;
position:absolute;
right:8px;
top:0;
}
#add_page{
color:#0000da;
cursor:pointer;
text-decoration:underline;
}
/*form for insert */
#f_insert{
background:#e0e0f0;
border-radius:8px;
display:none;
font-weight:700;
font-size:18px;
line-height:130%;
margin:8px auto;
padding:2px;
position:relative;
width:98%;
max-width:1100px;
}
#f_insert input[name=title], #f_insert input[name=description]{
width:88%;
max-width:400px;
}
#f_insert input[name=title]{
margin-left:48px;
}
#f_insert input[type=submit]{
display:block;
font-size:18px;
font-weight:700;
margin:2px auto;
}
#f_insert textarea{
display:block;
height:200px;
margin:5px auto;
width:98%;
}
#f_insert #f_close{
background:#efef00;
border-radius:5px;
color:#ee0000;
cursor:pointer;
padding:0 2px 2px 2px;
margin:0;
position:absolute;
right:-8px;
top:-8px;
}
/* End */
/*Table pages data*/
#pages{
background:#fbfbfe;
margin:2px auto;
position: relative;
width:99.8%;
}
#pages button{
display:block;
margin:8px auto;
}
#pages .update{
display:none;
}
#pages td{
padding:2px;
position: relative;
}
#pages .pg_title input, #pages .pg_description input{
width:calc(100% - 4px);
max-width:260px;
min-width:70px;
}
#pages .td_content{
max-height:145px;
overflow:hidden;
width:40%;
max-width:250px;
min-width:100px;
}
#pages div.pg_content{
width:100%;
height:100%;
max-height:148px;
overflow:hidden;
}
#pages div.pg_content textarea{
width:calc(100% - 4px);
height:140px;
}
#pages button{
font-weight:700;
}
/* End */
/*pagination links*/
.pagination{
background-color:#b0d0f0;
border:1px solid #eeeffe;
border-radius:8px;
list-style-type:none;
margin:10px 10%;
padding:3px 14px;
}
.pagination li{
display:inline;
font-size:18px;
font-weight:700;
}
.pagination a {
background-color:#f7f8fe;
border-radius:7px;
color:#0001da;
font-size:19px;
margin:1px 4px;
padding:1px 7px;
text-decoration:none;
}
.pagination a:hover {
background-color:#efef00;
text-decoration:underline;
}
/* End */
</style>
</head>
<body>
<h1>{{$title}}</h1>
<h4 id='p_resp' class='cl_r'>
@if(session('resp'))
{{session('resp')}}
@endif
</h4>
@if(Session::has('admin_logg'))
@include('crud.body')
@else
@include('crud.form_logg')
@endif
</body>
</html>
b)
resources/views/crud/form_logg.blade.php - form for authentication Admin.
<form action='/crud/adminlogg' method='post' id='admin_logg'>
{{ csrf_field() }}
<label for='name'>Name: <input type='text' name='name' id='name' /></label>
<label for='pass'>Password: <input type='password' name='pass' id='pass' /></label>
<input type='submit' id='submit' value='Send' />
</form>
c)
resources/views/crud/body.blade.php - contains the form to create new page, table with page data, pagination links, and JavaScript /Ajax code.
<a href="/crud/adminloggout" id='loggout' title='Log-Out'>Log-Out</a>
<h3 id='add_page'>Create New Page</h3>
<form method="post" action="/crud" id='f_insert'>
{{ csrf_field() }}
Title: <input type='text' name='title' required /><br>
Description: <input type='text' name='description' /><br>
Content:
<textarea name='content'>Content</textarea>
<input type='submit' name='f_sbmt' value='Store'/>
<h3 id='f_close'>[X]</h3>
</form>
@isset($pages)
@if(count($pages)>0)
<table id='pages' border='1' cellspacing='0'>
<tr><th>ID</th><th>Title</th><th>Description</th><th>Content</th><th>Reg. Date</th><th>Action</th></tr>
@foreach($pages as $page)
<tr><td class='pg_id'>{{$page->id}}</td><td class='pg_title'><a href="{{route('crud.show', ['id'=>$page->id])}}" target='_blank'>$page->title</a></td><td class='pg_description'>$page->description</td><td class='td_content'><div class='pg_content'>$page->content</div></td><td class='dtreg'>{{$page->dtreg}}</td><td colspan='2'><button class='edit'>Edit</button><button class='update'>Update</button><button class='delete'>Delete</button></td></tr>
@endforeach
</table>
{{$pages->links()}}
@else
<h4>No page data in database</h4>
@endif
@endisset
<script>
//the csrf_token generated by Laravel, to be sent with Ajax to server
var csrf_token ='{{csrf_token()}}';
//#p_resp elemet, for response from controller
var p_resp = document.getElementById('p_resp');
if(p_resp) window.setTimeout(function(){ p_resp.innerHTML='';}, 5000);
// Ajax, receives the url of file to access, data to send, request method, and a callback function (called when the response is received)
function ajaxSend(url, data, callback){
var req = (window.XMLHttpRequest) ? new XMLHttpRequest() : new ActiveXObject('Microsoft.XMLHTTP'); // XMLHttpRequest object
//put data from "data" into a string to be send to "php"
var str_data ='_token='+csrf_token; // to pass the csrf_token
for(var k in data){
k = k.toString(); //convert numeric key to string
//build the string with data to be sent
if(data[k]) str_data +='&'+ k +'='+ data[k].toString().replace(/\?/g, '?').replace(/=/g, '=').replace(/&/g, '&').replace(/[ ]+/g, ' ').replace(/[\+]/g, '+');
}
// alert(str_data); //for debug, to see the string that is sent
//send data to php
req.open('POST', url, true);
req.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
req.send(str_data);
//check the state request, if completed, pass the response to callback function
req.onreadystatechange = function(){
if(req.readyState == 4){
// alert(req.responseText); // debug
callback(req.responseText);
}
}
}
/*Code for Insert */
var add_page = document.getElementById('add_page');
var f_insert = document.getElementById('f_insert');
var f_close = document.getElementById('f_close');
//shows the form, reset input values
add_page.addEventListener('click', function(){
f_insert.style.display ='block';
f_insert['title'].value ='';
f_insert['description'].value ='';
f_insert['content'].value ='Content';
f_insert['f_sbmt'].removeAttribute('disabled'); //make sure isn't disabled
});
//hides the form
f_close.addEventListener('click', function(){
f_insert.style.display ='none';
});
//when submit the form
f_insert.addEventListener('submit', function(e){
e.preventDefault();
//get page data, send it to ajax
var data ={
title: f_insert['title'].value,
description: f_insert['description'].value,
content: f_insert['content'].value,
};
ajaxSend(e.target.action, data, function(resp){
var res = JSON.parse(resp);
if(res){
if(res.re){
p_resp.innerHTML = res.re;
e.target.style.display ='none';
}
else if(res.er) alert(res.er);
}
else alert(resp);
});
f_insert['f_sbmt'].setAttribute('disabled', 'disabled'); //to not click twice
return false;
});
/*end*/
/*Code for Edit/Update/Delete */
//register click on .edit buttons
var btns_edit = document.querySelectorAll('#pages td .edit');
for(var i=0; i<btns_edit.length; i++){
btns_edit[i].removeAttribute('disabled'); //make sure isn't disabled
btns_edit[i].addEventListener('click', function(e){
e.target.setAttribute('disabled', 'disabled');
var row = e.target.parentNode.parentNode; //parent /tr row
row.className ='bg_b';
//to can resize textarea with content
row.querySelector('.td_content').style.maxWidth ='none';
row.querySelector('.pg_content').style.maxHeight ='none';
//get elemets in button's row
var elms ={
title: row.querySelector('.pg_title a'),
description: row.querySelector('.pg_description'),
content: row.querySelector('.pg_content'),
dtreg: row.querySelector('.dtreg'),
};
//puts elms content in inputs
elms.title.parentNode.innerHTML ='<input type="text" value="'+ elms.title.innerHTML.replace(/"/g, '"') +'"/>';
elms.description.innerHTML ='<input type="text" value="'+ elms.description.innerHTML.replace(/"/g, '"') +'"/>';
elms.content.innerHTML ='<textarea>'+ elms.content.innerHTML.replace(/\</g, '<').replace(/\>/g, '>') +'</textarea>';
elms.dtreg.innerHTML ='<input type="date" value="'+ elms.dtreg.innerHTML +'"/>';
//after a few sec. shows .update, hides this button
window.setTimeout(function(){
row.querySelector('.update').style.display ='block';
e.target.style.display ='none';
}, 3000);
});
}
//register click on .update buttons
var btns_upd = document.querySelectorAll('#pages td .update');
for(var i=0; i<btns_upd.length; i++){
btns_upd[i].removeAttribute('disabled'); //make sure isn't disabled
btns_upd[i].addEventListener('click', function(e){
e.target.setAttribute('disabled', 'disabled');
var row = e.target.parentNode.parentNode; //parent /tr row
//get page data, send it to ajax
var data ={
id: row.querySelector('.pg_id').innerHTML*1,
title: row.querySelector('.pg_title input').value,
description: row.querySelector('.pg_description input').value,
content: row.querySelector('.pg_content textarea').value,
dtreg: row.querySelector('.dtreg input').value,
_method:'PUT'
};
ajaxSend('/crud/'+data.id, data, function(resp){
var res = JSON.parse(resp);
e.target.removeAttribute('disabled');
if(res){
if(res.re >=0){
resp = res.re +' row updated';
//reset buttons, swows data without inputs
if(res.re >0){
row.className ='bg_g';
//restore these css
row.querySelector('.td_content').style.maxWidth ='250px';
row.querySelector('.pg_content').style.maxHeight ='148px';
row.querySelector('.edit').removeAttribute('disabled');
e.target.style.display ='none';
row.querySelector('.edit').style.display ='block';
row.querySelector('.pg_title').innerHTML ='<a href="/crud/'+ data.id +'" target="_blank">'+ data.title.replace(/\</g, '<').replace(/\>/g, '>') +'</a>';
row.querySelector('.pg_description').innerHTML = data.description.replace(/\</g, '<').replace(/\>/g, '>');
row.querySelector('.pg_content').innerHTML = data.content.replace(/\</g, '<').replace(/\>/g, '>');
row.querySelector('.dtreg').innerHTML = data.dtreg;
}
}
else if(res.er) resp = res.er;
}
alert(resp);
});
});
}
//register click on .delete buttons
var btns_del = document.querySelectorAll('#pages td .delete');
for(var i=0; i<btns_del.length; i++){
btns_del[i].removeAttribute('disabled'); //make sure isn't disabled
btns_del[i].addEventListener('click', function(e){
//if delete confirmed, pass $id to ajax
if(window.confirm('Page data will be definetivelly deleted.')){
e.target.setAttribute('disabled', 'disabled');
ajaxSend('/crud/'+(e.target.parentNode.parentNode.querySelector('.pg_id').innerHTML*1), {_method:'DELETE'}, function(resp){
var res = JSON.parse(resp);
e.target.removeAttribute('disabled');
if(res){
if(res.re){
e.target.parentNode.parentNode.outerHTML ='';
resp = res.re;
}
else if(res.er) resp = res.er;
}
alert(resp);
});
}
});
}
</script>
d)
resources/views/crud/page.blade.php - will show page-data when a URI: '
crud/{id}' is accessed.
<!doctype html>
<html lang="{{app()->getLocale()}}">
<head>
<meta charset="utf-8">
<title>{{$title}}</title>
</head>
<body>
<h1>{{$title}}</h1>
<strong>Description</strong>: {{$description}}
<h3>Content:</h3>
{!!$content !!}
</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('/crud/createtable','CrudAjax@createTable');
//authentication admin in ajax-crud application
Route::post('/crud/adminlogg', 'CrudAjax@adminLogg');
//to log-out admin in ajax-crud application
Route::get('/crud/adminloggout', 'CrudAjax@adminLoggOut');
//to get pages data
Route::post('/crud/pages', 'CrudAjax@getPages');
//uses CrudAjax as a resource
Route::resource('/crud', 'CrudAjax');
Using the CRUD Application
Now, you can use this simple Ajax-CRUD application to store, edit and delete page data in mysql.
1. Visit the following URL, and authenticate with your
$admin_name and
$admin_pass data.
//localhost:8000/crud
2. You will be notified to create the table in your database.
- After the table is created, you can add pages with:
Title, Description, Content, and
Registered-Date in your database.
If you have questions related to this Laravel CRUD application, ask on:
CoursesWeb Forum
• To see and test this Laravel script, click:
Demo: Laravel Ajax MySQL CRUD.
• To download the files with the codes presented here, click:
Download: Laravel Ajax MySQL CRUD.