Home >Backend Development >PHP Tutorial >PHP database mysql query and connection class_PHP tutorial
class mysql
{
var $host = ""; //mysql host name
var $user = ""; //mysql username
var $pwd = ""; //mysql password
var $dbName = ""; //mysql database name
var $linkID = 0; //Used to save the connection ID
var $queryID = 0; //Used to save query ID
var $fetchMode= MYSQL_ASSOC;//Mode when fetching records
var $queryTimes = 0; //Number of saved queries
var $errno = 0; //mysql error code
var $error = ""; //mysql error message
var $record = array(); //A record array
//======================================
// Function: mysql()
// Function: Constructor
// Parameter: Variable definition of parameter class
// Description: The constructor will automatically connect to the database
// If you want to connect manually, remove the connection function
//======================================
function mysql($host,$user,$pwd,$dbName)
{ if(empty($host) || empty($user) || empty($dbName))
$this->halt("The database host address, user name or database name is incomplete, please check!");
$this->host = $host;
$this->user = $user;
$this->pwd = $pwd;
$this->dbName = $dbName;
$this->connect();//Set to automatically connect
}
//======================================
// Function: connect($host,$user,$pwd,$dbName)
// Function: Connect to database
// Parameters: $host hostname, $user username
// Parameters: $pwd password, $dbName database name
// Return: 0: Failure
// Description: By default, the initial value of the variable in the class is used
//======================================
function connect($host = "", $user = "", $pwd = "", $dbName = "")
{
if ("" == $host)
$host = $this->host;
if ("" == $user)
$user = $this->user;
if ("" == $pwd)
$pwd = $this->pwd;
if ("" == $dbName)
$dbName = $this->dbName;
//now connect to the database
$this->linkID = mysql_pconnect($host, $user, $pwd);
if (!$this->linkID)
{
$this->halt();
Return 0;
}
if (!mysql_select_db($dbName, $this->linkID))
{
$this->halt();
Return 0;
}
Return $this->linkID;
}
//======================================
// Function: query($sql)
// Function: Data query
// Parameter: $sql SQL statement to be queried
// Return: 0: Failure
//======================================
function query($sql)
{
$this->queryTimes++;
$this->queryID = mysql_query($sql, $this->linkID);
if (!$this->queryID)
{
$this->halt();
Return 0;
}
Return $this->queryID;
}
//======================================
// Function: setFetchMode($mode)
// Function: Set the record acquisition mode
// Parameters: $mode mode MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
// Return: 0: Failure
//======================================
function setFetchMode($mode)
{
if ($mode == MYSQL_ASSOC || $mode == MYSQL_NUM || $mode == MYSQL_BOTH)
{
$this->fetchMode = $mode;
Return 1;
}
else
{
$this->halt("Wrong mode.");
Return 0;
}
}
//======================================
// Function: fetchRow()
// Function: Get a record from the record set
// Return: 0: Error record: A record
//======================================
function fetchRow()
{
$this->record = mysql_fetch_array($this->queryID,$this->fetchMode);
Return $this->record;
}
//======================================
// Function: fetchAll()
// Function: Get all records from the record set
// Return: recordset array
//======================================
function fetchAll()
{
$arr = array();
while($this->record = mysql_fetch_array($this->queryID,$this->fetchMode))
{
$arr[] = $this->record;
}
mysql_free_result($this->queryID);
return $arr;
}
//======================================
// Function: getValue()
// Function: Return the data of the specified field in the record
// Parameter: $field field name or field index
// Return: value of the specified field
//======================================
function getValue($field)
{
Return $this->record[$field];
}
//======================================
// Function: affectedRows()
// Function: Return the number of affected records
//======================================
function affectedRows()
{
Return mysql_affected_rows($this->linkID);
}
//======================================
// Function: recordCount()
// Function: Return the total number of query records
// Parameters: None
// Return: total number of records
//======================================
function recordCount()
{
Return mysql_num_rows($this->queryID);
}
//======================================
// Function: getQueryTimes()
// Function: Return the number of queries
// Parameters: None
// Return: the number of queries
//======================================
function getQueryTimes()
{
Return $this->queryTimes;
}
//======================================
// Function: getVersion()
// Function: Return the mysql version
// Parameters: None
//======================================
function getVersion()
{
$this->query("select version() as ver");
$this->fetchRow();
Return $this->getValue("ver");
}
//======================================
// Function: getDBSize($dbName, $tblPrefix=null)
// Function: Return the space occupied by the database
// Parameter: $dbName database name
// Parameter: $tblPrefix table prefix, optional
//======================================
function getDBSize($dbName, $tblPrefix=null)
{
$sql = "SHOW TABLE STATUS FROM " . $dbName;
if($tblPrefix != null) {
$sql .= " LIKE '$tblPrefix%'";
}
$this->query($sql);
$size = 0;
while($this->fetchRow())
$size += $this->getValue("Data_length") + $this->getValue("Index_length");
Return $size;
}
//======================================
// Function: insertID()
// Function: Return the last inserted auto-increment ID
// Parameters: None
//======================================
function insertID() {
Return mysql_insert_id();
}
//======================================
// Function: halt($err_msg)
// Function: Handle all error messages
// Parameter: $err_msg Customized error message
//======================================
function halt($err_msg="")
{
if ("" == $err_msg)
{
$this->errno = mysql_errno();
$this->error = mysql_error();
echo "mysql error:
";
echo $this->errno.":".$this->error."
";
exit;
}
else
{
echo "mysql error:
";
echo $err_msg."
";
exit;
}
}
}
?>