Simple php database operation class code of add delete change check
- 2020-05-30 19:42:32
- OfStack
The basic process of database manipulation is as follows:
1. Connect to the database server
2. Select the database
3. Execute the SQL statement
4. Process the result set
5. Print operation information
The related functions used are
The & # 8226; resource mysql_connect ([string server [, string username [, string password [, bool new_link [, int client_flags]]]]]) connects to the database server
The & # 8226; resource mysql_pconnect ([string server [, string username [, string password [, int client_flags]]]]) connects to the database server, long connection
The & # 8226; int mysql_affected_rows ([resource link_identifier]) gets the number of record rows affected by the last INSERT, UPDATE, or DELETE query associated with link_identifier.
The & # 8226; bool mysql_close ([resource link_identifier]) returns TRUE on success and FALSE on failure.
The & # 8226; int mysql_errno ([resource link_identifier]) returns the error number of the last MySQL function, or 0 (zero) if there is no error.
The & # 8226; string mysql_error ([resource link_identifier]) returns the error text of the last MySQL function, and if there is no error, returns "(empty string). If no connection resource number is specified, the error message is extracted from the MySQL server using the last successfully opened connection.
The & # 8226; array mysql_fetch_array (resource result [, int result_type]) returns an array generated from the rows retrieved from the result set, or FALSE if there are no more rows.
The & # 8226; bool mysql_free_result (resource result) frees all memory associated with the result identifier result.
The & # 8226; int mysql_num_fields (resource result) returns the number of fields in the result set.
The & # 8226; int mysql_num_rows (resource result) returns the number of rows in the result set. This command is only valid for SELECT statements. To get the number of rows affected by INSERT, UPDATE, or DELETE queries, use mysql_affected_rows().
The & # 8226; resource mysql_query (string query [, resource link_identifier]) sends a query to the currently active database in the server associated with the specified connection identifier. If link_identifier is not specified, the last open connection is used. If there is no open connection, this function attempts to call the mysql_connect() function without arguments to establish a connection and use it. The query results are cached
The code is as follows:
class mysql {
private $db_host; // Database host
private $db_user; // Database login name
private $db_pwd; // Database login password
private $db_name; // The database name
private $db_charset; // Database character encoding
private $db_pconn; // Long connection identity bit
private $debug; // Debugging open
private $conn; // Database connection id
private $msg = ""; // Database manipulation information
// private $sql = ""; // To be performed SQL statements
public function __construct($db_host, $db_user, $db_pwd, $db_name, $db_chaeset = 'utf8', $db_pconn = false, $debug = false) {
$this->db_host = $db_host;
$this->db_user = $db_user;
$this->db_pwd = $db_pwd;
$this->db_name = $db_name;
$this->db_charset = $db_chaeset;
$this->db_pconn = $db_pconn;
$this->result = '';
$this->debug = $debug;
$this->initConnect();
}
public function initConnect() {
if ($this->db_pconn) {
$this->conn = @mysql_pconnect($this->db_host, $this->db_user, $this->db_pwd);
} else {
$this->conn = @mysql_connect($this->db_host, $this->db_user, $this->db_pwd);
}
if ($this->conn) {
$this->query("SET NAMES " . $this->db_charset);
} else {
$this->msg = " Database connection error, error number: " . mysql_errno() . " Error reason: " . mysql_error();
}
$this->selectDb($this->db_name);
}
public function selectDb($dbname) {
if ($dbname == "") {
$this->db_name = $dbname;
}
if (!mysql_select_db($this->db_name, $this->conn)) {
$this->msg = " Database unavailable ";
}
}
public function query($sql, $debug = false) {
if (!$debug) {
$this->result = @mysql_query($sql, $this->conn);
} else {
}
if ($this->result == false) {
$this->msg = "sql Execution error, error number: " . mysql_errno() . " Error reason: " . mysql_error();
}
// var_dump($this->result);
}
public function select($tableName, $columnName = "*", $where = "") {
$sql = "SELECT " . $columnName . " FROM " . $tableName;
$sql .= $where ? " WHERE " . $where : null;
$this->query($sql);
}
public function findAll($tableName) {
$sql = "SELECT * FROM $tableName";
$this->query($sql);
}
public function insert($tableName, $column = array()) {
$columnName = "";
$columnValue = "";
foreach ($column as $key => $value) {
$columnName .= $key . ",";
$columnValue .= "'" . $value . "',";
}
$columnName = substr($columnName, 0, strlen($columnName) - 1);
$columnValue = substr($columnValue, 0, strlen($columnValue) - 1);
$sql = "INSERT INTO $tableName($columnName) VALUES($columnValue)";
$this->query($sql);
if($this->result){
$this->msg = " The data was inserted successfully. The newly inserted id To: " . mysql_insert_id($this->conn);
}
}
public function update($tableName, $column = array(), $where = "") {
$updateValue = "";
foreach ($column as $key => $value) {
$updateValue .= $key . "='" . $value . "',";
}
$updateValue = substr($updateValue, 0, strlen($updateValue) - 1);
$sql = "UPDATE $tableName SET $updateValue";
$sql .= $where ? " WHERE $where" : null;
$this->query($sql);
if($this->result){
$this->msg = " The data was updated successfully. Number of affected rows: " . mysql_affected_rows($this->conn);
}
}
public function delete($tableName, $where = ""){
$sql = "DELETE FROM $tableName";
$sql .= $where ? " WHERE $where" : null;
$this->query($sql);
if($this->result){
$this->msg = " The data was deleted successfully. Number of affected rows: " . mysql_affected_rows($this->conn);
}
}
public function fetchArray($result_type = MYSQL_BOTH){
$resultArray = array();
$i = 0;
while($result = mysql_fetch_array($this->result, $result_type)){
$resultArray[$i] = $result;
$i++;
}
return $resultArray;
}
// public function fetchObject(){
// return mysql_fetch_object($this->result);
// }
public function printMessage(){
return $this->msg;
}
public function freeResult(){
@mysql_free_result($this->result);
}
public function __destruct() {
if(!empty($this->result)){
$this->freeResult();
}
mysql_close($this->conn);
}
}
The calling code is as follows
require_once 'mysql_V1.class.php';
require_once 'commonFun.php';
$db = new mysql('localhost', 'root', '', "test");
//select check
$db->select("user", "*", "username = 'system'");
$result = $db->fetchArray(MYSQL_ASSOC);
print_r($result);
dump($db->printMessage());
//insert increase
//$userInfo = array('username'=>'system', 'password' => md5("system"));
//$db->insert("user", $userInfo);
//dump($db->printMessage());
//update change
//$userInfo = array('password' => md5("123456"));
//$db->update("user", $userInfo, "id = 2");
//dump($db->printMessage());
//delete delete
//$db->delete("user", "id = 1");
//dump($db->printMessage());
//findAll All the query
$db->findAll("user");
$result = $db->fetchArray();
dump($result);
ps, I prefer dump function of tp, so I copy the friendly printing function in commonFun.php file. Change it to print_r() when used.