Checking mysql connection in php

mysql_ping

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide. Alternatives to this function include:

Description

Checks whether or not the connection to the server is working. If it has gone down, an automatic reconnection is attempted. This function can be used by scripts that remain idle for a long while, to check whether or not the server has closed the connection and reconnect if necessary.

Note:

Automatic reconnection is disabled by default in versions of MySQL >= 5.0.3.

Parameters

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() had been called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Return Values

Returns true if the connection to the server MySQL server is working, otherwise false .

Examples

Example #1 A mysql_ping() example

$conn = mysql_connect ( ‘localhost’ , ‘mysqluser’ , ‘mypass’ );
$db = mysql_select_db ( ‘mydb’ );

/* Assuming this query will take a long time */
$result = mysql_query ( $sql );
if (! $result ) echo ‘Query #1 failed, exiting.’ ;
exit;
>

/* Make sure the connection is still alive, if not, try to reconnect */
if (! mysql_ping ( $conn )) echo ‘Lost connection, exiting after query #1’ ;
exit;
>
mysql_free_result ( $result );

/* So the connection is still alive, let’s run another query */
$result2 = mysql_query ( $sql2 );
?>

See Also

User Contributed Notes 7 notes

It should be noted that mysql_ping() seems to reset the error message on the server.
I used it to check whether the connection was still alive before reading the error message via mysql_error() and it always returned an empty string. Upon removing the connection check everything worked.

mysql_ping() is really helpful when you have this annoying error:

MYSQL Error 2006 Server has gone away

For CI users:
In 1.7.2 version of codeigniter, there is a function

that uses mysql_ping() to reestablish the timed out connection.

This function is specially useful when developing social media sites that uses hundreds of connections to the db such asinserting or selecting.

When using the mysql_ping command under php 5.1.2 and mysql 5.0, I was having problems with the auto-reconnect «feature», mainly that when the connection was severed, a mysql_ping would not automatically re-establish the connection to the database.

The connection to the DB is dropped when the time without a query excedes the wait_timeout value in my.cnf. You can check your wait_timeout by running the query «SHOW VARIABLES;»

If you’re having problems auto-reconnecting when the connection is dropped, use this code:

$conn = mysql_connect ( ‘localhost’ , ‘user’ , ‘pass’ );
mysql_select_db ( ‘db’ , $conn );

if (! mysql_ping ( $conn )) //here is the major trick, you have to close the connection (even though its not currently working) for it to recreate properly.
mysql_close ( $conn );
$conn = mysql_connect ( ‘localhost’ , ‘user’ , ‘pass’ );
mysql_select_db ( ‘db’ , $conn );
>

//run queries knowing that your connection is alive.

When checking if a $resource works.
be prepared that mysql_ping returns NULL as long as $resource is no correct mysql resource.
$resource = NULL ;
var_dump = @ mysql_ping ( $resource );
# showing NULL
?>
This could be used to decide of a current $resource is a mysql or a mysqli connection when nothing else is available to do that.

This function *does not* attempt to reconnect at this time. It only tells you whether or not you currently *are* connected.

To actually reconnect, you will have to implement this yourself in a wrapper class.

Is important to remember that if your first connection to mysql don’t works, mysql_ping will always return true! So, if you want to check if mysql is connected, first of all you must check if mysql_connect do not returns false and then you can begin to check mysql_ping.

If you get ‘error 2006: MySQL server has gone away’ messages when running (really) long scripts, mysql_ping will help detecting the loss of the db-connection. This can happen, when ‘wait timeout’ is reached (MySQL default is 8 hours).

