PHP数据库操作简单封装

最新推荐文章于 2022-10-09 20:33:32 发布

原创最新推荐文章于 2022-10-09 20:33:32 发布 · 198 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#php #操作数据库

PHP 专栏收录该内容

113 篇文章

订阅专栏

final class util {

    /**
     * 数据库操作简单封装
     * @param string $sql
     * @param number $db
     * @param boolean $single 结果只有一条的时候适用
     * @return mixed
     */
    public static function query($sql, $db, $single = false) {
        $conn = util::connect($db);
        if (!$conn) {
            UB_LOG_FATAL("connect mysql failed");
            return null;
        }
        return self::queryConn($sql, $conn, $single);
    }

    /**
     * 连接数据库wrapper
     * @param integer $db
     * @param integer $type 1 read 2 write
     * @param boolean $useCache 是否使用缓存中的连接句柄(多进程时可能需要)
     * @return resource 数据库连接句柄
     */
    public static function connect($db, $type = conf::READ, $useCache = true) {
        static $connCache = array();
        $k = $db.$type;
        if ($useCache && isset($connCache[$k]) && $connCache[$k] && mysqli_ping($connCache[$k])) {
            return $connCache[$k];
        }
        switch ($db) {
            case conf::FIRSTDB:
                $config = self::parseDsnFile('first-db-read');
                break;
            case conf::SECONDDB:
                $config = self::parseDsnFile('second-db-read');
                break;
            default:
                UB_LOG_FATAL("db not find db config, db name is %s", $db);
                return false;
        }
        $host = $config['host'];
        $port = $config['port'];
        $user = $config['user'];
        $pass = $config['password'];
        $retry = 4;
        do {
            $conn = mysqli_connect($host, $user, $pass, "", $port);
            if ($conn) {
                break;
            }
            $time = 60 * (5 - $retry);
            UB_LOG_WARNING("connected to mysql server at mysql://%s:******@%s:%d failed, retry after $time seconds, err: %s", $user, $host, $port, mysqli_connect_error());
            sleep($time); // 建立连接失败则休眠一段时间后再次尝试
        } while ($retry-- > 0);
        if (!$conn) {
            UB_LOG_FATAL("can't connect to mysql server at mysql://%s:******@%s:%d", $user, $host, $port);
            return false;
        }
        UB_LOG_DEBUG("connected to mysql server at mysql://%s:******@%s:%d", $user, $host, $port);
        mysqli_set_charset($conn,"latin1");
        if ($useCache) {
            $connCache[$k] = $conn;
        }
        return $conn;
    }

    /**
     * a simple wrapper for mysql_query
     * @param string $sql
     * @param resource $conn mysql connection
     * @param boolean $single if the query result is single
     * @return mixed
     */
    public static function queryConn($sql, $conn, $single = false) {
        if (!$conn) {
            UB_LOG_FATAL("conn is not a valid MySQL-Link resource");
            return false;
        }
        if (DEBUG) {
            UB_LOG_DEBUG("execute sql %s", $sql);
        }
        $res = mysqli_query($conn,$sql);
        if (!$res) {
            UB_LOG_FATAL("execute sql %s , errCode: %d, failed: %s", $sql, mysqli_errno($conn), mysqli_error($conn));
            return false;
        }
        $data = array();
        while ($row = mysqli_fetch_assoc($res)) {
            $data[] = $row;
        }
        if ($single && $data) {
            return $data[0];
        }
        return $data;
    }

    private static function parseDsnFile($fileName) {
        $dsnPath = conf::$DSN_CONF_PATH. $fileName . '.dsn';
        try {
            $dsnContent = file_get_contents($dsnPath);
        } catch (\Exception $e) {
            UB_LOG_FATAL("load {$dsnPath} failed");
        }
        preg_match("/mysql:\/\/'?([\s\S]*?)'?:'?([\s\S]*?)'?@([\s\S]*?):(\d+)/", $dsnContent, $matches);
        $dbConfig = [
            'user'     => $matches[1],
            'password' => $matches[2],
            'host'     => $matches[3],
            'port'     => $matches[4],
        ];
        return $dbConfig;
    }


}