Test connection to mysql database php

M165437 / db-connect-test.php

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

# Fill our vars and run on cli

# $ php -f db-connect-test.php

$ dbname = ‘name’ ;

$ dbuser = ‘user’ ;

$ dbpass = ‘pass’ ;

$ dbhost = ‘host’ ;

$ link = mysqli_connect( $ dbhost , $ dbuser , $ dbpass ) or die(» Unable to Connect to ‘ $ dbhost ‘ «);

mysqli_select_db( $ link , $ dbname ) or die(» Could not open the db ‘ $ dbname ‘ «);

$ test_query = » SHOW TABLES FROM $ dbname «;

$ result = mysqli_query( $ link , $ test_query );

$ tblCnt = 0 ;

while ( $ tbl = mysqli_fetch_array( $ result ))

$ tblCnt ++;

#echo $tbl[0].» \n»;

>

if (! $ tblCnt )

echo » There are no tables \n»;

> else

echo » There are $ tblCnt tables \n»;

>

Hi! Having an issue with a script (stopped working for some reason after many years of running just fine) and trying to test database and server connection. I uploaded the script and it appears to be working, except, it is saying there are no tables, when in fact there are many tables in the database. Does anyone know what the issue may be or able to point me in the right direction?

Thank you for the updated version. This is very handy indeed.

Having trouble understanding how to run the script.
I first of all moved it as «index.php» into the root apache directory. . not sure if there’s an ownership issue there?
Online the browser failed to respond, even though it’s checked out with the basic script.

I then took the command as you’d hashed out in line 3, and as per below tried to run as root in the var/www directory:

