Test mysqli connection php

mattstein / db-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

/**

* Database Connection Tester

* A quick-and-dirty standalone script for checking PHP’s connection to a

* MySQL (or MariaDB) or PostgreSQL database.

*

* To use, enter the settings below and run the following from your terminal:

* «`

* php -f db-test.php

* «`

*/

$ driver = ‘mysql’ ; // ‘mysql’ or ‘pgsql’

$ database = » ;

$ username = » ;

$ password = » ;

$ host = » ;

$ port = $ driver === ‘mysql’ ? 3306 : 5432 ;

/**

* Ignore the stuff below this line.

*/

$ numTables = 0 ;

echo » ———————————————— \n»;

echo » Database Connection Test \n»;

echo » PHP «. PHP_VERSION .»\n»;

echo » DB driver: $ driver \n»;

echo » ———————————————— \n»;

if ( $ driver === ‘mysql’ )

$ connection = mysqli_connect( $ host , $ username , $ password , null , $ port )

or die(» 🚫 Unable to Connect to ‘ $ host ‘. \n\n»);

mysqli_select_db( $ connection , $ database )

or die(» 🚫 Connected but could not open db ‘ $ database ‘. \n\n»);

$ result = mysqli_query( $ connection , » SHOW TABLES FROM ` $ database ` «);

if ( $ result === false )

die(» 🚫 Couldn’t query ‘ $ database ‘. \n\n»);

>

while ( $ table = mysqli_fetch_array( $ result ))

$ numTables ++;

//echo $table[0].»\n»;

>

> elseif ( $ driver === ‘pgsql’ )

$ connection = pg_connect(» host= $ host dbname= $ database user= $ username password= $ password port= $ port «)

or die(» 🚫 Unable to Connect to ‘ $ host ‘. \n\n»);

$ result = pg_query(

$ connection ,

» SELECT table_schema || ‘.’ || table_name

FROM information_schema.tables

WHERE table_type = ‘BASE TABLE’

AND table_schema NOT IN (‘pg_catalog’, ‘information_schema’);

«

);

if ( $ result === false )

die(» 🚫 Couldn’t query ‘ $ database ‘. \n\n»);

>

while ( $ table = pg_fetch_array( $ result ))

$ numTables ++;

//echo $table[0].»\n»;

>

> else

die(» ⛔ Invalid driver ` $ driver `; must be `mysql` or `pgsql`. \n\n»);

>

if (! $ numTables )

echo » 🤷‍️ Connected but no tables found. \n\n»;

> else

echo » 👍 Connected and found $ numTables tables. \n\n»;

>

Источник

Тестовое подключение к MySQL из PHP mysqli

Иногда необходимо протестировать работу базы данных, если не удается настроить подключение через сайт или CMS, для этого можно использовать простой PHP скрипт с расширением mysqli.

Скрипт достаточно загрузить в папку вашего сайта и подставить свои значения в файле вместо USER, PASSWORD, BASENAME.