• MySQL Functions
  • mysql_​affected_​rows
  • mysql_​client_​encoding
  • mysql_​close
  • mysql_​connect
  • mysql_​create_​db
  • mysql_​data_​seek
  • mysql_​db_​name
  • mysql_​db_​query
  • mysql_​drop_​db
  • mysql_​errno
  • mysql_​error
  • mysql_​escape_​string
  • mysql_​fetch_​array
  • mysql_​fetch_​assoc
  • mysql_​fetch_​field
  • mysql_​fetch_​lengths
  • mysql_​fetch_​object
  • mysql_​fetch_​row
  • mysql_​field_​flags
  • mysql_​field_​len
  • mysql_​field_​name
  • mysql_​field_​seek
  • mysql_​field_​table
  • mysql_​field_​type
  • mysql_​free_​result
  • mysql_​get_​client_​info
  • mysql_​get_​host_​info
  • mysql_​get_​proto_​info
  • mysql_​get_​server_​info
  • mysql_​info
  • mysql_​insert_​id
  • mysql_​list_​dbs
  • mysql_​list_​fields
  • mysql_​list_​processes
  • mysql_​list_​tables
  • mysql_​num_​fields
  • mysql_​num_​rows
  • mysql_​pconnect
  • mysql_​ping
  • mysql_​query
  • mysql_​real_​escape_​string
  • mysql_​result
  • mysql_​select_​db
  • mysql_​set_​charset
  • mysql_​stat
  • mysql_​tablename
  • mysql_​thread_​id
  • mysql_​unbuffered_​query

  Источник

  PHP Connect to MySQL

  PHP 5 and later can work with a MySQL database using:

  • MySQLi extension (the «i» stands for improved)
  • PDO (PHP Data Objects)

  Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

  Should I Use MySQLi or PDO?

  If you need a short answer, it would be «Whatever you like».

  Both MySQLi and PDO have their advantages:

  PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

  So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code — queries included.

  Both are object-oriented, but MySQLi also offers a procedural API.

  Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

  MySQL Examples in Both MySQLi and PDO Syntax

  In this, and in the following chapters we demonstrate three ways of working with PHP and MySQL:

  MySQLi Installation

  For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

  PDO Installation

  Open a Connection to MySQL

  Before we can access data in the MySQL database, we need to be able to connect to the server:

  Example (MySQLi Object-Oriented)

  $servername = «localhost»;
  $username = «username»;
  $password = «password»;

  // Create connection
  $conn = new mysqli($servername, $username, $password);

  // Check connection
  if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
  >
  echo «Connected successfully»;
  ?>

  Note on the object-oriented example above:

  $connect_error was broken until PHP 5.2.9 and 5.3.0. If you need to ensure compatibility with PHP versions prior to 5.2.9 and 5.3.0, use the following code instead:

  // Check connection
  if (mysqli_connect_error()) die(«Database connection failed: » . mysqli_connect_error());
  >

  Example (MySQLi Procedural)

  $servername = «localhost»;
  $username = «username»;
  $password = «password»;

  // Create connection
  $conn = mysqli_connect($servername, $username, $password);

  // Check connection
  if (!$conn) die(«Connection failed: » . mysqli_connect_error());
  >
  echo «Connected successfully»;
  ?>

  Example (PDO)

  $servername = «localhost»;
  $username = «username»;
  $password = «password»;

  try $conn = new PDO(«mysql:host=$servername;dbname=myDB», $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo «Connected successfully»;
  > catch(PDOException $e) echo «Connection failed: » . $e->getMessage();
  >
  ?>

  Note: In the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

  Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try < >block, the script stops executing and flows directly to the first catch() < >block.

  Close the Connection

  The connection will be closed automatically when the script ends. To close the connection before, use the following:

  Источник

  PHP mysqli connect() Function

  The connect() / mysqli_connect() function opens a new connection to the MySQL server.

  Syntax

  Object oriented style:

  Procedural style:

  Parameter Values

  Parameter	Description
  host	Optional. Specifies a host name or an IP address
  username	Optional. Specifies the MySQL username
  password	Optional. Specifies the MySQL password
  dbname	Optional. Specifies the default database to be used
  port	Optional. Specifies the port number to attempt to connect to the MySQL server
  socket	Optional. Specifies the socket or named pipe to be used

  Technical Details

  Example — Procedural style

  Open a new connection to the MySQL server:

  // Check connection
  if (mysqli_connect_errno()) echo «Failed to connect to MySQL: » . mysqli_connect_error();
  exit();
  >
  ?>

  

  COLOR PICKER

  

  

  Report Error

  If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

  Thank You For Helping Us!

  Your message has been sent to W3Schools.

  Top Tutorials

  Top References

  Top Examples

  Get Certified

  W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.

  Источник

  Обработка ошибок в соединении и запросах к базе данных в PHP (mysqli_connect_error, mysqli_connect_errno, mysqli_error, mysqli_errno)

  Правильная обработка ошибок позволяет сделать стабильное приложение, которое не будет завершаться неожиданно. В случае возникновения непредвиденной ситуации программа должна выдавать вменяемый ответ, почему она не хочет исполнится. И создание правильного механизма обработки ошибок — это задача программиста.

  В этой статье рассмотрим два вида ошибок, которые могут возникнуть при программировании взаимодействия с базой данных. Первый тип — это ошибка подключения к базе данных. Второй тип — это ошибка выполнения запроса к базе данных. Для обработки этих ошибок будем использовать специальные функции для работы с базой.

  Ошибка соединения с базой данных

  '; echo 'Код ошибки: ' . mysqli_connect_errno(); >else < // соединение было установлено успешно // здесь можно делать запрос к базе, // потому что соединение успешно установлено >?>

  В этом примере можно заметить функцию mysqli_connect_error. Она выводит текстовое описание ошибки подключения (на английском языке). В отличии от неё функция mysqli_connect_errno выводит числовой код ошибки, к примеру «1045».

  Ошибка запроса к базе

  Теперь попробуем доработать пример из предыдущего параграфа и добавить запрос к базе данных. Не будем вдаваться в детали, что будет записано в тексте SQL запроса, просто предположим, что он может завершиться ошибкой. К примеру, из-за неожиданного отключения сервера с базой данных от сети. В примере добавим проверку на наличие ошибок при выполнении запроса:

  '; echo 'Код ошибки: ' . mysqli_connect_errno(); >else< // подключение успешно установлено // текст SQL запроса, который будет передан базе $query = 'SELECT * FROM `USERS`'; // выполняем запрос к базе данных $result = mysqli_query($connection, $query); if(!$result)< // запрос завершился ошибкой echo 'Ошибка запроса: ' . mysqli_error($connection) . '
  '; echo 'Код ошибки: ' . mysqli_errno($connection); >else< // запрос успешно выполнился while($row = $result->fetch_assoc()) < // обрабатываем полученные данные >> // закрываем соединение с базой mysqli_close($connection); > ?>

  В этом примере есть две функции, которые работают с ошибками базы. Функция mysqli_error возвращает описание ошибки запроса (на английском языке), а функция mysqli_errno возвращает числовой код ошибки, к примеру, «1193».

  Обратите внимание, что все функции обработки ошибок в этой статье (mysqli_connect_error, mysqli_connect_errno, mysqli_error, mysqli_errno) возвращают информацию только о последней ошибке. Но ошибок может быть несколько.

  Источник