PHP database operation database operation class library based on Mysqli

This type of library is simple and easy to use, making it easy for you to modify and improve its functions. It can solve most SQL operations performed in PHP projects.

Preliminary work

First of all, please download this class library M.class.php and then download a Mysqli database connection class library MysqliDb.class.php (package download address)
New An includes folder, put the two downloaded class files into it.
Then, please create a test.php file under the project. Note: UTF-8 file format

Please first fill in the following code according to your machine's situation to connect to the database:

Copy code The code is as follows:

header('Content-Type: text/html;Charset=utf-8');
define('ROOT_PATH', dirname(__FILE__));
define('DB_HOST', 'localhost'); //Database server address
define('DB_USER', 'root'); //Database user name
define('DB_PWD', '××× ');//Database password
define('DB_NAME', '×××'); //Database name
define('DB_PORT', '3306'); //Database port
function __autoload ($className) {
require_once ROOT_PATH . '/includes/'. ucfirst($className) .'.class.php'; //Automatically load class files
}

Okay, the above operations are preliminary work, now we will officially enter the explanation of the class library.

Class library explanation

First, we have to instantiate M.class.php. Instantiation is very simple:

Copy code The code is as follows:

$m = new M(); //This step represents M.class. All functions in php are encapsulated in the variable $m

Note:
1. For the method parameter description in the M class library, please go to the M.class.php file to see the detailed comments, which will not be described here. It is recommended that when studying, check the parameters in the file, that is, the comments.
2. The database structure used in the explanation code is:

Copy code The code is as follows:

