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”

blogdar June 4, 2008 at 7:13 pm

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 😉
Cristian July 29, 2008 at 4:23 pm

Excellent tool!
David September 22, 2008 at 5:11 pm

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

Regards
David
Ben September 24, 2008 at 11:59 am

excellent tool, thanks
David2 October 30, 2008 at 1:18 pm

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?
David2 October 30, 2008 at 3:33 pm

Never mind. Figured it out. Error logs are useful. 🙂 Nice script. Thanks.
scual November 2, 2008 at 6:15 am

works fine, thanks for sharing
mauricio December 5, 2008 at 3:16 pm

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.
Neil January 29, 2009 at 1:42 am

Thanks! This is such a great resource!
Rick Billings February 28, 2009 at 6:04 am

Thank You. Excellent Tool.
philip March 9, 2009 at 2:21 pm

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 .
Sathya March 23, 2009 at 4:21 am

Thanks alot. You saved me from wating time on debugging the code.
mark April 9, 2009 at 11:58 am

thanks~
Pingback: Test MySQL Connection | Sellu.net
foolmylove May 6, 2009 at 6:02 am

Thanks This is very gooooooooooooooooooooooooood …..!!!
Daya May 12, 2009 at 7:59 pm

THANKS! the script works great..this is exactly what I was looking for!
chris June 23, 2009 at 5:46 pm

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…
Alex July 12, 2009 at 8:46 pm

Dood you are awesome – nice html/php script! Thank you! 😉
Jarrod July 24, 2009 at 4:33 pm

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
marinemerchant August 27, 2009 at 1:37 pm

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.
Swaan September 20, 2009 at 9:59 am

Thanks for this awesomeness!
Sean C Phillips October 24, 2009 at 6:41 am

Thanks. Saved this novice some serious time.
Pingback: ./ » Blog Archive » musings with LAMP part1
Ralph December 11, 2009 at 6:28 pm

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?
Tacon March 2, 2010 at 9:55 am

Worked for me just now! One BigAss Kiss from Holland for you. Thanks for sharing.
Jesse Rijks April 4, 2010 at 8:35 am

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!
Bruno Ng August 18, 2010 at 8:35 am

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?
Sam soramo September 20, 2010 at 5:37 am

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
Dave September 25, 2010 at 2:48 am

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.
Thom Winterrowd September 30, 2010 at 8:56 am

A PHP script for testing a MySQL database connection

Script returns blank page?
Bert November 4, 2010 at 5:40 am

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
Josh Benedikt November 12, 2010 at 8:56 pm

I just needed to run a quick test and this script helped out a lot. thanks 100000000000000000000000000000000000000000000000000000!
anvarri meiji February 2, 2011 at 6:37 pm

Really helped me out!
Thank you ^_^
Pingback: musings with LAMP part1
RayH May 13, 2011 at 6:58 pm

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.
ardamis Post authorMay 13, 2011 at 8:53 pm

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.
ardamis Post authorMay 14, 2011 at 10:45 am

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.
RayH May 14, 2011 at 11:31 am

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
RayH May 14, 2011 at 12:56 pm

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.
Ramnihor June 16, 2011 at 5:30 am

Really wonderfull
AC June 30, 2011 at 6:54 pm

This script worked great in debugging my simple issue – minor error in database name. Thank you for posting this!!!
Michael Christian July 17, 2011 at 3:34 pm

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
mss.janey October 6, 2011 at 3:50 am

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!
Deborah Cole November 2, 2011 at 12:02 am

Wow! A useful utility that really works!!! Thanks dude!
param December 15, 2011 at 3:20 am

very good and very easy test
Emraan December 24, 2011 at 11:04 am

Very Nice.
Itz very Helpfull,
Thank You so MUCH 🙂
Bohat Aala
suggestion January 24, 2012 at 2:03 pm

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”;
A T Smith April 23, 2012 at 8:15 pm

Thank-you. – Works well and does what was required.
David July 16, 2012 at 4:33 pm

Very useful and timely – thank you for sharing, helped this newbie out alot!
rully September 6, 2012 at 12:45 pm

how it work,? Is the file upload to /public_html,? or how,? explaint as detail, please.
I am newbie. 🙂
Pingback: SQL db check - The UNIX and Linux Forums
polosko December 14, 2012 at 12:02 pm

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(); } } } } ?>
polosko December 14, 2012 at 12:05 pm

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
Sef March 21, 2013 at 7:50 am

If you use HostGator the script will not work…
Neo May 4, 2015 at 10:48 am

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.
Santosh June 21, 2015 at 11:31 am

Thank you. I worked for like weeks to setup a simple connection. This simple code saved my day.
Thank You Sir. 🙂
Mike February 19, 2016 at 5:27 pm

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.
misa April 3, 2016 at 10:34 am

Still works! Thanks for this.

Comments are closed.