A PHP script for testing a MySQL database connection

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).

<!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());
		}
	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_object($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>
  • RSS
  • email
  • Twitter
  • Facebook
  • Digg
  • StumbleUpon
  • del.icio.us
  • Google Bookmarks
  • Technorati
  • LinkedIn
  • Reddit
  • MySpace
  • Slashdot
  • SphereIt
  • Sphinn
  • Mixx

25 Responses to “A PHP script for testing a MySQL database connection”

  1. blogdar says:

    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. Cristian says:

    Excellent tool!

  3. David says:

    hey really cool script …..thanks for sharing your script …!!! saves lot of time

    Regards
    David

  4. Ben says:

    excellent tool, thanks

  5. David2 says:

    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?

  6. David2 says:

    Never mind. Figured it out. Error logs are useful. :-) Nice script. Thanks.

  7. scual says:

    works fine, thanks for sharing

  8. mauricio says:

    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.

  9. Neil says:

    Thanks! This is such a great resource!

  10. Rick Billings says:

    Thank You. Excellent Tool.

  11. philip says:

    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 .

  12. Sathya says:

    Thanks alot. You saved me from wating time on debugging the code.

  13. foolmylove says:

    Thanks This is very gooooooooooooooooooooooooood …..!!!

  14. Daya says:

    THANKS! the script works great..this is exactly what I was looking for!

  15. chris says:

    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…

  16. Alex says:

    Dood you are awesome – nice html/php script! Thank you! ;)

  17. Jarrod says:

    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

  18. marinemerchant says:

    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.

  19. Swaan says:

    Thanks for this awesomeness!

  20. Sean C Phillips says:

    Thanks. Saved this novice some serious time.

  21. [...] from stormwind to darnassus’ db. But trying to check apache/mysql connectivity, no cigar. Here’s a nice php script to check for mysql connectivity on your web server. Checked the logs, nothing [...]

  22. Ralph says:

    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?

  23. Tacon says:

    Worked for me just now! One BigAss Kiss from Holland for you. Thanks for sharing.

Leave a Reply

Wrap code snippets in <code></code> tags.