CREATE TABLE `user` (
`id` int(8) unsigned NOT NULL auto_increment,
`name` varchar(50) default NULL,
`email` varchar(100) default NULL,
`age` smallint(3) default NULL,
`class_id` int( 8) default NULL,
`commit_time` int(10) default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET =utf8

Copy code The code is as follows:

CREATE TABLE `class` (
`class_id` int(8) NOT NULL auto_increment,
`class_name` varchar(100) default NULL,
PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And add a piece of test data.

3. In the M class library, most methods are divided into two types, namely: SQL method; splicing method, which can be seen in the example
4. M in the following description is M.class.php File

Method 1, Insert() Add data

All use cases of the Insert method are as follows:

Copy code The code is as follows:

$m->Insert("user", null, array('Jiaojiao ', 'liruxing1715@sina.com', '23', time())); // Splicing method: add a piece of data to the `user` table, and the return value is the number of rows affected by the database
$m-> ;Insert("user", null, array('Jiaojiao', 'liruxing1715@sina.com', '23', time()), true); // The function is the same as above, returning last_insert_id (inserted growth id)
$m->Insert("INSERT INTO `user` (`name`, `email`, `age`, `commit_time`) VALUES ('Zhang Xiaohua', 'zhangxiaohua@sina.com.cn', ' 22', '".time()."')"); //SQL method, the return value is the number of rows affected by the database
$m->Insert("INSERT INTO `user` (`name`, `email`, `age`, `commit_time`) VALUES ('Zhang Xiaohua', 'zhangxiaohua@sina.com.cn', '22', '".time()."')", true); // Same as above, return last_insert_id

Note: If the second parameter in the Insert method is null, all fields in the inserted table except the auto_increment field can be automatically obtained. For details, please see the M source file; if the return value is the last inserted ID, then please Set the last parameter of the Insert method to true (default is false);

Method 2, Update() to modify data

All use cases of the update method are as follows:

Copy code The code is as follows:

$m->Update("user", array('name'=>'Li Ruru', 'age'=>24), "id=1"); //Splicing method, Modify the name of the data with ID 1 to "Li Ruru"; the age to "24", and the return value of its method is the number of affected rows
$m->Update("UPDATE `user` SET `name`= 'Li Ruru', `age`=24 WHERE id = 1"); //SQL usage, the function is the same as above

Method 3, Del() delete data

All use cases of the Del method are as follows:

Copy code The code is as follows:

$m->Del('user', 'id=3'); //Splicing method: delete the data with id 3 in the `user` table and return the number of affected rows
$m->Del("DELETE FROM `user` WHERE id=4"); //SQL method : Delete the data with id 4 in the `user` table and return the number of affected rows
$m->Del("DELETE FROM `user` WHERE id in (10, 11, 12)"); // SQL method: Delete multiple pieces of data, delete data with IDs 10, 11, and 12 in the `user` table, and return the number of affected rows

Method 4, Total() gets the number of records, the return values are all int

All use cases of the Del method are as follows:

Copy code The code is as follows:

$m->Total('user'); //Splicing method: return The number of records in the `user` table, unconditional
$m->Total('user', 'id>1'); //Splicing method: Return the number of records with id greater than 1 in the `user` table, there are Condition
$m->Total("SELECT COUNT(*) AS total FROM `user`"); //SQL method, note: when using SQL method, "AS total" must be used in the statement, otherwise an error will be reported

Method 5, IsExists() checks whether the data exists, the return value is boolean

Copy code The code is as follows:

$m->IsExists('user', "`name`='focus Jiao'"); //Splicing method: Return whether there is data with `name` as "Jiao Jiao" in the `user` table, return true, if not, return false

Method 6. InsertId() Gets the next added automatic growth id of the table. Note that there is no adding operation here, just getting the next growth id

Copy code The code is as follows:

echo $m->InsertId('user'); //Get `user ` The automatically growing id added under the table

Method 7, GetRow() returns a single piece of data, and the return value is a one-dimensional array

All use cases of GetRow method are as follows:

Copy code The code is as follows:

$data = $m->GetRow("SELECT `name`,email FROM `user` WHERE id=1"); //SQL method, returns one-dimensional array, for example: Array ([name] => Jiaojiao[email] => liruxing1715@sina.com)
$data = $m->GetRow("SELECT u.`name`, u.email, c.class_name FROM `user` u, `class` c WHERE u.class_id=c.class_id AND u.id=1"); / /SQL method, multi-table query
$data = $m->GetRow('user', '`name`,email', "id=1"); //Splicing method
$data = $ m->GetRow('user as u,`class` c', 'u.`name`,u.email,c.class_name', "u.id=1 AND u.class_id=c.class_id"); //Splicing method, multi-table query
$data = $m->GetRow("SELECT `name`,email FROM `user`"); //If no conditions are specified, all information should be displayed, but here The first item will be displayed by default in the method (not recommended!!!)

$data is the one-dimensional array queried.

Method 8, GetOne() returns a single data

All use cases of the GetOne method are as follows:

Copy code The code is as follows:

$name = $m->GetOne("SELECT `name` FROM `user ` WHERE id=1"); //SQL method, returns a string, for example: Jiaojiao
$name = $m->GetOne("user", "name", "id=1"); //Splicing method, returns a string, for example: Jiaojiao

Method 9, FetchAll() returns all records

Copy code The code is as follows:

$data = $m->FetchAll("user"); //Return all records in the `user` table in the form of a two-dimensional array
$data = $m-> FetchAll("SELECT * FROM `user`"); //SQL method, function and return value are the same as above
$data = $m->FetchAll("user", "name,email", "id>1" , 'id DESC', '2'); //Return two pieces of data with id>1, only display name and email, and sort by id in reverse order. Note: Please note that the last parameter of this method can also be '0,2', which is prepared for paging. If the first page is '0,2', then the second page will be '2,2'
//This method also supports joint table query and multi-table query. The following is a joint table query as an example
$data = $m->FetchAll("`user` as u LEFT JOIN `class` as c ON u .class_id=c.class_id", "u.`name`,u.email, c.class_name", "u.id=1"); //Note: In this splicing method, the position where ON is added

Note: For this FetchAll method, I will write an article later on using this method for perfect paging! ! Please pay attention.

Method 10, MultiQuery() executes multiple SQL statements

Copy code The code is as follows:

$sql = "INSERT INTO user (`name`,email, age, class_id, commit_time) VALUES ('Jiahuahua', 'jiahuahua@sina.com.cn', '22', '1', '".time()."')"; //Add a person named "Jiahuahua" Student information
$sql .= ";DELETE FROM `user` WHERE `name`='Jiaojiao'"; //Delete a student information named "Jiaojiao"
//Explanation: $sql is Multiple SQLs are spliced together in English; (semicolon)
$data = $m->MultiQuery($sql); //Returning true means the execution was successful; false means the execution failed

Class library explanation completed

This completes the explanation of all the functions of this class library. I hope you can read more about the M file and understand its internal operating mechanism. There will be no slow execution of M files, so please feel free to use them.
If an SQL splicing error occurs during use, the class library will report a friendly error message.
If you have any questions, please leave a message. Everyone’s criticisms and suggestions are welcome. Come on! Learn good luck.

PHP database operation database operation class library based on Mysqli_PHP tutorial