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:

  1. ID auto increment unique primary key
  2. name TEXT
  3. created_at DATETIME
  4. 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′]

View database with FTP or SFTP – Core PHP

In this article, we are going to teach you how you can view your database if you only have been given access to FTP or SFTP. phpMyAdmin can easily be accessed from cPanel but in this tutorial, we will show you how you can view the database without the cPanel access.

If you are working on a PHP project and you have been given only FTP or SFTP details by your client or project owner, then it would be a problem for you to see the actual data on live site. You have to run queries to add, edit, view or delete some data from database. Of course, you can export the database and import in your localhost to see the structure. But what if you have to add some columns or a whole new table on live site, then it would be difficult and time consuming for you. So we have created a script that allows you to:

  • View all tables in database
  • Add new table
  • Browse each table (with pagination)
  • Add new columns in a specific table
  • Delete columns from specific table
  • Add new rows in table
  • Edit and delete rows

You just need to create a folder using your FTP in your project anywhere you want to access the database and start creating files in that folder. You can access that folder directly from your browser, only you will know the path of that folder since you created it. Moreover, it needs to connect with database before performing any action and you can get the database credentials using FTP.

Following will be our file structure:

[adnanplugin_shortcode_treeview id=”phpmyadmin-for-ftp”]

You can download the design template from here.

Connect with database

First we will create a form from where you can connect with database because the script does not know the username, password and database name. As you already have FTP access, you can find these values.

<!-- index.php -->

<form action="connect.php" method="post">
    <p>
        <input name="username" type="text" required>
    </p>

    <p>
        <input name="password" type="password" required>
    </p>

    <p>
        <input name="database" type="text" required>
    </p>

    <p>
        <button name="submit_connect" type="submit">
            Connect
        </button>
    </p>
</form>

The script needs only database user’s name, user’s password and database’s name. When this form is submitted, the data will be send to connect.php file. Now we need to create this file and process the form:

<?php

    // connect.php

    session_start();

    if (isset($_POST["submit_connect"]))
    {
        $username = $_POST["username"];
        $password = $_POST["password"];
        $database = $_POST["database"];

        $conn = mysqli_connect("localhost", $username, $password, $database) or die(mysqli_connect_error());

        $_SESSION["phpmyadmin_username"] = $username;
        $_SESSION["phpmyadmin_password"] = $password;
        $_SESSION["phpmyadmin_database"] = $database;
        $_SESSION["phpmyadmin_connected"] = true;

        header("Location: tables.php");
        exit();
    }
?>

Fill in your correct database’s username, password and database name and submit the form. If the credentials are okay, the database will be connected and the session will be started. We are using PHP sessions because once the database is connected, the session variables will be required on all other pages to perform all database related functions like getting all tables, create new table, browser tables etc. When the database is successfully connected then all values of the form will be saved in session variables separately along with an additional variable phpmyadmin_connected. Its value is true and it will only be used to identify if the user is logged in or not so when you have performed the database actions you wanted to perform then you can safely disconnect from it and it will no longer be accessed from the URL.

Disconnect

Now that you have connected, you will be able to perform database structure related actions. Now we need to have the ability to disconnect from database, because someone will try to access the URL from your laptop where the session is still created and can delete some data from database or drop all your tables.

Create an anchor tag anywhere in your index.php labeled Logout. When clicked we will simply remove the above session variables from $_SESSION array.

<a href="logout.php">Logout</a>

Now in your logout.php simply do the following:

<?php
    
    // logout.php

    session_start();

    unset($_SESSION["phpmyadmin_username"]);
    unset($_SESSION["phpmyadmin_password"]);
    unset($_SESSION["phpmyadmin_database"]);
    unset($_SESSION["phpmyadmin_connected"]);

    header("Location: index.php");
    exit();
?>

View all tables

First thing that every developer working on FTP or SFTP needs, when it comes to database, is to view all tables created in that database. So you just need to create a new file named tables.php in the folder you created in first section and show all tables in tabular form:

<?php
    
    // tables.php

    $conn = mysqli_connect("localhost", $_SESSION["phpmyadmin_username"], $_SESSION["phpmyadmin_password"], $_SESSION["phpmyadmin_database"]) or die(mysqli_connect_error());    
?>

