| <?php /**
* MySQL PDO operation class
* Created by Phpstorm.
* User:sumiaowen
* DATE:14-3-12
* Time: 4:57
* To change this template use File | Settings | File Templates.
*/
Class Mymysql
{
PDO link MySQL DNS
static $dns = null; MySQL User name
static $username = null; MySQL Password
static $password = null; PDO Link Instance
static $pdo = null; Debugging
public $debug = null; Start a transaction
Private $_begin_transaction = false; /**
* @param bool $debug open debugging, error message output
* @param string $database Database category
*/
Public function __construct ($debug = true, $database = ' default ')
{
$this->debug = $debug;
Self:: $dns = yaf_registry::get (' config ')->db-> $database->dns;
Self:: $username = yaf_registry::get (' config ')->db-> $database->username;
Self:: $password = yaf_registry::get (' config ')->db-> $database->password;
} /**
* PDO Object Instantiation
* @return null| Pdo
*/
static function instance ()
{
if (is_null self:: $pdo))
{
Try
{
Self:: $pdo = new PDO (self:: $dns, Self:: $username, Self:: $password);
Self:: $pdo->query (' Set names UTF8 ');
}
catch (Pdoexception $e)
{
Exit (' Pdoexception: '. $e->getmessage ());
}
} Return self:: $pdo;
} /**
* Preprocessing executes SELECT SQL statements
* @param string $sql
* @param array $parameters
* @param int $option
* @return Array
*/
Public Function query ($sql, $parameters = Array (), $option = PDO::FETCH_ASSOC)
{
Self:: $pdo | | Self::instance (); $stmt = self:: $pdo->prepare ($sql);
$stmt->execute ($parameters); $tmp = Array ();
while ($row = $stmt->fetch ($option))
{
$tmp [] = $row;
} if ($this->debug)
{
$this->error ($stmt);
} return $tmp;
} /**
* Preprocessing executes update, DELETE, insert SQL statements
* @param sting $sql
* @param array $parameters
* @return Int returns the number of affected rows
*/
Public function Execute ($sql, $parameters = Array ())
{
Self:: $pdo | | Self::instance (); $stmt = self:: $pdo->prepare ($sql);
$stmt->execute ($parameters); if ($this->debug)
{
$this->error ($stmt);
} return $stmt->rowcount ();
} /**
* Execute an SQL statement
* @param string $sql
* @return Int returns the number of affected rows
*/
Public function exec ($sql)
{
Self:: $pdo | | Self::instance (); $rows = self:: $pdo->exec ($sql); if ($this->debug)
{
$this->error ();
} return $rows;
} /**
* Add a record
* @param string $tableName database table name
* @param array $data need to add data, a key|value corresponding array, where key is the table field name, value is inserted values, such as: $data = array (' keyword ' => ' keyword ')
* @return INT returns the ID of the inserted row
*/
Public Function Insert ($tableName, $data)
{
Self:: $pdo | | Self::instance (); $fields = "". Implode (",", Array_keys ($data)). '`'; $values = "'". Implode ("', '", $data). "'"; $sql = "INSERT into ' {$tableName} ' ({$fields}) VALUES ({$values})"; Self:: $pdo->exec ($sql); if ($this->debug)
{
$this->error ();
} return $this->getlastinsertid ();
} /**
* Add more than one data
* @param string $tableName database table name
* @param array $data need to add data for a two-dimensional array, such as: $data = Array (' fileld1 ' => ' value1 ', ' fileld2 ' => ' value2 '), Array (' Fil Eld1 ' => ' value1 ', ' fileld2 ' => ' value2 ')
* @return Int returns the number of affected rows
*/
Public Function Insertbatch ($tableName, $data)
{
Self:: $pdo | | Self::instance (); $fields = "". Implode (",", Array_keys ($data [0])). '`'; $tmp = Array ();
foreach ($data as $value)
{
$tmp [] = "'". Implode ("', '", $value). "'";
} $values = "(". Implode ("), (", $tmp). "); $sql = "INSERT into ' {$tableName} ' ({$fields}) VALUES {$values}"; $rows = self:: $pdo->exec ($sql); if ($this->debug)
{
$this->error ();
} return $rows;
} /**
* Update data based on primary key
* @param string $tableName database table name
* @param array $where update condition, which corresponds to Key|value, for example: Array (' ID ' =>233)
* @param array $data to update the data, which corresponds to Key|value, such as: Array (' field1 ' => ' value1 ', ' field12 ' => ' value2 ')
* @return int successfully returns the number of affected rows, failure returns an error message
*/
Public Function Updatebyprimarykey ($tableName, $where, $data)
{
Self:: $pdo | | Self::instance (); Conditions
$whereId = Array_keys ($where);
$whereValue = Array_values ($where); $tmp = Array ();
foreach ($data as $key => $value)
{
$tmp [] = "' {$key} ' = ' {$value} '";
} $data = Implode (', ', $tmp); $sql = "UPDATE ' {$tableName} ' SET {$data} WHERE ' {$whereId [0]} ' = ' {$whereValue [0]} '"; $rows = self:: $pdo->exec ($sql); if ($this->debug)
{
$this->error ();
} return $rows;
} /**
* Delete data based on primary key
* @param string $tableName database table name
* @param array $where Delete condition, an array corresponding to Key|value, such as: Array (' ID ' =>233)
* @return int successfully returns the number of affected rows, failure returns an error message
*/
Public Function Deletebyprimarykey ($tableName, $where)
{
Self:: $pdo | | Self::instance (); Conditions
$whereId = Array_keys ($where);
$whereValue = Array_values ($where); $sql = "DELETE from ' {$tableName} ' WHERE ' {$whereId [0]} ' = ' {$whereValue [0]} '"; $rows = self:: $pdo->exec ($sql); if ($this->debug)
{
$this->error ();
} return $rows;
} /**
* Returns the ID or sequence value of the last inserted row
* @return int
*/
Public Function Getlastinsertid ()
{
Self:: $pdo | | Self::instance (); Return self:: $pdo->lastinsertid ();
} /**
* Set error message
*/
Public Function error ($stmt = ')
{
$error = $stmt? $stmt->errorinfo (): Self:: $pdo->errorinfo (); $msg = "sqlstate:{$error [0]}"; if ($error [1])
{
$msg. = "-errorcode:{$error [1]}";
} if ($error [2])
{
$msg. = "-error:{$error [2]}";
} if ($error [1] | | $error [2])
{
Exit ($MSG);
}
} /**
* Start of Business
* @return BOOL
*/
Public function begin ()
{
Self:: $pdo | | Self::instance (); There are already transactions, exiting the transaction
$this->rollback (); if (!self:: $pdo->begintransaction ())
{
return false;
} return $this->_begin_transaction = true;
} /**
* Transaction Submission
* @return BOOL
*/
Public Function commit ()
{
if ($this->_begin_transaction)
{
$this->_begin_transaction = false;
Self:: $pdo->commit ();
} return true;
} /**
* Transaction rollback
* @return BOOL
*/
Public Function rollback ()
{
if ($this->_begin_transaction)
{
$this->_begin_transaction = false;
Self:: $pdo->rollback ();
} return false;
} /**
* Close Link
*/
Public Function Close ()
{
Self:: $pdo = null;
}
} |
