Security Note:
As the commenter Bruno Ng points out, this script sends your database connection info in plain text.
It 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.
As the commenter Bruno Ng points out, this script sends your database connection info in plain text.
It 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.
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>
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 ;)
Excellent tool!
hey really cool script …..thanks for sharing your script …!!! saves lot of time
Regards
David
excellent tool, thanks
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?
Never mind. Figured it out. Error logs are useful. :-) Nice script. Thanks.
works fine, thanks for sharing
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.
Thanks! This is such a great resource!
Thank You. Excellent Tool.
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 .
Thanks alot. You saved me from wating time on debugging the code.
thanks~
[...] Visit the site at:
Thanks This is very gooooooooooooooooooooooooood …..!!!
THANKS! the script works great..this is exactly what I was looking for!
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…
Dood you are awesome – nice html/php script! Thank you! ;)
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
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.
Thanks for this awesomeness!
Thanks. Saved this novice some serious time.
[...] 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 [...]
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?
Worked for me just now! One BigAss Kiss from Holland for you. Thanks for sharing.
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!
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?
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
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.
A PHP script for testing a MySQL database connection
Script returns blank page?
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
I just needed to run a quick test and this script helped out a lot. thanks 100000000000000000000000000000000000000000000000000000!
Really helped me out!
Thank you ^_^
[...] connect 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 [...]
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.
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.
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.
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
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.
Really wonderfull
This script worked great in debugging my simple issue – minor error in database name. Thank you for posting this!!!
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
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!
Wow! A useful utility that really works!!! Thanks dude!
very good and very easy test
Very Nice.
Itz very Helpfull,
Thank You so MUCH :-)
Bohat Aala
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”;