<table>
    <tr>
        <th>name</th>
        <th>actions</th>
    </tr>

    <?php
        $result = mysqli_query($conn, "SHOW TABLES");
        while ($row = mysqli_fetch_object($result)):
            $table_name = $row->{"Tables_in_" . $_SESSION["phpmyadmin_database"]};
    ?>
        <tr>
            <td><?php echo $table_name; ?></td>
            <td>
                <a href="view-table.php?name=<?php echo $table_name; ?>">
                    Browse
                </a>

                <a onclick="return confirm('Are you sure you want to drop this table ?');" href="delete-table.php?name=<?php echo $table_name; ?>">
                    Drop table
                </a>
            </td>
        </tr>

    <?php endwhile; ?>
</table>

First we are connecting with database using credentials from sessions, if the session values are incorrect or if you have disconnected then the values does not exists, in both of these cases the database will not connect. If the credentials are correct then the database will be connected, a table tag will be created with 1 row for heading and 2 cells (name and actions).

SHOW TABLES is an SQL query which returns all the tables in the database. In each row returned from this query, we have a key named Tables_in_databasename where databasename is the name of your database. We are already storing that in session variable so we can fetch it from there. This key will have the value of table name, this is all we need to perform further actions.

In second cell, we are creating 2 buttons (browse the table and delete). First we will discuss view function as browsing the database is the most important feature every developer working on FTP needs. You can apply some styles to your table tag if you want:

<style>
    table, th, td {
        border: 1px solid black;
        border-collapse: collapse;
    }
    th, td {
        padding: 10px;
    }
</style>

Browse single table

Create a file named view-table.php, here we will get the table name from URL and display all columns and rows from it. We need to run 2 queries, one to get columns and one to get rows:

<?php
    
    // view-table.php

    $conn = mysqli_connect("localhost", $_SESSION["phpmyadmin_username"], $_SESSION["phpmyadmin_password"], $_SESSION["phpmyadmin_database"]) or die(mysqli_connect_error());
    $name = $_GET["name"];

    $rows = array();
    $columns = array();

    $page_number = isset($_GET["page"]) ? $_GET["page"] : 1;
    $record_per_page = 100;
    $start_from = ($page_number - 1) * $record_per_page;

    $result = mysqli_query($conn, "SELECT * FROM " . $name . " LIMIT " . $start_from . ", " . $record_per_page);
    while ($row = mysqli_fetch_object($result))
    {
        array_push($rows, $row);
    }

    // Getting total number of records
    $result = mysqli_query($conn, "SELECT COUNT(*) AS total FROM " . $name);
    $total = mysqli_fetch_object($result)->total;
     
    // Calculating number of pagination links required
    $pages = number_format($total / $record_per_page);

    $result = mysqli_query($conn, "SHOW COLUMNS FROM " . $name);
    while ($row = mysqli_fetch_object($result))
    {
        array_push($columns, $row);
    }
?>

<table>
    <tr>
        <?php foreach ($columns as $column): ?>
            <th><?php echo $column->Field; ?></th>
        <?php endforeach; ?>
    </tr>

    <?php foreach ($rows as $row): ?>
        <tr>
            <?php foreach ($columns as $column): ?>
                <td><?php echo $row->{$column->Field}; ?></td>
            <?php endforeach; ?>
        </tr>
    <?php endforeach; ?>
</table>

<ul class="pagination">
    <?php for ($a = 1; $a <= $pages; $a++): ?>
        <li class="<?php echo $a == $page_number ? 'active' : ''; ?>">
            <a href="view-table.php?name=<?php echo $name; ?>&page=<?php echo $a; ?>">
                <?php echo $a; ?>
            </a>
        </li>
    <?php endfor; ?>
</ul>

We are using pagination to browse the data because we do not know how much data one table contains. If we try to load all data at once then it might crash your browser. Let’s discuss each step.

First we are connecting with database, then getting the name from URL using PHP built-in GET variable. Then creating 2 arrays, one for rows and one for columns, we are saving the data in arrays because we will be needing that multiple times. Then we are getting data from database as 100 records per page. We have written a detailed article on how to implement pagination, check this out.

Then we are getting all columns of selected table. Then we are creating a table tag and displaying all columns in the first row as heading. Moving forward you will see 2 loops, 1 inside another. First we are looping through each row, creating a tr tag then looping through each column and displaying the value from $row variable using the key from $column variable. {} operator is used to fetch object key using another variable.

