Home  >  Article  >  Backend Development  >  PDO implements a method to determine whether the connection is available

PDO implements a method to determine whether the connection is available

墨辰丷
墨辰丷Original
2018-05-24 10:40:183505browse

This article mainly introduces the method of php PDO to determine whether the connection is available. Interested friends can refer to it. I hope it will be helpful to everyone.

mysql_ping() Checks whether the connection to the server is normal. Returns true if a connection to the server is available, false otherwise.

But PDO does not support the mysql_ping() method, so you need to write your own code to simulate the mysql_ping() method to check whether the connection is available.

The code is as follows:

<?php
/**
 * 检查连接是否可用
 * @param Link $dbconn 数据库连接
 * @return Boolean
 */
function pdo_ping($dbconn){
  try{
    $dbconn->getAttribute(PDO::ATTR_SERVER_INFO);
  } catch (PDOException $e) {
    if(strpos($e->getMessage(), &#39;MySQL server has gone away&#39;)!==false){
      return false;
    }
  }
  return true;
}
?>

Code demonstration:

1. Create a test data table

CREATE TABLE `user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


2. Insert test data

insert into user(name) values(&#39;fdipzone&#39;),(&#39;xfdipzone&#39;),(&#39;terry&#39;);

mysql> select * from user;
+----+-----------+
| id | name   |
+----+-----------+
| 1 | fdipzone |
| 2 | xfdipzone |
| 3 | terry   |
+----+-----------+


3. Demo file

db.php

##

<?php
// 数据库操作类
class DB{

  // 保存数据库连接
  private static $_instance = null;

  // 连接数据库
  public static function get_conn($config){
    if(isset(self::$_instance) && !empty(self::$_instance)){
      return self::$_instance;
    }

    $dbhost = $config[&#39;host&#39;];
    $dbname = $config[&#39;dbname&#39;];
    $dbuser = $config[&#39;user&#39;];
    $dbpasswd = $config[&#39;password&#39;];
    $pconnect = $config[&#39;pconnect&#39;];
    $charset = $config[&#39;charset&#39;];

    $dsn = "mysql:host=$dbhost;dbname=$dbname;";
    try {
      $h_param = array(
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      );
      if ($charset != &#39;&#39;) {
        $h_param[PDO::MYSQL_ATTR_INIT_COMMAND] = &#39;SET NAMES &#39; . $charset; //設置默認編碼
      }
      if ($pconnect) {
        $h_param[PDO::ATTR_PERSISTENT] = true;
      }
      $conn = new PDO($dsn, $dbuser, $dbpasswd, $h_param);

    } catch (PDOException $e) {
      throw new ErrorException(&#39;Unable to connect to db server. Error:&#39; . $e->getMessage(), 31);
    }

    self::$_instance = $conn;
    return $conn;
  }

  // 执行查询
  public static function query($dbconn, $sqlstr, $condparam){
    $sth = $dbconn->prepare($sqlstr);
    try{
      $sth->execute($condparam);
    } catch (PDOException $e) {
      echo $e->getMessage().PHP_EOL;
    }
    $result = $sth->fetchAll(PDO::FETCH_ASSOC);
    return $result;
  }

  // 重置连接
  public static function reset_connect(){
    self::$_instance = null;
  }

}
?>

test.php

<?php
require &#39;db.php&#39;;

// 数据库设定
$config = array(
  &#39;host&#39; => &#39;localhost&#39;,
  &#39;dbname&#39; => &#39;user&#39;,
  &#39;user&#39; => &#39;root&#39;,
  &#39;password&#39; => &#39;&#39;,
  &#39;pconnect&#39; => 0,
  &#39;charset&#39; => &#39;&#39;
);

// 循环执行
while(true){

  // 创建数据连接
  $dbconn = DB::get_conn($config);

  // 判断连接是否有效
  $status = pdo_ping($dbconn);

  if($status){
    echo &#39;connect ok&#39;.PHP_EOL;
  }else{
    echo &#39;connect failure&#39;.PHP_EOL;

    // 重置连接
    DB::reset_connect();
    $dbconn = DB::get_conn($config);
  }

  // 执行查询
  $sqlstr = &#39;select * from user where id=?&#39;;
  $condparam = array(mt_rand(1,3));
  $data = DB::query($dbconn, $sqlstr, $condparam);
  print_r($data);

  // 延时10秒
  echo &#39;sleep 10&#39;.PHP_EOL.PHP_EOL;
  sleep(10);

}

/**
 * 检查连接是否可用
 * @param Link $dbconn 数据库连接
 * @return Boolean
 */
function pdo_ping($dbconn){
  try{
    $dbconn->getAttribute(PDO::ATTR_SERVER_INFO);
  } catch (PDOException $e) {
    if(strpos($e->getMessage(), &#39;MySQL server has gone away&#39;)!==false){
      return false;
    }
  }
  return true;
}
?>

4. Execution

Execute test.php in php cli mode, and then immediately execute mysql.server stop and mysql.server start to simulate a flash interruption

mysql.server stop
Shutting down MySQL
.... SUCCESS! 
mysql.server start
Starting MySQL
 SUCCESS!


Execution output:

connect ok
Array
(
  [0] => Array
    (
      [id] => 2
      [name] => xfdipzone
    )

)
sleep 10

connect failure
Array
(
  [0] => Array
    (
      [id] => 3
      [name] => terry
    )

)
sleep 10

connect ok
Array
(
  [0] => Array
    (
      [id] => 3
      [name] => terry
    )

)
sleep 10

You can see that after the interruption, pdo_ping() returns false and the connection fails. Then call automatic reconnection to ensure that subsequent queries can continue to be executed.

The above is the entire content of this article, I hope it will be helpful to everyone's study.


Related recommendations:

Use Composer to automatically load the php namespace

PHP 3 ways to connect to the database

Complete list of redis commands under php

The above is the detailed content of PDO implements a method to determine whether the connection is available. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn