Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "how to understand the Mysqli-based database operation class library of PHP database operation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

This kind of library is simple, easy to use, easy for you to modify and improve the function, and can solve the SQL operations performed in most PHP projects.

Preliminary work

First of all, please download this class library M.class.php and then download a Mysqli connection database class library MysqliDb.class.php (package download address)

Create a new includes folder and put the downloaded two class files in it.

Please create a test.php file under the project. Note: UTF-8 file format

Please first fill in the following code to connect to the database according to the condition of your machine:

The copy 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 the class file

}

All right, the above operations are preliminary work, and we will officially enter the explanation of the class library.

Class library explanation

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

The copy code is as follows:

M = new M (); / / this operation means that all functions in M.class.php are encapsulated in the variable $m

Note:

1. The description of the method parameters 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 learning, take a look at the parameters in the file, namely comments.

2. The database structure used in the explanation code is:

The copy 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

The copy 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 of the methods are divided into two types: SQL method and splicing method, which can be seen in the example.

4. M in the following description is called M.class.php file.

Method 1. Insert () add data

All use cases of the Insert method are as follows:

The copy code is as follows:

M-> Insert ("user", null, array ('focus', 'liruxing1715@sina.com',' 23rd, time ()); / / stitching 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 ('focus', 'liruxing1715@sina.com',' 23rd, time ()), true); / / same as above, return last_insert_id (inserted growth id)

M-> Insert ("INSERT INTO `user` (`name`, `email`, `age`, `commit_ time`) VALUES ('Zhang Xiaohua', 'zhangxiaohua@sina.com.cn',' 22 minutes,'" .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 minutes,'" .time (). "), true); / / same as above, return last_insert_id

Note: if the second parameter in the Insert method is null, you can automatically get all the fields except the auto_increment field of the insert table. For more information, please see the M source file. To return the last inserted ID, set the last parameter of the Insert method to true (default is false).

Method 2. Update () modifies the data

All use cases of the update method are as follows:

The copy code is as follows:

M-> Update ("user", array ('name'= >' Li Ruru', 'age'= > 24), "id=1"); / / stitching method, changing the name of the data whose id is 1 to "Li Ru"; the age is "24", and the return value of the method is the number of affected rows

M-> Update ("UPDATE `user`SET `name` ='Li Ruru', `age` = 24 WHERE id = 1"); / / usage of SQL with the same function as above

Method 3. Del () deletes data

All use cases of the Del method are as follows:

The copy code is as follows:

M-> Del ('user',' id=3'); / / stitching method: delete the data with an id of 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 data, delete the data in the `user` table with id of 10, 11, 12, and return the number of affected rows

Method 4. Total () gets the number of records, and the returned values are all int

All use cases of the Del method are as follows:

The copy code is as follows:

M-> Total ('user'); / / stitching method: returns the number of records in the `user` table unconditionally

M-> Total ('user',' id > 1'); / / stitching method: returns the number of records with id greater than 1 in the `user` table, conditional

M-> Total ("SELECT COUNT (*) AS total FROM `user`"); / / SQL method. Note: if you use the SQL method, "AS total" must be used in the statement, otherwise an error will be reported.

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

The copy code is as follows:

M-> IsExists ('user', "`name` =' focus'"); / / stitching method: returns whether the `name` data exists in the `user` table, returns true, and returns false if it does not exist.

Method 6. InsertId () gets the next auto-growing id added under the table. Note that there is no add operation here, just the next growing id.

The copy code is as follows:

Echo $m-> InsertId ('user'); / / get the auto-growing id added under the `user` table

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

All use cases of the GetRow method are as follows:

The copy code is as follows:

$data = $m-> GetRow ("SELECT `name`, email FROM `user` WHERE id=1"); / / SQL method, which returns an one-dimensional array, for example: Array ([name] = > focus [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 for multi-table query

$data = $m-> GetRow ('user',' `name`, email', "id=1"); / / stitching method

$data = $m-> GetRow ('user as u, `class`name`,' u.`name`, u.emailrecoveryc.classclassnamekeeper, "u.id=1 AND u.class_id=c.class_id"); / / stitching method, multi-table query

$data = $m-> GetRow ("SELECT `name`, email FROM `user`"); / / if no condition is specified, all information should be displayed, but the first item will be displayed by default in this method (not recommended! )

$data is a queried one-dimensional array.

Method 8. GetOne () returns a single data

All use cases of the GetOne method are as follows:

The copy code is as follows:

$name = $m-> GetOne ("SELECT `name` FROM `user` WHERE id=1"); / / SQL method, which returns a string, such as focus

$name = $m-> GetOne ("user", "name", "id=1"); / / stitching method, which returns a string, such as focus

Method 9. FetchAll () returns all records

The copy code is as follows:

$data = $m-> FetchAll ("user"); / / returns all records in the `user` table in the form of a two-dimensional array

$data = $m-> FetchAll ("SELECT * FROM `user`"); / / SQL method. The function and return value are the same as above.

$data = $m-> FetchAll ("user", "name,email", "id > 1",'id DESC','2'); / / returns two pieces of data with id > 1, showing only name,email, and sorting in reverse order with id. Note: please note that the last parameter of this method can also be '0d2', which is for paging. If the first page is' 0d2', then the second page is' 2d2'.

/ / this method also supports join table query and multi-table query. Let's take join 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: the location where ON is added in the stitching method

Note: for this FetchAll method, I will write an article that uses this method for perfect paging! Please pay attention.

Method 10. MultiQuery () executes multiple SQL statements

The copy code is as follows:

$sql = "INSERT INTO user (`name`, email, age, class_id, commit_time) VALUES ('Jia Huahua', 'jiahuahua@sina.com.cn',' 22 flowers,'1 flowers,'" .time (). ")") "; / / add a student information named" Jia Huahua "

$sql. = "; DELETE FROM `user` WHERE `name` = 'Jiaojiao'"; / / Delete a student message called "Jiaojiao"

/ / explanation: $sql is concatenated by multiple SQL in English; (semicolon)

$data = $m-> MultiQuery ($sql); / / return true: successful execution; false: failed execution

The class library is over.

At this point, all the functions of the library will be explained. I hope you can take a look at the M file to understand its internal operation mechanism. There will be no slow execution of M files, please rest assured to use them.

If a SQL splicing error occurs during use, the class library will report a friendly error message.

This is the end of the content of "how to understand the Mysqli-based database operation class library of PHP database operations". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.