PDO mysql database operation class_PHP tutorial-PHP Tutorial-php.cn

PDO mysql database operation class_PHP tutorial

WBOY

Release： 2016-07-13 17:05:40

Original

917 people have browsed it

pdo’s mysql tutorial database tutorial operation class
* The dbconfig class is responsible for configuring database access information, including: server address, port, database instance name, user name, user password, character set, etc.
* The dbtemplate class collects access operations to the database, mainly including the following operations:
1. queryrows: Return multiple rows of records
2. queryrow: return as a single record
3. queryforint: Query a single field and return an integer
4. queryforfloat: Query a single field and return a floating point number (float)
5. queryfordouble: Query a single field and return a floating point number (double)
6. queryforobject: Query a single field and return an object. The actual type is determined by the database
7. update: execute an update statement. insert / upadate / delete

class dbconfig {

private static $dbms = "mysql";
Private static $host = '127.0.0.1';
Private static $port = '3306';
Private static $username = '';
Private static $password = '';
Private static $dbname = '';
Private static $charset = 'utf-8';
Private static $dsn;

/**
*
* @return Return pdo dsn configuration
*/
Public static function getdsn() {
If (!isset(self::$dsn)) {
                   self::$dsn = self::$dbms . ':host=' . self::$host . ';port=' .
                                                                                                                                                                                                                                                           self::$port . ';dbname=' . self::$dbname;
If (strlen(self::$charset) > 0) {
                     self::$dsn = self::$dsn . ';charset=' . self::$charset;
            }
}
          return self::$dsn;
}

/**
* * Set up mysql database server host
* @param $host IP address of the host
*/
Public static function sethost($host) {
If (isset($host) && strlen($host) > 0)
                self::$host = trim($host);
}

/**
*Set the port of mysql database server
* @param $port port
*/
Public static function setport($port) {
If (isset($port) && strlen($port) > 0)
                self::$port = trim($port);
}

/**
* * Set the login user name of the mysql database server
* @param $username
*/
Public static function setusername($username) {
If (isset($username) && strlen($username) > 0)
                self::$username = $username;
}

/**
* *Set the login password for the mysql database server
* @param $password
*/
Public static function setpassword($password) {
If (isset($password) && strlen($password) > 0)
                self::$password = $password;
}

/**
* * Set the database instance name of the mysql database server
* @param $dbname Database instance name
*/
Public static function setdbname($dbname) {
If (isset($dbname) && strlen($dbname) > 0)
                self::$dbname = $dbname;
}

    /**
* *Set database encoding
* @param $charset
*/
    public static function setcharset($charset) {
        if (isset($charset) && strlen($charset) > 0)
            self::$charset = $charset;
    }

}

/**
* A database operation tool class
*
* @author zhjiun@gmail.com
*/
class dbtemplate {

    /**
* * Return multi-line records
* @param $sql
* @param $parameters
* @return Record data
*/
    public function queryrows($sql, $parameters = null) {
        return $this->exequery($sql, $parameters);
    }

    /**
* Return as a single record
* @param $sql
* @param $parameters
* @return
*/
    public function queryrow($sql, $parameters = null) {
        $rs = $this->exequery($sql, $parameters);
        if (count($rs) > 0) {
            return $rs[0];
        } else {
            return null;
        }
    }

    /**
* Query a single field and return an integer
* @param $sql
* @param $parameters
* @return
*/
    public function queryforint($sql, $parameters = null) {
        $rs = $this->exequery($sql, $parameters);
        if (count($rs) > 0) {
            return intval($rs[0][0]);
        } else {
            return null;
        }
    }

    /**
* Query a single field and return a floating point number (float)
* @param $sql
* @param $parameters
* @return
*/
    public function queryforfloat($sql, $parameters = null) {
        $rs = $this->exequery($sql, $parameters);
        if (count($rs) > 0) {
            return floatval($rs[0][0]);
        } else {
            return null;
        }
    }

    /**
* Query a single field and return a floating point number (double)
* @param $sql
* @param $parameters
* @return
*/
    public function queryfordouble($sql, $parameters = null) {
        $rs = $this->exequery($sql, $parameters);
        if (count($rs) > 0) {
            return doubleval($rs[0][0]);
        } else {
            return null;
        }
    }

    /**
* Query a single field and return an object. The actual type is determined by the database
* @param $sql
* @param $parameters
* @return
*/
    public function queryforobject($sql, $parameters = null) {
        $rs = $this->exequery($sql, $parameters);
        if (count($rs) > 0) {
            return $rs[0][0];
        } else {
            return null;
        }
    }

    /**
* Execute an update statement.insert/upadate/delete
* @param $sql
* @param $parameters
* @return Number of affected rows
*/
    public function update($sql, $parameters = null) {
        return $this->exeupdate($sql, $parameters);
    }

    private function getconnection() {
        $conn = new pdo(dbconfig::getdsn(), dbconfig::getusername(), dbconfig::getpassword());
        $conn->setattribute(pdo::attr_case, pdo::case_upper);
        return $conn;
    }

    private function exequery($sql, $parameters = null) {
        $conn = $this->getconnection();
        $stmt = $conn->prepare($sql);
        $stmt->execute($parameters);
        $rs = $stmt->fetchall();
        $stmt = null;
        $conn = null;
        return $rs;
    }

    private function exeupdate($sql, $parameters = null) {
        $conn = $this->getconnection();
        $stmt = $conn->prepare($sql);
        $stmt->execute($parameters);
        $affectedrows = $stmt->rowcount();
        $stmt = null;
        $conn = null;
        return $affectedrows;
    }
}