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′]
