MySQL工具類(lèi)

功能:

1. 單例創(chuàng)建工具類(lèi)對(duì)象，并連接MySQL
2. 對(duì)mysql_query做封裝，錯(cuò)誤時(shí)打印錯(cuò)誤SQL語(yǔ)句、出錯(cuò)信息、出錯(cuò)碼
3. 查詢(xún)方法1：返回查詢(xún)的全部結(jié)果，并作為數(shù)組返回
4. 查詢(xún)方法2：返回第一行數(shù)據(jù)，并作為數(shù)據(jù)返回
5. 查詢(xún)方法3：返回第一行的第一列數(shù)據(jù)，并作為數(shù)據(jù)返回

<?php
//定義連接數(shù)據(jù)庫(kù)的類(lèi)，并返回鏈接后的資源
//實(shí)現(xiàn)單例模式
//執(zhí)行普通增刪改非返回結(jié)果集語(yǔ)句
//執(zhí)行select語(yǔ)句并可以返回3種類(lèi)型的數(shù)據(jù)
//單行結(jié)果(一維數(shù)組)，多行結(jié)果(二維數(shù)組)
//單行單例(單個(gè)數(shù)據(jù))
class mysqlDB{
    public $host;
    public $port;
    public $username;
    public $password;
    public $charset;
    public $dbname;

    //連接結(jié)果
    private static $link;

    private $resource;

    //單例方法
    public static function getInstance($config){
        if(!isset(self::$link)){
            self::$link = new self($config);
        }
        return self::$link;
    }

    //禁止new
    private function __construct($config){
        $this->host = isset($config['host'])?$config['host']:'localhost';
        $this->port = isset($config['port'])?$config['port']:'8889';
        $this->username = isset($config['username'])?$config['username']:'root';
        $this->password = isset($config['password'])?$config['password']:'root';
        $this->charset = isset($config['charset'])?$config['charset']:'utf8';
        $this->dbname = isset($config['dbname'])?$config['dbname']:'';

        $this->connect();
        //設(shè)定連接編碼
        $this->setCharset($this->charset);
        //設(shè)定數(shù)據(jù)庫(kù)
        $this->selectDb($this->dbname);
    }
    //禁止clone
    private function __clone(){}

    public function connect(){
        $this->resource = mysql_connect("$this->host:$this->port","$this->username","$this->password") or die("連接數(shù)據(jù)庫(kù)失敗");
    }
    public function setCharset($charset){
        $this->query("set name $charset");

    }
    public function selectDb($dbname){
        $this->query("use $dbname") or die('選擇數(shù)據(jù)庫(kù)失敗');
    }
    /**
     * 執(zhí)行最基本sql語(yǔ)句
     * @param  返回錯(cuò)誤代碼
     * @return 返回執(zhí)行結(jié)果
     */
    public function query($sql){
        //執(zhí)行失敗
        if(!$result = mysql_query($sql,$this->resource)){
            echo "<br/>sql語(yǔ)句：".$sql;
            echo "<br/出錯(cuò)信息>：".mysql_error();
            echo "<br/出錯(cuò)代碼>：".mysql_errno();
            die();
        }
        return $result;
    }
    /**
     * 功能執(zhí)行select語(yǔ)句，返回2維數(shù)組
     * 參數(shù):$sql 字符串類(lèi)型 select語(yǔ)句
     */
    public function getAll($sql){
        $result = $this -> query($sql);
        $arr = array();
        while($rec = mysql_fetch_assoc($result)){
            $arr[] = $rec;
        }
        return $arr;
    }
    /**
     * 功能：返回一行數(shù)據(jù)作為一維數(shù)組
     * 參數(shù):$sql 字符串類(lèi)型 select語(yǔ)句
     */
    public function getRow($sql){
        $result = $this -> query($sql);
        if($rec2 = mysql_fetch_assoc($result)){
            //如果fetch出來(lái)有數(shù)據(jù)，返回一維數(shù)組
            return $rec2;
        }
        return false;
    }

    /**
     * 功能：返回select的第一行第一列
     * 參數(shù):$sql 字符串類(lèi)型 select語(yǔ)句
     */
    public function getOne($sql){
        $result = $this -> query($sql);
        $rec = mysql_fetch_row($result);//返回下標(biāo)為數(shù)字的數(shù)組
        if($rec == false){
            return false;
        }
        return $rec[0];
    }
}
?>

昨天球隊(duì)信息例子來(lái)展示如何使用。昨天內(nèi)容戳這里

<?
    require './SQLDB.class.php';
    # 比賽列表
    header('Content-Type: text/html;charset=utf-8');

    //通過(guò)數(shù)據(jù)操作，將比賽列表需要的數(shù)據(jù)處理
    //初始化MySQLDB

    $config = array(
        'host'=>'localhost',
        'port'=>'8889',
        'username'=>'root',
        'password'=>'root',
        'charset'=>'utf8',
        'dbname'=>'php'
    );
    $dao = MySQLDB::getInstance($config);//$dao,Database Access Object 數(shù)據(jù)庫(kù)操作對(duì)象(道層)

    //獲得比賽里表數(shù)據(jù)
    $sql = "select t1.t_name as t1_name, m.t1_score, m.t2_score, t2.t_name as t2_name, m.m_time from `match` as m 
        left join `team` as t1 ON
        m.t1_id = t1.t_id  
        left join `team` as t2 ON 
        m.t2_id=t2.t_id;";
    $match_list=$dao->getAll($sql);
?>
<!-- 利用html展示數(shù)據(jù) -->
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>比賽列表</title>
</head>
<body>
    <table>
        <tr>
            <th>球隊(duì)一</th><th>比分</th><th>球隊(duì)二</th><th>比賽時(shí)間</th>
        </tr>
            <?php foreach ($match_list as $row) :?>
            <tr>
                <td><?php echo $row['t1_name'];?></td>
                <td><?php echo $row['t1_score'];?>:<?php echo $row['t2_score'];?></td>
                <td><?php echo $row['t2_name'];?></td>
                <td><?php echo date('Y-m-d H:i',$row['m_time']);?></td>
            </tr>
            <?php endForeach ?>
    </table>
</body>
</html>

執(zhí)行結(jié)果