phpMyAdmin for FTP or SFTP - Laravel

In our previous post, we gave a detailed explanation of why you need a database viewer if you are working with only FTP or SFTP. In this post, we will create a phpMyAdmin for developers who have access to FTP or SFTP only while working on a Laravel project.

Let’s get started

First, you need to create a separate route and a separate controller. Following will be your routes, paste them into your routes/web.php file:

use App\Http\Controllers\PHPMyAdminController;

Route::prefix("/phpmyadmin")->group(function () {

    Route::get("/", [PHPMyAdminController::class, "index"]);
    // all other routes will reside here

});

Connect with SSH

Now we need to create a controller, and run the following command in your Terminal or Git bash. Now if you are working via FTP then you might only have SSH access, if you do not know how to connect with your server via SSH, please follow the below tutorial first:

Creating a controller

After you have connected with SSH, open your project root folder in your command prompt and run the following command:

php artisan make:controller PHPMyAdminController

This will create an empty controller file in your app/Http/Controllers/PHPMyAdminController folder.

use DB;
use Schema;

class PHPMyAdminController extends Controller
{

}

Install “dbal” library from composer

Now we need to fetch all tables. You need to run the following command first:

composer require doctrine/dbal

This command will install a library that helps to get all table names in the connected database. Paste the following lines in your PHPMyAdminController file:

public function index(Request $request)
{
    $tables = DB::connection()->getDoctrineSchemaManager()->listTableNames();

    return view("phpmyadmin.tables", [
        "tables" => $tables
    ]);
}

Code at line #3 will fetch all the tables from the connected database. We have created a separate folder named resources/views/phpmyadmin, we will place all our views files here.

View all tables in MySQL database

First, create a file named tables.blade.php and display all tables using the following code:

<!-- tables.blade.php -->

<table>
    <thead>

        <tr>
            <th>name</th>
            <th></th>
        </tr>

    </thead>
    <tbody>

        @foreach ($tables as $table)
            <tr>
                <td>{{ $table }}</td>
                <td>
                    <div>
                        <a href="{{ url()->to('/phpmyadmin/browse-table/' . $table) }}">
                            Browse
                        </a>
                    </div>
                </td>
            </tr>
        @endforeach

    </tbody>
</table>

You can design it as per your desire. It will display all table names along with the button “Browse”. On click it will redirect to the browse-table route we will create in the next step.

View table data

Create the following route in your “phpmyadmin” group in web.php:

Route::get("/browse-table/{name}/{search?}", [PHPMyAdminController::class, "browse_table"]);

It has a name required parameter which tells the name of table whose data you want to view. Search parameter is optional, it will only be used when performing the search functionality. Any get parameter that you want to make optional in Laravel should have question mark (?) at the end. Now create its function in your controller:

public function browse_table(Request $request, $name, $search = "")
{
    $columns = Schema::getColumnListing($name);

    if (empty($search))
    {
        $data = DB::table($name)->paginate();
    }
    else
    {
        $query = DB::table($name);
        foreach ($columns as $column)
        {
            $query = $query->orWhere($column, "LIKE", "%" . $search . "%");
        }
        $data = $query->paginate();
    }

    return view("phpmyadmin.browse-table", [
        "name" => $name,
        "search" => $search,
        "data" => $data,
        "columns" => $columns
    ]);
}

First it gets all columns of selected table. Then it checks if the search query is made, right now the if condition will be true and code at line #7 will be executed.

It will get few records from that table and with pagination, which means that it can display a pagination to view more records. Finally we are sending that data in our browse-table view. Create a file named browse-table.blade.php and view all rows of selected table:

<!-- browse-table.blade.php -->

<table>
    <thead>
        <tr>
            @foreach ($columns as $column)
                <th>{{ $column }}</th>
            @endforeach
        </tr>
    </thead>

    <tbody>

        @foreach ($data as $d)
            <tr>
                @foreach ($columns as $column)
                    <td>{{ $d->{$column} }}</td>
                @endforeach
                <td>
                    <a href="{{ url()->to('/phpmyadmin/edit_row/' . $name . '/' . $d->id) }}">
                        Edit
                    </a>
                    
                    <form method="POST" action="{{ url()->to('/phpmyadmin/delete_row') }}" onsubmit="return confirm('Are you sure you want to delete this row ?');">
                        {{ csrf_field() }}

                        <input type="hidden" name="name" value="{{ $name }}">
                        <input type="hidden" name="id" value="{{ $d->id }}">

                        <button class="item" type="submit">
                            <i class="zmdi zmdi-delete"></i>
                        </button>
                    </form>
                </td>
            </tr>
        @endforeach

    </tbody>
</table>

{{ $data->links() }}

This will display all columns in table header. In tbody, first it will loop through all rows then it will display each column value. Last column will have 2 buttons, one to edit the and one to delete the row. To add rows, we will be using Laravel seeders. When the delete button is clicked, we will show a confirmation dialog, if user presses “okay” then we will delete the row.

