Adminer is a free GUI tool that can you can easily install on a PHP web server. Adminer allows you to easily connect to your MySQL instance, create databases/tables/indexes/rows and backup/import databases and much more.
You can read my other posts on Useful Linux Terminal Commands and Useful OSX Terminal Commands.
I used to use phpMyAdmin to manage MySQL databases on AWS, Digital Ocean and Vultr but switched to Adminer due to forgotten issues. You can always manage MySQL via command line but that is quite boring.
The below screenshots were taken on my local Development Mac Laptop (with optional OSX Apache SSL Setup (that reports “Not Secure” (but it is good enough to use locally)). I prefer to code in SSL and warn when SSL is not detected.
Downloading and Installing Adminer
Navigate to https://www.adminer.org/ and click Download.
Click English only (.php file)
Save the Adminder for MySQL (.php) file to your web server and give it a random name and put in a folder also with a random name (I use https://www.grc.com/passwords.htm to generate strong password).
Tip: Uploading this file to a live serve offers hackers and unauthorized people potential access to your MySQL server. I would remove this file from live serves when you are not using it not to be sure.
Tip: Read my guide here on setting up NGINX, MySQL and PHP here. Basically, I did this to setup MySQL on Ubuntu 16.04.
TIP: Ensure MySQL is secure and has a good root password, also consider setting up Ubuntu Firewalls and Securing Ubuntu. Also, ensure the Server is patched and does not have exploits like Spectre and meltdown.
Now you can access your Admirer php file on your Web Server (hopefully with an obfuscated name).
Login to Adminer with your MySQL root password.
Click Create databaase
Give the database a name and choose the character coding standard (e.g UTF8 general ci). Different standards have different performance impacts too.
Now that you have a database you can create a table.
Consider adding an auto-incrementing ID and say a Key and Value varchar column.
When the table is created you can add a row to the table.
I created one with a “TestKey” and “TestValue” row.
The row was inserted.
The final thing to do is add a database user that code can connect to the database with. Click Privileges.
Click Create user
Tick All privileges and click Save
Now the user is added to the database
Let’s create a PHP file and talk to the database. Let’s use parameterized queries
<?php
date_default_timezone_set('Australia/Sydney');
echo "Last modified: " . date ("F d Y H:i:s.", getlastmod()) . "<br /><br />";
// Turn on if you need to see errors
// error_reporting(E_ALL);
// ini_set('display_errors', 0);
$dbhost = '127.0.0.1';
$dbname = 'dbtest';
$dbusername = 'dbtestuser';
$dbpassword = '*****************************************'';
$con = mysqli_connect($dbhost, $dbusername, $dbpassword, $dbname);
// Turn on debug stuff if you need it
// echo var_dump($con);
// printf(" - Error: %s.n", $stmt->error);
if($con->connect_errno > 0){
printf(" - Error: %s.n", $stmt->error);
die("Error: Unable to connect to MySQL");
} else {
echo "Charset set to utf8<br />";
mysqli_set_charset($con,"utf8");
}
if (!$con) {
echo "Error: Unable to connect to MySQL (E002)" . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
} else {
echo "Database Connection OK<br />";
echo " Success: A proper connection to MySQL was made! The $dbname database is great." . PHP_EOL . "<br />";
echo " - Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />";
echo " - Server Info: '" . mysqli_get_server_info($con) . "'<br />";
echo " - Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />";
echo " - Server Version: " . mysqli_get_server_version($con) . "<br />";
//echo " - Server Connection Stats: " . print_r(vmysqli_get_connection_stats($con)) . "<br />";
echo " - Client Version: " . mysqli_get_client_version($con) . "<br />";
echo " - Client Info: '" . mysqli_get_client_info() . "'<br />";
echo "Ready to Query the database '$dbname'.<br />";
// Input Var's that are parameterized/bound into the query statement
$in_key = mysqli_real_escape_string($con, 'TestKey');
// Output Var's that the query fills after querying the database
// These variables will be filled with data from the current returned row
$out_id = "";
$out_key = "";
$out_value = "";
echo "1. About to query the database: '$dbname'<br />";
$stmt = mysqli_stmt_init($con);
$sql = "SELECT testid, testkey, testvalue FROM tbtest WHERE testkey = ?";
echo "SQL: $sql (In = $in_key)<br /";
if (mysqli_stmt_prepare($stmt, $sql)) {
echo "2. Query Returned<br />";
/*
Type specification chars
Character Description
i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets
*/
mysqli_stmt_bind_param($stmt, 's', $in_key);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $out_id, $out_key, $out_value);
mysqli_stmt_fetch($stmt);
// Do something with the 1st returned row
echo " - Row: ID: $out_id, KEY: $out_key, VAL: $out_value <br />";//
// Do we have more rows to process
while($stmt->fetch()) {
// Output returned values
echo " - Row: ID: $out_id, KEY: $out_key, VAL: $out_value <br />";//
}
mysqli_stmt_close($stmt);
echo "Done<br />";
} else {
echo "3. Error Querying<br/>";
printf(" - Error: %s.n", $stmt->error);
}
}
?>
Result
If you don’t have a server check out my guides on AWS, Digital Ocean and Vultr.
Happy coding and I hope this helps someone.
Donate and make this blog better
Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]
Revision History
v1.0 Initial Version