Данный фрагмент кода поможет проверить подключение к базе данных. А также может служить основой для работы с базой данных и выполнения SQL запросов.

 $mysqli = new mysqli('localhost', 'USER', 'PASSWORD', 'BASENAME'); if ($mysqli->connect_error) { die('Ошибка подключения (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); } echo '
Соединение установлено. ' . $mysqli->host_info . "
"; $result = $mysqli->query("SHOW TABLES;"); while ($row = $result->fetch_row()) { echo "
Таблица: "; } echo 'Версия MYSQL сервера: ' . $mysqli->server_info . "\n"; $mysqli->close(); ?>

Пригодился скрипт для тестирования хостинга Namecheap.

Источник

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.

Источник

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

Источник

mysqli_ping

Проверяет работоспособность соединения с сервером. Если соединение разорвано, а глобальная настройка mysqli.reconnect включена, PHP попытается автоматически переподключиться.

Замечание: Настройка php.ini mysqli.reconnect игнорируется драйвером «mysqlnd», так что автоматического переподключения не произойдёт.

Эта функция может использоваться клиентами, которые простаивают без дела долгое время, чтобы проверить, что сервер их не отключил, и переподключиться в случае необходимости.

Список параметров

Только для процедурного стиля: объект mysqli , полученный с помощью mysqli_connect() или mysqli_init() .

Возвращаемые значения

Возвращает true в случае успешного выполнения или false в случае возникновения ошибки.

Ошибки

Если уведомления об ошибках mysqli включены ( MYSQLI_REPORT_ERROR ) и запрошенная операция не удалась, выдаётся предупреждение. Если, кроме того, установлен режим MYSQLI_REPORT_STRICT , вместо этого будет выброшено исключение mysqli_sql_exception .

Примеры

Пример #1 Пример использования mysqli::ping()

$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

/* проверка соединения */
if ( $mysqli -> connect_errno ) printf ( «Не удалось подключиться: %s\n» , $mysqli -> connect_error );
exit();
>

/* проверим, жив ли сервер */
if ( $mysqli -> ping ()) printf ( «Соединение в порядке!\n» );
> else printf ( «Ошибка: %s\n» , $mysqli -> error );
>

/* закрываем соединение */
$mysqli -> close ();
?>

$link = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

/* проверка соединения */
if ( mysqli_connect_errno ()) printf ( «Не удалось подключиться: %s\n» , mysqli_connect_error ());
exit();
>

/* проверим, жив ли сервер */
if ( mysqli_ping ( $link )) printf ( «Соединение в порядке!\n» );
> else printf ( «Ошибка: %s\n» , mysqli_error ( $link ));
>

/* закрываем соединение */
mysqli_close ( $link );
?>

Результат выполнения данных примеров:

User Contributed Notes 3 notes

The behaviour about the option mysqli.reconnect is default set to Off at Debian PHP Packages. So i would recommend to update the first line description about the recommendation at the option mysqli.reconnect. (practice note ;))

As jay at grooveshark dot com very helpfully pointed out, the mysqlnd driver which is becoming pretty standard does not obey reconnect commands. If you have a DB wrapper class (which hopefully you do) you can implement your own version of ping() such as:

class db extends mysqli
private $db_host ;
private $db_user ;
private $db_pass ;
private $db_name ;
private $persistent ;

public function __construct ( $db_host , $db_user , $db_pass , $db_name , $persistent = true )
$this -> db_host = $db_host ;
$this -> db_user = $db_user ;
$this -> db_pass = $db_pass ;
$this -> db_name = $db_name ;
$this -> persistent = $persistent ;

parent :: init ();
parent :: options ( MYSQLI_OPT_CONNECT_TIMEOUT , 1 );
@ parent :: real_connect (( $this -> persistent ? ‘p:’ : » ) . $this -> db_host , $this -> db_user , $this -> db_pass , $this -> db_name );

if ( $this -> connect_errno )
die( «All DB servers down!\n» );
>

public function ping ()
@ parent :: query ( ‘SELECT LAST_INSERT_ID()’ );

if ( $this -> errno == 2006 )
$this -> __construct ( $this -> db_host , $this -> db_user , $this -> db_pass , $this -> db_name , $this -> persistent );
>
.
>

$db = new db ( DB_HOST , DB_USER , DB_PASS , DB_NAME );
// Some code that potentially takes a really long time to execute goes here
// Ping for safety to try to gracefully reconnect
$db -> ping ();
// Now we should be able to run queries again
$db -> query ( ‘SELECT LAST_INSERT_ID()’ );

?>

If you wanted you could even put «$this->ping();» at the top of db::query() to avoid any explicit reconnection calls but I wouldn’t recommend it due to the (slight) overhead of running the cheap «SELECT LAST_INSERT_ID()» query every time prior to running your real intended query. There are probably even cheaper queries to run in favor of «SELECT LAST_INSERT_ID()» but it was the first that came to mind and is cheap enough for most purposes since you shouldn’t be calling ping() a whole bunch anyway.

Источник