Delete row

Create the following route in your routes/web.php file inside phpmyadmin group:

Route::post("/delete_row", [PHPMyAdminController::class, "delete_row"]);

Now create a function in your controller class that will delete that row using its ID from the selected table.

public function delete_row(Request $request)
{
    DB::table($request->get("name"))->where("id", "=", $request->get("id"))->delete();
    return redirect()->back()->with("danger", "Record has been deleted");
}

Edit row

After that, we come to the editing part. Create a route which will fetch the selected row for edit and display it in input fields:

Route::get("/edit_row/{name}/{id}", [PHPMyAdminController::class, "edit_row"]);

Then create a function in your controller class to fetch the row and its columns and render it in our new blade template:

public function edit_row(Request $request, $name, $id)
{
    $data = DB::table($name)->where("id", "=", $id)->first();
    if ($data == null)
    {
        return redirect()->back()->with("error", "Record does not exists.");
    }
    $columns = Schema::getColumnListing($name);
    return view("phpmyadmin.edit-row", [
        "data" => $data,
        "name" => $name,
        "id" => $id,
        "columns" => $columns
    ]);
}

Make sure you added use Schema; at the top of your controller class. Now create a blade template file named “edit-row.blade.php” in our resources/views/phpmyadmin folder.

<form action="{{ url()->to('/phpmyadmin/edit_row') }}" method="post">

    {{ csrf_field() }}

    <input type="hidden" name="phpmyadmin_tablename" value="{{ $name }}">
    <input type="hidden" name="phpmyadmin_tableid" value="{{ $id }}">

    @foreach ($columns as $column)
        @if ($column == "id")
            @php continue @endphp
        @endif
        <p>
            <input name="{{ $column }}" value="{{ $data->{$column} }}">
        </p>
    @endforeach
    
    <button type="submit">
        Edit row
    </button>
</form>

This will create a form, a required CSRF field. 2 hidden fields for table name and row ID which is being updating. Then looping through all columns, skipping the ID column and displaying them in input fields. Lastly, a submit button.

Now we need to create it’s post route in our web.php file:

Route::post("/edit_row", [PHPMyAdminController::class, "do_edit_row"]);

Now create it’s function “do_edit_row” in your controller class:

public function do_edit_row(Request $request)
{
    $data = DB::table($request->get("phpmyadmin_tablename"))->where("id", "=", $request->get("phpmyadmin_tableid"))->first();
    if ($data == null)
    {
        return redirect()->back()->with("error", "Record does not exists.");
    }
    $columns = Schema::getColumnListing($request->get("phpmyadmin_tablename"));
    $data = array();
    foreach ($columns as $column)
    {
        if ($column == "id")
        {
            continue;
        }
        $data[$column] = $request->get($column);
    }

    DB::table($request->get("phpmyadmin_tablename"))->where("id", "=", $request->get("phpmyadmin_tableid"))
        ->update($data);
    return redirect()->back()->with("success", "Record has been updated.");
}

First we are checking if the row exists in selected table. Then we are getting all columns of that table skipping the ID column. Finally, we are updating the database and returning the user back with a success message.

Create new table or add column in existing table using Laravel migration

Migrations in Laravel are used to change the structure in database, to create a new table or to add a column in existing table. To create a migration to create a new table, run the following command in your terminal:

php artisan make:migration create_my_test_table

This will create a file named create_my_test_table.php in database/migrations folder. Set the content of this file to the following:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateMyTestTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('my_test', function (Blueprint $table) {
            $table->id();
            $table->string("name");
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('my_test');
    }
}

To run the migration, you need to run the following command:

php artisan migrate

This will create a table named “my_test” in your database with 4 columns:

ID auto increment unique primary key
name TEXT
created_at DATETIME
updated_at DATETIME

Now if you want to create a new column in existing table, run the following command to create it’s migration:

php artisan make:migration add_email_to_my_test

This will create a file named “add_email_to_my_test.php” in database/migrations folder. Set the content of this file to the following:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddEmailToMyTest extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('my_test', function (Blueprint $table) {
            $table->string("email");
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('my_test', function (Blueprint $table) {
            //
        });
    }
}

Now again you have to run the migration using following command:

php artisan migrate

This will create a new column named “email” with datatype TEXT in my_test table.

Add data using Laravel seeders

To create a seeder, first you need to run the following command in your terminal:

php artisan make:seeder MyTestSeeder

This will create a file named “MyTestSeeder.php” in database/seeders folder. Set the content of this file to the following:

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;

use DB;

class MyTestSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table("my_test")->insert([
            "name" => "Adnan",
            "email" => "adnan@gmail.com",
            "created_at" => now(),
            "updated_at" => now()
        ]);
    }
}

Now to run the seed, you have to run the following command:

php artisan db:seed --class=MyTestSeeder

This will run this specific seed and will insert a new row with values mentioned in the MyTestSeeder class.

[wpdm_package id=’834′]

phpMyAdmin for FTP or SFTP – Laravel