Finally we are creating an un-ordered list to display the number of pages. This depends on the number of records per page you set, higher the record_per_page results in less pages, lower the record_per_page results in more pages. So, with only FTP access, we are able to view the database. We are also applying a class named active to the list item of current page, by default bootstrap will highlight the list item which has an “active” class.

Edit row in table

With FTP access, you can not only view the database. But you can also edit the rows and columns too. Now that you are browsing the data from database table, you can create 2 buttons (edit, delete) at the of each row as you did for tables. You can create those buttons by creating a td tag right after the $columns loop:

<a href="edit-row.php?name=<?php echo $name; ?>&id=<?php echo $row->id; ?>">
    Edit row
</a>

<a onclick="return confirm('Are you sure you want to delete this row ?');" href="delete-row.php?name=<?php echo $name; ?>&id=<?php echo $row->id; ?>">
    Delete row
</a>

Now create a file named edit-row.php and first show all values of that row in input fields. In this file we will be receiving table name and ID of row which needs to be updated:

<?php

    // edit-row.php
    // make sure to connect with database as in previous step

    $name = isset($_GET["name"]) ? $_GET["name"] : "";
    $id = isset($_GET["id"]) ? $_GET["id"] : "";

    if (isset($_POST["submit_edit_row"]))
    {
        $sql = "UPDATE " . $name . " SET ";
        
        foreach ($_POST as $key => $value)
        {
            if ($key == "submit_edit_row")
            {
                continue;
            }
            $input =  mysqli_real_escape_string($conn, $value);
            $sql .= $key . " = '" . $input . "', ";
        }
        
        $sql = rtrim($sql, ", ");
        $sql .= " WHERE id = " . $id;

        $result = mysqli_query($conn, $sql);
    }

    $columns = array();
    $result = mysqli_query($conn, "SELECT * FROM " . $name . " WHERE id = " . $id);
    $data_row = mysqli_fetch_object($result);

    $result = mysqli_query($conn, "SHOW COLUMNS FROM " . $name);
    while ($row = mysqli_fetch_object($result))
    {
        array_push($columns, $row);
    }
?>

<form action="edit-row.php?name=<?php echo $name; ?>&id=<?php echo $id; ?>" method="post">
    <?php
        foreach ($columns as $column):
            if ($column->Field == "id")
            {
                continue;
            }
    ?>
        <div class="form-group">
            <input name="<?php echo $column->Field; ?>" value="<?php echo $data_row->{$column->Field}; ?>" type="text">
        </div>
    <?php endforeach; ?>
    
    <button name="submit_edit_row" type="submit">
        Edit row
    </button>
</form>

This will display all current values of that row in input fields, upon submit will update the values in database. We are skipping the ID field in input fields because that is the primary key and we should not display it in input field because someone may accidentally update it’s value.

Create a new table

If you ever worked with only FTP access, you might be needing that function. Suppose client ask to add a new feature and that feature requires a new table in database, then you can create a form to enter table name and when submit will create a table with that name in the database.

<?php

    // add-table.php
    // make sure to connect with database

    if (isset($_POST["submit_add_table"]))
    {
        $name = mysqli_real_escape_string($conn, $_POST["name"]);

        mysqli_query($conn, "CREATE TABLE IF NOT EXISTS " . $name . "(
            id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT
        );");
    }
?>

<form action="add-table.php" method="post">
    <input name="name" type="text" required>

    <button name="submit_add_table" type="submit">
        Create table
    </button>
</form>

This will create a new table in database if not already exists with same name. It will have just 1 auto increment key which will be a primary key of that table. You can learn how to add more columns in table in the next step.

Add column in table

If you are adding more features in the project then you might also require to add new columns in current table. You can simply do that be getting the name of column and datatype and add it using ALTER command. Create a new file named add-column.php and it will have table name in the URL parameter. So it can be accessed like this:

<a href="add-column.php?name=<?php echo $name; ?>">Add column</a>

Where $name will be the name of table in which you want to add a new column.

<?php

    // add-column.php
    // make sure to connect with database

    $name = isset($_GET["name"]) ? $_GET["name"] : "";

    if (isset($_POST["submit_add_column"]))
    {
        $column_name = mysqli_real_escape_string($conn, $_POST["name"]);
        $datatype = mysqli_real_escape_string($conn, $_POST["datatype"]);

        mysqli_query($conn, "ALTER TABLE " . $name . " ADD COLUMN " . $column_name . " " . $datatype . " NOT NULL");
    }
