I was looking for a PHP script that I can easily configure (within seconds) and could tell me if the mysql connection was successful and show me the available tables. This is very easy via SSH but I was looking for a script in PHP, With little help some stack overflow & github, I came up with the following code that you can easily upload to your server and get it going.
This will show if the connection is successful and also provide you with the list of tables in your database.
Configure your DB connection in the first 4 lines and take it from there:
<?php
$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'testdb';
$link = mysql_connect("$hostname", "$username", "$password");
if (!$link) {
echo "<p>Could not connect to the server '" . $hostname . "'</p>\n";
echo mysql_error();
}else{
echo "<p>Successfully connected to the server '" . $hostname . "'</p>\n";
}
if ($link && !$database) {
echo "<p>No database name was given. Available databases:</p>\n";
$db_list = mysql_list_dbs($link);
echo "<pre>\n";
while ($row = mysql_fetch_array($db_list)) {
echo $row['Database'] . "\n";
}
echo "</pre>\n";
}
if ($database) {
$dbcheck = mysql_select_db("$database");
if (!$dbcheck) {
echo mysql_error();
}else{
echo "<p>Successfully connected to the database '" . $database . "'</p>\n";
// Check tables
$sql = "SHOW TABLES FROM `$database`";
$result = mysql_query($sql);
if (mysql_num_rows($result) > 0) {
echo "<p>Available tables:</p>\n";
echo "<pre>\n";
while ($row = mysql_fetch_row($result)) {
echo "{$row[0]}\n";
}
echo "</pre>\n";
} else {
echo "<p>The database '" . $database . "' contains no tables.</p>\n";
echo mysql_error();
}
}
}
?>
It is also worthwhile to note here that databases often require some optimization tricks to keep its performance on the high. So, it’s a good practice to keep a regular check on your database, getting known about its flaws and using the little MySQL tuning tips to keep the performance on the track.
