Laravel Working With Database

0
1776

The MVC framework has made it easy to integrate database. It currently supports only 4 major databases which are MySQL, Postgres, SQLite, and SQL Server.You can fire a query to any of these databases by the use of raw SQL, Eloquent ORM, and the fluent query builder. Here, we will be focusing mainly on the 4 operations that can be performed on the databases which include Create, Read, Update, and Delete, in short CRUD.

Also Read: Laravel Redirections

For your better understanding, below is an example.
Here, we are creating a basic student management database. You can find this database.php file in the config directory. To start with this database, first, create a college database with the help of MySQL. Create the table as below provided structure.

Name of Database: college
Name of Table: student

Column Name Column Datatype Remarks
ID int(11) Primary Key (PK) / Auto Increment
Name varchar(25)

Now, take a brief look at how to perform the 4 primary operations (create, read, update, and delete) on the student table.

How to Insert Records?
With the help of this operation, you can insert a record in a pre-existing database. Follow the given syntax strictly when using an insert() method. Below are the syntax and a few important information to use insert method properly.

Syntax bool insert(string $query, array $bindings = array())
Parameters * $query(string) – to fire a query in a database
* $bindings(array) – to bind values with queries
Returns bool
Description You can insert a record in a database

Follow the example to get a clear picture of how to use insert method.
Step 1: Create a plain controller by the name StudInsertController.
laravel> php artisan make:controller StudInsertController –plain

Step 2: After the successful execution of the command, include the code provided below. It should be inserted in the StudInsertController.php file. To locate this file, follow the path: app/Http/Controllers/.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudInsertController extends Controller
{
public function insertform()
{
return view(‘stud_create’);
}
public function insert(Request $request)
{
$name = $request->input(‘stud_name’);
DB::insert(‘insert into student (name) values(?)’,[$name]);
echo “Record inserted successfully.<br/>”;
echo ‘<a href = “/insert”>Click Here</a> to go back.’;
}
}

Step 3: Now, you need to create a view file called stud_create.php. Save this file in the path: resources/views/. In this file, copy the below provided piece of code.
<html>
<head>
<title> Student Management | Add </title>
</head>

<body>
<form action = “/create” method = “post”>
<input type = “hidden” name = “_token” value = “<?php echo csrf_token(); ?>”>

<table>
<tr> <td> Name </td>
<td> <input type=’text’ name=’stud_name’ /> </td> </tr>
<tr> <td colspan = ‘2’>
<input type = ‘submit’ value = “Add student”/>
</td> </tr>
</table>
</form>
</body>
</html>

Step 4: After this, you are required to open routes.php file. Here, add the following content. You can find this file in the path: app/Http/.
Route::get(‘insert’,’StudInsertController@insertform’);
Route::post(‘create’,’StudInsertController@insert’);

Step 5: Now, to check if it is working properly or not. Copy the URL: http://localhost:8000/insert to the web server. And you will get an output like the following.

How To Retrieve Records?
This operation will work only when you have successfully configured the database. Well, to retrieve records from a configured database, you can use select() method. Below is the syntax for select() method with a few relevant information.

Syntax array select(string $query, array $bindings = array())
Parameters * $query(string) – for the execution of database
* $bindings(array) – binds values with associated queries
Returns array
Description You can retrieve a record from the database

To understand select() method, take a look at the following example.
Step 1: Create a controller named as StudViewController. Note that, it is a plain constructor.
laravel> php artisan make:controller StudViewController –plain

Step 2: After the successful creation of the controller, Include the given lines of code in StudViewController.php file. You can locate this file at app/Http/Controllers/.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudViewController extends Controller
{
public function index()
{
$users = DB::select(‘select * from student’);
return view(‘stud_view’,[‘users’=>$users]);
}
}

Step 3: Create a file in resources/views/. Name this file as stud_view.blade.php. Now, add the following code to the file.
<html>
<head>
<title>View Student Records</title>
</head>

<body>
<table border = 1>
<tr> <td> ID </td>
<td> Name </td> </tr>
@foreach($users as $user)
<tr>
<td> {{ $user->id }} </td>
<td> {{ $user->name }} </td>
</tr>
@endforeach
</table>

</body>
</html>

Step 4: Now, copy the following line of code in the routes.php file. You can find this file in app/Http/ path.
Route::get(‘view-records’,’StudViewController@index’);

Step 5: To see the final output, copy the given URL: http://localhost:8000/view-records to the PHP’s web server. This query will let you see the stored records from the database.

How To Update Records?
You can easily update pre-existing records in a database with the help of update() method. You can take a look at the table given below. It contains the syntax and other useful information related to update() method.

Syntax int update(string $query, array $bindings = array())
Parameters * $query(string) – for the execution of database
* $bindings(array) – binds values with associated queries
Returns int
Description You can update a pre-existing record in the database