?>
 
<form action="add-column.php?name=<?php echo $name; ?>" method="post">
    <input name="name" type="text" class="form-control" required>
    
    <select name="datatype" class="form-control" required>
        <option value="">Please select</option>
        <option value="INTEGER">INTEGER</option>
        <option value="TEXT">TEXT</option>
        <option value="DOUBLE">DOUBLE</option>
        <option value="DATE">DATE</option>
        <option value="TIME">TIME</option>
        <option value="DATETIME">DATETIME</option>
        <option value="BOOLEAN">BOOLEAN</option>
    </select>
    
    <div>
        <button name="submit_add_column" type="submit">
            Create column
        </button>
    </div>
</form>

When you fill-in this form and hit submit, it will create a new column in selected table in the database. Most common data types are being displayed in the select tag but you can add more if you want.

Drop a column

Dropping a column results in deleting all the data exists in that column. To do that we are going to show a list of all columns in selected table. Create a new file named delete-column.php and it will also have a name parameter in the URL. That will be the name of table whose column needs to be dropped.

<?php

    // delete-column.php
    // make sure to connect with database

    $name = isset($_GET["name"]) ? $_GET["name"] : "";

    if (isset($_POST["submit_delete_column"]))
    {
        $column_name = mysqli_real_escape_string($conn, $_POST["column_name"]);

        if ($column_name == "id")
        {
            echo "<p>Sorry cannot deleted ID primary key.</p>";
        }
        else
        {
            mysqli_query($conn, "ALTER TABLE " . $name . " DROP COLUMN " . $column_name);
        }
    }
?>

<form action="delete-column.php?name=<?php echo $name; ?>" method="post">
    <select name="column_name" required>
        <option value="">Please select</option>
        <?php
            $result = mysqli_query($conn, "SHOW COLUMNS FROM " . $name . " WHERE Field != 'id'");
            while ($row = mysqli_fetch_object($result)):
        ?>
            <option value="<?php echo $row->Field; ?>"><?php echo $row->Field; ?></option>
        <?php endwhile; ?>
    </select>

    <button name="submit_delete_column" type="submit">
        Delete column
    </button>
</form>

First we are creating a form which will be submitted to the page itself. We are getting all the columns from the table except ID as it is a primary key and it should not be dropped. Then we are displaying each column name in option tag. The value attribute of option tag will the column name too. And finally a submit button which when clicked will submit the form.

When the form is submitted, we are connecting with database, validating input field from SQL injection. Making sure you are not dropping the ID attribute. And finally running the ALTER query to drop the column from selected table. Doing so will delete all the data saved in that column as well.

Add a row in the table

The function for adding a row will be almost similar to the one we did for editing the row earlier in this article. You just have to get all columns of that table except for ID as it is auto-incremented. When the form is submitted prepare an SQL query and run the command to INSERT the row.

<?php

    // add-row.php
    // make sure to connect with database

    // name of table where row will be added
    $name = isset($_GET["name"]) ? $_GET["name"] : "";

    // check if the form is submitted
    if (isset($_POST["submit_add_row"]))
    {
        // preparing INSERT query column names
        $sql = "INSERT INTO " . $name . "(";

        // prepare VALUES clause for INSERT query
        $values = " VALUES (";
        
        // loop through all input fields
        foreach ($_POST as $key => $value)
        {
            // skip the submit button
            if ($key == "submit_add_row")
            {
                continue;
            }

            // prevent each input field from SQL injection
            $input =  mysqli_real_escape_string($conn, $value);

            // append column name in INSERT query
            $sql .= $key . ", ";

            // append column value in VALUES clause
            $values .= "'" . $input . "', ";
        }
        
        // remove last comma and add ) at the end in INSERT column statement
        $sql = rtrim($sql, ", ");
        $sql .= ")";

        // remove last comma and add ) at the end in VALUES clause
        $values = rtrim($values, ", ");
        $values .= ")";

        // appending both variables to become the final query
        $final_sql = $sql . $values;
        
        // executing the query
        mysqli_query($conn, $final_sql);
    }
?>

