A PHP script for testing a MySQL database connection

Security Note:
As the commenter Bruno Ng points out, submitting information via the form fields when using this script sends your database connection info in plain text, and that’s a bad thing.

Therefore, the form field method should only be used for testing local databases.

If you need to test production databases, your database connection info should be hard coded in the PHP script (which isn’t hard to do – look around lines 72-75).

A simple page for testing and troubleshooting a connection to a MySQL database. The PHP script will test the server address, username and password. If the database field is left empty, it will return a list of available databases. Testing a specific database is optional, but if a database name is supplied, it will return a list of the tables in that database (if any exist).

Due to problems with the Syntax Highlighter plugin producing invalid code when copying and pasting, here is a link to the code in plain text. The code below is just for reference.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>MySQL Connection Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
#wrapper {
	width: 600px;
	margin: 20px auto 0;
	font: 1.2em Verdana, Arial, sans-serif;
}
input {
	font-size: 1em;
}
#submit {
	padding: 4px 8px;
}
</style>
</head>

<body>

<div id="wrapper">

<?php
	$action = htmlspecialchars($_GET['action'], ENT_QUOTES);
?>

<?php if (!$action) { ?>

	<h1>MySQL connection test</h1>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>?action=test" id="mail" method="post">

	<table cellpadding="2">
		<tr>
			<td>Hostname</td>
			<td><input type="text" name="hostname" id="hostname" value="" size="30" tabindex="1" /></td>
			<td>(usually "localhost")</td>
		</tr>
		<tr>
			<td>Username</td>
			<td><input type="text" name="username" id="username" value="" size="30" tabindex="2" /></td>
			<td></td>
		</tr>
		<tr>
			<td>Password</td>
			<td><input type="text" name="password" id="password" value="" size="30" tabindex="3" /></td>
			<td></td>
		</tr>
		<tr>
			<td>Database</td>
			<td><input type="text" name="database" id="database" value="" size="30" tabindex="4" /></td>
			<td>(optional)</td>
		</tr>
		<tr>
			<td></td>
			<td><input type="submit" id="submit" value="Test Connection" tabindex="5" /></td>
			<td></td>
		</tr>
	</table>

</form>

<?php } ?>

<?php if ($action == "test") {

// The variables have not been adequately sanitized to protect against SQL Injection attacks: http://us3.php.net/mysql_real_escape_string

	$hostname = trim($_POST['hostname']);
	$username = trim($_POST['username']);
	$password = trim($_POST['password']);
	$database = trim($_POST['database']);

	$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";
//			printf("MySQL client info: %s\n", mysql_get_client_info());
//			printf("MySQL host info: %s\n", mysql_get_host_info());
//			printf("MySQL server version: %s\n", mysql_get_server_info());
//			printf("MySQL protocol version: %s\n", mysql_get_proto_info());
		}
	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();
			}
		}
	}
}
?>

</div><!-- end #wrapper -->
</body>
</html>