Here is an example to learn about update() method more clearly.
Step 1: Execute the given command to create a controller with the name StudUpdateController.
laravel> php artisan make:controller StudUpdateController –plain

Step 2: After the successful execution of the command, copy the given code to the StudUpdateController.php file. You can find this file on the path: app/Http/Controllers/.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudUpdateController extends Controller
{
public function index()
{
$users = DB::select(‘select * from student’);
return view(‘stud_edit_view’,[‘users’=>$users]);
}

public function show($id)
{
$users = DB::select(‘select * from student where id = ?’,[$id]);
return view(‘stud_update’,[‘users’=>$users]);
}

public function edit(Request $request,$id)
{
$name = $request->input(‘stud_name’);
DB::update(‘update student set name = ? where id = ?’,[$name,$id]);
echo “Record updated successfully.<br/>”;
echo ‘<a href = “/edit-records”>Click Here</a> to go back.’;
}
}

Step 3: Now, create a file in resources/views/. Name the file as stud_edit_view.blade.php. Add the following code to the file.
<html>
<head>
<title>View Student Records</title>
</head>

<body>
<table border = “1”>
<tr>
<td> ID </td>
<td> Name </td>
<td> Edit </td>
</tr>
@foreach ($users as $user)
<tr>
<td> {{ $user->id }} </td>
<td> {{ $user->name }} </td>
<td> <a href = ‘edit/{{ $user->id }}’>Edit</a> </td>
</tr>
@endforeach
</table>

</body>
</html>

Step 4: After this, create another file with the name stud_update.php. Save this file in the path resources/views/.
<html>

<head>
<title>Student Management | Edit</title>
</head>

<body>
<form action = “/edit/<?php echo $users[0]->id; ?>” method = “post”>
<input type = “hidden” name = “_token” value = “<?php echo csrf_token(); ?>”>

<table>
<tr>
<td> Name </td>
<td>
<input type = ‘text’ name = ‘stud_name’
value = ‘<?php echo$users[0]->name; ?>’/> </td>
</tr>
<tr>
<td colspan = ‘2’>
<input type = ‘submit’ value = “Update student” /> </td>
</tr>
</table>
</form>

</body>
</html>

Step 5: The last piece of code is to be added to routes.php. You can find this file at app/Http/.
Route::get(‘edit-records’,’StudUpdateController@index’);
Route::get(‘edit/{id}’,’StudUpdateController@show’);
Route::post(‘edit/{id}’,’StudUpdateController@edit’);

Step 6: Now, to update the records in a database, copy the URL: http://localhost:8000/edit-records to the web server and you will be displayed with the following output.

Step 7: Click the edit link to whichever record you want to edit. After which, you will be redirected to the page where you can edit the record.

After the successful operation on the record, you will get the following output.

How To Delete Records?
The delete() method helps you to delete records from an already configured database. You can take a look at the syntax of the delete() method before we proceed further.

Syntax int delete(string $query, array $bindings = array())
Parameters * $query(string) – for the execution of database
* $bindings(array) – binds values with associated queries
Returns int
Description You can delete a pre-existing record from the database

Here is an example for you to get a clearer picture of delete() method and its use.
Step 1: Start with the creation of StudDeleteController.
laravel> php artisan make:controller StudDeleteController –plain

Step 2: After the successful creation of the controller, copy the following code to the StudDeleteController.php. This file is stored in app/Http/Controllers/.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class StudDeleteController extends Controller
{
public function index()
{
$users = DB::select(‘select * from student’);
return view(‘stud_delete_view’,[‘users’=>$users]);
}
public function destroy($id)
{
DB::delete(‘delete from student where id = ?’,[$id]);
echo “Record deleted successfully.<br/>”;
echo ‘<a href=”/delete-records”>Click Here</a> to go back.’;
}
}

Step 3: Create another file by the name stud_delete_view.blade.php. Save this file in resources/views/.
<html>
<head>
<title>View Student Records</title>
</head>

<body>
<table border = “1”>
<tr> <td> ID </td>
<td> Name </td>
<td> Edit </td> </tr>
@foreach ($users as $user)
<tr>
<td> {{ $user->id }} </td>
<td> {{ $user->name }} </td>
<td> <a href = ‘delete/{{ $user->id }}’>Delete</a> </td>
</tr>
@endforeach
</table>

</body>
</html>

Step 4: Now, to finish the whole process, include the following code the routes.php file. You will get this file in app/Http/.
Route::get(‘delete-records’,’StudDeleteController@index’);
Route::get(‘delete/{id}’,’StudDeleteController@destroy’);

Step 5: The output will look like the following.

Database Transactions in Laravel

To delete a record from the database, you will then be required to click any of the edit links. After which you will be redirected to the following page.

After the successful deletion of the record, you will get the following output.