root@ip-172-31-33-139:/home/ubuntu# cd /var/www
root@ip-172-31-33-139:/var/www# ls -l
total 12
-rw-rw-r— 1 ubuntu ubuntu 678 Sep 2 19:20 db-connect-test.php
drwxrwxr-x 2 www-data www-data 4096 Sep 2 16:35 html
-rw-rw-r— 1 ubuntu ubuntu 662 Sep 2 19:13 index.php
root@ip-172-31-33-139:/var/www# php -f db-connect-test.php
PHP Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /var/www/db-connect-test.php:9
Stack trace:
#0
thrown in /var/www/db-connect-test.php on line 9
root@ip-172-31-33-139:/var/www# root@ip-172-31-33-139:/var/www# php -f db-connect-test.php
bash: root@ip-172-31-33-139:/var/www#: No such file or directory
root@ip-172-31-33-139:/var/www# PHP Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /var/www/db-connect-test.php:9
bash: syntax error near unexpected token `(‘

I have set the variables with the correct RDS host and login credentials, which checkout from this machine using the remote mysql access, which works fine, logging in and returning the schema structure with the show databases; mysql command.

In your second line hashed out statement, you’re suggesting running this under CLI. Could you please provide the syntax?

Also, as it’s my goal here to understand the general rules / syntax for evaluating php scripts from this micro staging server I»m using, please do comment if what I’m trying to do is possible (ie invoking a php script in the root apache directory as «index.php» to get a browser response linked to the server’s url).

Источник

How to Test PHP MySQL Database Connection Using Script

MySQL is a popular database management system while PHP is a server-side scripting language suitable for web development; together with Apache or Nginx HTTP servers, are the different components of the LAMP (Linux Apache MySQL/MariaDB PHP) or LEMP (Linux Nginx MySQL/MariaDB PHP) stack receptively.

If you are a web developer then you might have installed these software packages or used them to setup a local web server on your system. In order for your website or web application to store data, it needs a database such as MySQL/MariaDB.

For the web application users to interact with the information stored in the database, there must be a program running on the server to pick requests from client and pass to the server.

In this guide, we will explain how to test a MySQL database connection using a PHP file. Before moving further, make sure you must have LAMP or LEMP installed on the system, if not follow these tutorials to setup.

Setup LAMP Stack on Linux Systems

Setup LEMP Stack on Linux Systems

Quick MySQL Database Connection Test Using PHP Script

To do a quick PHP MySQL DB connection test, we will use a following handy script as file db-connect-test.php .

Now change the database name, database user and user password as well as the host to your local values.

$dbname = 'name'; $dbuser = 'user'; $dbpass = 'pass'; $dbhost = 'host';

Save and close the file. Now run it as follows; it should print the total number of tables in the specified database.

You can cross check manually by connecting to the database server and listing the total number of tables in the particular database.

You may also like to check out these following related articles.

Do you have any other way or script to test a MySQL DB connection? If yes, then use the feedback form below to do that.

Источник

How to test MySQL connection in PHP and Laravel? [duplicate]

I’m making a PHP application installer (something like WordPress installation script) and I need to check mysql connection using host name, username, password and database provided by user during installation. I’m using this code as a Laravel controller method to test connection:

public function TestDatabaseConnection() < try < $database_host = Config::get('config.database_host'); $database_name = Config::get('config.database_name'); $database_user = Config::get('config.database_user'); $database_password = Config::get('config.database_password'); $connection = mysqli_connect($database_host,$database_user,$database_password,$database_name); if (mysqli_connect_errno())< return false; >else < return true; >> catch (Exception $e) < return false; >> 

This code doesn’t seem to properly test the connection. Function return value (true/false) doesn’t depend whether user supplies db data at all, or if db data is correct/incorrect.. Fils /app/config/config.php contains the following array:

 'localhost', 'database_name' => 'dbasename', 'database_user' => 'dbuser', 'database_password' => 'pass'); 

and it’s being updated via form during installation process. Is there any way to modify this code or maybe you have some other code suggestions?

I posted a full answer, but why not use Phing as an application installer? No need to reinvent the wheel on this kind of stuff.

3 Answers 3

How to test MySQL connection in PHP and Laravel?

But then you are setting up a standard PHP MySQLi connection like this:

$connection = mysqli_connect($database_host,$database_user,$database_password,$database_name); 

Why would you do that? The whole purpose of using a framework is to work within the framework. And something that encompasses these two basic systems concepts:

Doing those things is something that pretty much every capable—and widely adopted—programming framework should be able to handle within it’s own structure & using it’s own methods.

So that said, looking at the Laravel documentation on “Basic Database Usage” shows the following. This is placed in your DB configuration file located in app/config/database.php. :

'mysql' => array( 'read' => array( 'host' => '192.168.1.1', ), 'write' => array( 'host' => '196.168.1.2' ), 'driver' => 'mysql', 'database' => 'database', 'username' => 'root', 'password' => '', 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), 

The example has two distinct DB connections: One for read and the other for write , but that is not how most DB connections for simple projects work. So you can set this instead also using your settings:

'mysql' => array( 'host' => Config::get('config.database_host'), 'driver' => 'mysql', 'database' => Config::get('config.database_name'), 'username' => Config::get('config.database_user'), 'password' => Config::get('config.database_password'), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ), 

Then to test that connection, you would just do this:

if(DB::connection()->getDatabaseName()) < echo "Yes! successfully connected to the DB: " . DB::connection()->getDatabaseName(); > 

But that said you are also saying:

I'm making a PHP application installer… 

Why reinvent the wheel when PHP build systems such as Phing exist?

Источник

PDO Connection Test

I am writing an installer for one of my apps and I would like to be able to test some default database settings. Is this possible using PDO to test valid and invalid database connections? I have the following code:

try < $dbh = new pdo('mysql:host=127.0.0.1:3308;dbname=axpdb','admin','1234'); die(json_encode(array('outcome' =>true))); >catch(PDOException $ex) < die(json_encode(array( 'outcome' =>false, 'message' => 'Unable to connect' ))); > 

The problem I am having is that the script trys to connect until the script execution time of 60 seconds runs out instead of saying it cannot connect to the db. Thanks

Lower case pdo works the same as PDO, if I put correct details in the script works as expected but i’m trying to detect invalid settings

If i add it here: $dbh = new PDO(‘mysql:host=127.0.0.1;port=3308;dbname=axpdb’,’admin’,’1234′, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); it makes no difference and as it is that line that errors i cannot do $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); below

4 Answers 4

you need to set the error mode when connection to the database:

try < $dbh = new pdo( 'mysql:host=127.0.0.1:3308;dbname=axpdb', 'admin', '1234', array(PDO::ATTR_ERRMODE =>PDO::ERRMODE_EXCEPTION)); die(json_encode(array('outcome' => true))); > catch(PDOException $ex) < die(json_encode(array('outcome' =>false, 'message' => 'Unable to connect'))); > 

for more infos see the following links:

@Themodem: Why would you need to turn display errors off? You’ve caught the exception — it’s handled.

@Mark, because the implementation of PDO::ERRMODE_EXCEPTION is broken (PHP5.3.28), and has no effect in some cases (e.g. «DB server not running» on my host). So, this solution then, unfortunately, is not enough, as Themodem noticed it in his workaround. (Hey, BTW, just put @new pdo() , don’t brute-force display_errors to off just for this!)

For example: Warning: PDO::__construct() [pdo.—construct]: [2002] No connection could be made because the target machine actively refused it. (trying to connect via tcp://localhost:3306) in . , and the code is: $this->pdo = new PDO($cfg[‘DB’], $cfg[‘DB_USER’], $cfg[‘DB_PASS’], array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_LOCAL_INFILE => true)); (Note: the exception is thrown, and cought, I put some log messages in the catch clause.)

As @Sascha Galley already mentioned you should set error mode to exception mode. However, you should also set up PDO::ATTR_TIMEOUT attribute to prevent a long time waiting for response in some cases.

Although documentation says that behavior of this attribute is driver-dependent in case of MySQL it’s a connection timeout. You won’t find anything about it documentation but here’s a short snippet from driver’s source code:

long connect_timeout = pdo_attr_lval(driver_options, PDO_ATTR_TIMEOUT, 30 TSRMLS_CC); 

As seen e.g. in the comments at this answer (but hardly anywhere else, so I made it more visible here), the «classic» PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION solution does not always work.

The implementation of PDO::ERRMODE_EXCEPTION is broken, so it seems to be «leaking» in some cases.

Warning: PDO::__construct() [pdo.—construct]: [2002] No connection could be made because the target machine actively refused it. (trying to connect via tcp://localhost:3306) in [. ] db.php on line 34

try < $this->pdo = new PDO($cfg['DB'], $cfg['DB_USER'], $cfg['DB_PASS'], array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); > catch

The exception is thrown (and cought: I can see my message).

So, as a necessary workaround, you need to also put a @ (let’s call it a «diaper operator» in this case) before new pdo(. ) to actually keep it clean.

Источник