<form action="add-row.php?name=<?php echo $name; ?>" method="post">

    <?php
        $result = mysqli_query($conn, "SHOW COLUMNS FROM " . $name);
        while ($row = mysqli_fetch_object($result)):
            if ($row->Field == "id")
            {
                continue;
            }
    ?>
        <p>
            <input name="<?php echo $row->Field; ?>" type="text">
        </p>
    <?php endwhile; ?>
    
    <button name="submit_add_row" type="submit">
        Create row
    </button>
</form>

This will create a form with all the columns of table except ID. When the form is submitted, all the data will be sent to the page itself. The PHP code to run the query is explained on each step.

Delete a row

You are working on your client’s project via FTP, you added some test data in the database to test your functionality. When you fully done your testing and time to make the site live, you have to delete those test records. So we have already created a button in the edit row section which asks for confirmation before deleting that row. Now we are just going to create a file named delete-row.php. It will be receiving name of table from where the row needs to be deleted along with the ID of row. This will help to uniquely identify the row and delete it.

<?php
    
    // delete-row.php
    // make sure to connect with database

    if (isset($_GET["name"]) && isset($_GET["id"]))
    {
        $name = $_GET["name"];
        $id = $_GET["id"];
        
        mysqli_query($conn, "DELETE FROM " . $name . " WHERE id = " . $id);
    }
?>

First it is checking if the URL contains the name and id parameters, then it is running a simple MySQL query to delete that row from selected table in the database. If you re-open the tables.php file and browse the table, you will no longer see that row.

Drop a table

Dropping a table is almost similar to dropping the column except for a minor change in the query. We already created a button to drop a table in the “view all tables” section, when clicked it will ask for a confirmation, if confirmed then it will drop the table from database. Create a file named delete-table.php and paste the following code in it:

<?php

    // delete-table.php
    // make sure to connect with database

    if (isset($_GET["name"]))
    {
        $name = $_GET["name"];
        mysqli_query($conn, "DROP TABLE " . $name);
    }
?>

It simply connects with database, check if the URL has name parameter and drop the table using MySQL DROP TABLE statement.

Search

In your edit-table.php and view-table.php where you are seeing all the data in the selected table, you might also want to have a search functionality. We will create a simple form in both these files. When submit we will modify our query in such a way that it will run the normal query when the page loads. And run our new code when the search form is submitted.

<form method="POST" action="edit-table.php?name=<?php echo $name; ?>">
    <p>
        <input name="search" value="<?php echo isset($_POST['search']) ? $_POST['search'] : ''; ?>" type="text" required>
    </p>

    <button name="submit_search" type="submit">
        Search
    </button>
</form>

This will create a form with a POST method and action attribute will be edit-table.php. In case of view-table.php you just need to change it to view-table.php. A search input field is created and it will have the value when the form is submitted. Otherwise, this field will be empty. And finally a submit button.

Now you need to change your code in edit-table.php where you are fetching all rows from table and using LIMIT clause.

// check if the search form is submitted
if (isset($_POST["submit_search"]))
{
    // initialize query
    $sql_search = "SELECT * FROM " . $name . " WHERE ";
    
    // array to save all columns
    $sql_search_fields = array();

    // get all columns of selected table
    $rs = mysqli_query($conn, "SHOW COLUMNS FROM " . $name);
    
    // loop through each column
    while($r = mysqli_fetch_object($rs))
    {
        // LIKE clause will search for any occurrence of searched text
        array_push($sql_search_fields, $r->Field . " LIKE '%" . $_POST["search"] . "%' ");
    }

    // implode will join all the array elements with OR clause
    $sql_search .= implode(" OR ", $sql_search_fields);

    // executing the query
    $result = mysqli_query($conn, $sql_search);
}
else
{
    // if the search form is not submitted then display all data
    $result = mysqli_query($conn, "SELECT * FROM " . $name . " LIMIT " . $start_from . ", " . $record_per_page);
}

Your previous query remains same, not change in that. First, we are checking if the search form is submitted. If not then we are displaying data as we were doing before (using pagination). Save the file via FTP and refresh, then you will be able to view the database along with search. All the code for search functionality is explained using comments on each line. If you still having difficulty in understanding the code, please do mention it in the comments section below.

Conclusion

So that’s how you can view data in the database with only FTP access. If you have more ideas about this, please do let us know.

[wpdm_package id=’835′]