58 thoughts on “A PHP script for testing a MySQL database connection

  1. blogdar

    Hey i try your codes and its worked!

    I think this is best easy way to try mysql status for newbies on PHP coding, if i am wrong let me know πŸ˜‰

  2. David2

    I am new to this and this script is nice, but the “Test Connection” returns an empty page in my case. Any ideas on why or what to check?

  3. mauricio

    yes! nice work and nice that you shared it. This script is a great tool not only for what is does but also, for a beginner php programmer like myself, it helps further understanding of the langauge constructs.

  4. philip

    Fantastic script, it’s proved to one of my clients that the fault is with their hosting company’s setup of the mysql database, not my script.
    Can’t thank you enough, 10/10 .

  5. Pingback: Test MySQL Connection | Sellu.net

  6. chris

    Is this supposed to be saved as HTML or PHP? When I save it as PHP, it gives me 500 Internal Server Error, but when I save it as HTML, there is a bunch of code under the button displayed on the page…

  7. Jarrod

    I found this script very useful when troubleshooting my db connection string. Nice work.

    While using this script I did run into a problem with databases that have a “-” in the name. For example, “dbname-one”. Using backticks in the sql query resolved it.
    $sql = "SHOW TABLES FROM `$database`";
    It’s a very minor correction, but might ease the frustration of someone who encounters it.
    -Jarrod

  8. marinemerchant

    Thanks a bunch, helped me realized I didn’t put in the correct username in my wp-config file. My host puts a prefix on any mysql username I create.

  9. Pingback: ./ » Blog Archive » musings with LAMP part1

  10. Ralph

    I get the same garbage as Chris got back in June!
    Did he ever get a answer?
    Anybody know what is going on and why we are seeing code?

  11. Jesse Rijks

    Man, I can’t make up my mind upon whether you’re awesome or awesome! Thank you SO very much!

    I can now finally move on (didn’t know what I was doing wrong all the time when coding my PHP pages) and try to make a login page (which is a pain in the ass for a novice like me).

    Jarrod, thanx a lot for the add on!

  12. Bruno Ng

    I hope whoever is using this code is either running on a local webserver or running on an https (secure) protocol. Otherwise, that’s the most stupid and dangerous script to use. Submitting on a non-secure protocol means everything is submitted in PLAIN TEXT, i.e anyone can SEE what you’re submitting, and access to a Database is GOLD for any hacker!

    Basically, the tool presented here is OK at most, but values to $hostname, $username, $password, and $database should be entered (hardcoded) in the code and not submitted if you’re not using https.

    For those of you having trouble to make this form work, that’s probably because you have register globals to OFF, which is very good actually. You basically need to change $action to $_GET[‘action’]. For those who have notice reporting to ON, then you should also use if isset($_GET[‘action’])

    Those who have register globals to ON are running their server with a big opened back door (a big security hole)!

    I am aware that probably most of you who used that script are novice and beginners, and you might feel I’m coming down too hard on you, but why not do it right since the beginning?

  13. Sam soramo

    dear all!

    i got some problems this code does not work with me. please help me, i use dreamweaver cs3 and i paste your code then it show me so messy code and echo. i can not test it. i use phpmyadmin v2.5.7. please tell me the process and how to use it..

    Thank you! Smiling From Cambodia

  14. Dave

    I am seeing the code below the “Test Connection’ button as well as the other two individuals did. I don’t see the answer to this posted on the thread. Anyone know why this is happening? Using Firefox 3.6.10. Same thing happens with current versions of Chrome and Opera. IE8 just shows a blank screen.

  15. Bert

    WampServer 2.0 has installed the following items:

    Apache 2.2.11
    MySQL 5.1.36
    PHP 5.3.0
    PhpMyAdmin 3.2.0.1

    Then I used the script for testing a MySQL database connection and got the following error:

    parse error online 27 of the “testphp.php script”

    Β Β Β Β $action = htmlspecialchars($_GET['action'], ENT_QUOTES);

    What to do?

    Thanks

  16. Pingback: musings with LAMP part1

  17. RayH

    Hi I fell upon this and thought great, just what I need! Sadly there is no way that this code as presented here COULD EVER WORK or have worked. I’m sorry to say this but after spending 7 hours struggling to work out why MOST of the php displays when the page is rendered and after several people far better than me at php have tried it too and received the same nonsence and after running the code through several php editors with debugging enables I know that I have reproduced your code faithfully, without error, syntax error just as you presented it. Every “” is in the right place etc etc etc. I also notice that a number of other people experienced the same thing. SAdly you didn’t bother to respond to then. So, I conclude please, either recheck your code and prove that it does in deed work or withdraw it from the web. Nothing is worse that code presented as a working piece when it isn’t.

  18. ardamis Post author

    RayH, the code is fine. It looks like something gets corrupted when the code is copied from the web page and pasted into a new document. I’m blaming the Syntax Highlighter plugin.

    Anyway, I’ve uploaded a plain text version of the plugin that I’ve confirmed to work correctly.

  19. ardamis Post author

    RayH, after looking at the page that your server is outputting, it appears that the problem is with your environment. It’s treating any > symbol in the PHP as the end of an HTML comment, and then outputting the code that follows as HTML.

    Sometimes, the code is good, but the environment is the problem – be it different versions of PHP or a different php.ini configuration or what-have-you.

  20. RayH

    Hi I want to thank Oliver for the hundred and 10 miles he walked to assist me in getting the above example to function. While the problem was never fully resolved, it may well be something to do with wqamp, php.ini and windows? Oliver reallt did try to help and I thank him for that. Good luck

  21. RayH

    Hi All,. If anyone experiences the same problem as mine, ie the code was partially displayed on the page and of course it wouldn’t run, then please check that the php page was created without hidden editor characters. After sterling work by Oliver to help with the problem I managed to resolve the issue by deleting the page, creating a new one using a different editor and page type settings and then placing the code into that. Wham it worked brilliantly. Just to prove the point to myself, I then copied the code in a HTML page to see what would happen and the problems experienced were repeated there so obviously my server was not reading the page as a php page. Creating a new page sorted the problem. Thanks Oliver.

  22. AC

    This script worked great in debugging my simple issue – minor error in database name. Thank you for posting this!!!

  23. Michael Christian

    You freakin rock man. I used this on a japanese server configuring it. the entire host site is in japanese. you just saved my day man!!!!!!!!!!

    thanks

  24. mss.janey

    such an excellent piece of code!
    easy, stylish, light and above all, functional – just what i needed right now to do some quick testing on multiple accounts!
    thnx!

  25. suggestion

    for more info on failed connection attempts, change this

    echo “Could not connect to the server ‘” . $hostname . “‘n”;

    to this

    echo “Could not connect to the server ‘” . $hostname . “‘. Error message returned was ” . mysql_error() . ” n”;

  26. rully

    how it work,? Is the file upload to /public_html,? or how,? explaint as detail, please.
    I am newbie. πŸ™‚

  27. Pingback: SQL db check - The UNIX and Linux Forums

  28. polosko

    MySQL connection test
    Hostname (usually “localhost”)
    Username
    Password
    Database (optional)

    Could not connect to the server ‘” . $hostname . “‘n”; echo mysql_error(); }else{ echo ”

    Successfully connected to the server ‘” . $hostname . “‘
    n”; // printf(“MySQL client info: %sn”, mysql_get_client_info()); // printf(“MySQL host info: %sn”, mysql_get_host_info()); // printf(“MySQL server version: %sn”, mysql_get_server_info()); // printf(“MySQL protocol version: %sn”, mysql_get_proto_info()); } if ($link && !$database) { echo ”

    No database name was given. Available databases:
    n”; $db_list = mysql_list_dbs($link); echo ”

    n”;
    while ($row = mysql_fetch_array($db_list)) {
    echo $row[‘Database’] . “n”;
    }
    echo ”

    n”; } if ($database) { $dbcheck = mysql_select_db(“$database”); if (!$dbcheck) { echo mysql_error(); }else{ echo ”

    Successfully connected to the database ‘” . $database . “‘
    n”; // Check tables $sql = “SHOW TABLES FROM `$database`”; $result = mysql_query($sql); if (mysql_num_rows($result) > 0) { echo ”

    Available tables:
    n”; echo ”

    n”;
    while ($row = mysql_fetch_row($result)) {
    echo “{$row[0]}n”;
    }
    echo ”

    n”; } else { echo ”

    The database ‘” . $database . “‘ contains no tables.
    n”; echo mysql_error(); } } } } ?>

  29. polosko

    i get the following error sir……the capitalized words are explainers..START CODE
    MySQL connection test
    Hostname (usually “localhost”)
    Username INPUT
    Password INPUT
    Database (optional)

    Could not connect to the server ‘” . $hostname . “‘n”; echo mysql_error(); }else{ echo ”

    Successfully connected to the server ‘” . $hostname . “‘
    n”; // printf(“MySQL client info: %sn”, mysql_get_client_info()); // printf(“MySQL host info: %sn”, mysql_get_host_info()); // printf(“MySQL server version: %sn”, mysql_get_server_info()); // printf(“MySQL protocol version: %sn”, mysql_get_proto_info()); } if ($link && !$database) { echo ”

    No database name was given. Available databases:
    n”; $db_list = mysql_list_dbs($link); echo ”

    n”;
    while ($row = mysql_fetch_array($db_list)) {
    echo $row[‘Database’] . “n”;
    }
    echo ”

    n”; } if ($database) { $dbcheck = mysql_select_db(“$database”); if (!$dbcheck) { echo mysql_error(); }else{ echo ”

    Successfully connected to the database ‘” . $database . “‘
    n”; // Check tables $sql = “SHOW TABLES FROM `$database`”; $result = mysql_query($sql); if (mysql_num_rows($result) > 0) { echo ”

    Available tables:
    n”; echo ”

    n”;
    while ($row = mysql_fetch_row($result)) {
    echo “{$row[0]}n”;
    }
    echo ”

    n”; } else { echo ”

    The database ‘” . $database . “‘ contains no tables.
    n”; echo mysql_error(); } } } } ?>
    END CODE

  30. Neo

    This is still helping in 2015. By the way, the plain text link doesn’t work anymore. It gives a 403 and additionally a 404. I managed to copy off the text though.

  31. Santosh

    Thank you. I worked for like weeks to setup a simple connection. This simple code saved my day.
    Thank You Sir. πŸ™‚

  32. Mike

    Seriously, I see that this clean and excellent piece of PHP has been helping people for over five years and it really helped me just now! I struggled for two days to create an ODBC connection on a new web hosted site. I copied your example, pasted it into notepad, deleted all the line markers and it ran perfectly the first time–proving that my DB connection was good and the data was fine. Thanks to you and everyone who share their creations.

Leave a Reply

Your email address will not be published. Required fields are marked *