Mysql extension functions of PHP and phpmysql functions of the frontend

Mysql extension functions of PHP and phpmysql functions of the frontend
* Directory [1] connect to database [2] Use Database [3] Execute SQL Query [4] operation result set [5] Before closing connection

Because of its small size, fast speed, and low total cost of ownership, especially with open source code, mysql is selected as the website database for many small and medium-sized websites to reduce the total cost of ownership. The database system solution that combines the mysql database management system with the php script language is being adopted by more and more websites. Among them, the LAMP (linux + apche + mysql + php) mode is the most popular.

PHP has standard functions used to operate databases. mysqli is newly added in PHP5 and is an improvement to mysql extension. However, due to historical issues, many old projects are extended using mysql in PHP4. If secondary development is performed on the original project, mysql extended functions are required. For a newly designed project, mysqli extension or PDO technology is recommended. This article mainly introduces mysql expansion functions in PHP.

 

Summary

Follow these steps in the PHP script to operate the MySQL database:

1. Connect to the MySQL database server and check whether the connection is correct.

2. Select a database and set the character set (optional)

3. Execute SQL commands

4. processing result set

5. Close database connection

 

Step 1: connect to the MySQL database server and check whether the connection is correct

Mysql_connect ()

The mysql_connect () function is used to open a connection to the MySQL server. If the call succeeds, a resource is returned, or FALSE is returned if the call fails.

resource mysql_connect ([ string $server [, string $username [, string $password [, bool $new_link [, int $client_flags ]]]]] )

Mysql_errno ()

　　The mysql_errno () function is used to return the numerical encoding of the error message in the previous MySQL operation.

int mysql_errno ([ resource $link_identifier ] )

Mysql_error ()

The mysql_error () function is used to return text error messages generated by the previous MySQL operation. If no connection resource number is specified, the error message is extracted from the MySQL server using the previous successfully opened connection.

string mysql_error ([ resource $link_identifier ] )

<? Php $ link = mysql_connect ('localhost', 'root', '000000'); var_dump ($ link); // resource (3, mysql link) if (! $ Link) {die ('Connection failed: '. mysql_error () ;}?>

 

Step 2: select a database and set the character set (optional)

Generally, the database creation work is first established by the database administrator (DBA), and then used by PHP programmers in the script. For example, create a database named bookstore

After using the PHP script to establish a connection with the mysql server, to avoid specifying the target database for every call to the mysql extension function of PHP, it is best to use the mysql_select_db () function to select a default database for subsequent operations, this function is similar to the SQL command "USE bookstore ".

Mysql_select_db ()

The mysql_select_db () function is used to select a MySQL database.

bool mysql_select_db ( string $database_name [, resource $ link_identifier ] )

<? Php $ link = mysql_connect ('localhost', 'root', 'zhiaihebe0123 '); var_dump ($ link); // resource (3, mysql link) if (! $ Link) {die ('Connection failed :'. mysql_error ();} mysql_select_db ('bookstore', $ link) or die ('database bookstore :'. mysql_error (); mysql_query ('set names utf8'); // set the character set (not commonly used)?>

 

Step 3: run the SQL command

First, create a books data table in the bookstore database.

CREATE TABLE books(    id INT NOT NULL AUTO_INCREMENT,    bookname VARCHAR(80) NOT NULL DEFAULT '',    publisher VARCHAR(60) NOT NULL DEFAULT '',    author VARCHAR(20) NOT NULL DEFAULT '',    price DOUBLE(5,2) NOT NULL DEFAULT 0.00,    ptime INT NOT NULL DEFAULT 0,    pic CHAR(24) NOT NULL DEFAULT '',    detail TEXT,    PRIMARY KEY(id));));

In PHP, as long as the SQL command is passed to the mysql_query () function as a string, it will be sent to the MYSQL server and executed

Mysql_query ()

The mysql_query () function is used to send a MySQL query. Mysql_query () returns only one resource for SELECT, SHOW, DESCRIBE, EXPLAIN, and other statements. If an error occurs in the query, FALSE is returned. For other types of SQL statements, such as INSERT, UPDATE, such as DELETE and DROP. If mysql_query () is executed successfully, TRUE is returned. If an error occurs, FALSE is returned.

resource mysql_query ( string $query [, resource $link_identifier = NULL ] )

The three INSERT statements to be inserted are declared as a string.

$ Insert = "insert into books (bookname, publisher, author, price, detail) values ('php', 'electronics industry publishers ', 'James 3', '80. 00 ', 'php relevance'), ('asp ', 'electronics Industry Publishing House', 'Lee 4', '90. 00', 'asp-related '), ('jsps', 'electronics industry publishers ', 'wang 5', '70. 00', 'jsp related ')";

Use the mysql_query () function to send the INSERT statement. If the INSERT statement is successful, true is returned. If the INSERT statement fails, false is returned.

$result = mysql_query($insert);var_dump($result);

Mysql_affected_rows ()

The mysql_affected_rows () function is used to obtain the number of rows affected by the previous MySQL operation. If the execution succeeds, the number of affected rows is returned. If the last query fails, the function returns-1.

int mysql_affected_rows ([ resource $link_identifier = NULL ] )

var_dump(mysql_affected_rows());//int3 

Generally, you can determine whether the data operation is successful by determining whether the value of the mysql_affected_rows () function is greater than 0.

Mysql_insert_id ()

The mysql_insert_id () function is used to obtain the ID generated by the previous INSERT operation.

int mysql_insert_id ([ resource $link_identifier ] )

<? Php $ insert = "insert into books (bookname, publisher, author, price, detail) values ('php', 'electronics Industry Publishing House ', 'James 3', '80. 00 ', 'php relevance'), ('asp ', 'electronics Industry Publishing House', 'Lee 4', '90. 00', 'asp-related '), ('jsps', 'electronics industry publishers ', 'wang 5', '70. 00', 'jsp related ') "; $ result = mysql_query ($ insert); if ($ result & mysql_affected_rows ()> 0) {// After refreshing the page twice, it is equivalent to inserting data twice. The page shows that the data record is successfully inserted. The last inserted Data Record id is 4 echo. The data record is inserted successfully. The last inserted Data Record id is :". mysql_insert_id (). "<br>" ;}else {// if the data table is deleted, the insertion of the data record fails. Error code: 1146. Error cause: La table 'bookstore. books 'n' existe pas echo "data record insertion failed, error code :". mysql_errno (). ", error cause :". mysql_error (). "<br>" ;}?>

In fact, the last id should be 6, but because the three statements 4, 5, and 6 are inserted at the same time, the first id is 4.

Next, change the author of the record with id 4

$ Result = mysql_query ("UPDATE books SET author = 'xiaobai' WHERE id = '4'"); if ($ result & mysql_affected_rows ()> 0) {echo "data record modification succeeded <br>" ;}else {echo "data record modification failed, error code :". mysql_errno (). ", error cause :". mysql_error (). "<br> ";}

Next, delete the record written by Li Si.

$ Result = mysql_query ("delete from books WHERE author = 'lily'"); if ($ result & mysql_affected_rows ()> 0) {echo "data record deleted successfully <br>" ;}else {echo "failed to delete data record, error code :". mysql_errno (). ", error cause :". mysql_error (). "<br> ";}

 

Step 4: process the result set

Executing the SELECT query command in a PHP script also calls the mysql_query () function. However, unlike executing DML, after executing the SELECT command, mysql_query () the return value of a function is a reference pointer (result set) of a PHP resource ). This return value can be used in various result set processing functions to process each field in the result table.

Mysql_num_fields ()

The number of fields in the result set obtained by the mysql_num_fields () function.

int mysql_num_fields ( resource $result )

Mysql_num_rows ()

The number of rows in the result set obtained by the mysql_num_rows () function.

int mysql_num_rows ( resource $result )

$result = mysql_query("SELECT * FROM books");$rows = mysql_num_rows($result);$cols = mysql_num_fields($result);var_dump($rows,$cols);//int 4 int 8

The results show that the result set has four rows and eight columns.

 

To access data in the result set, you can select either of the four functions mysql_fetch_row (), mysql_fetch_assoc (), mysql_fetch_array (), and mysql_fetch_object ().

Mysql_fetch_row ()

The mysql_fetch_row () function retrieves a row from the result set as an enumeration array.

array mysql_fetch_row ( resource $result )

To access data in the result set, you can select either of the four functions mysql_fetch_row (), mysql_fetch_assoc (), mysql_fetch_array (), and mysql_fetch_object ().

Mysql_fetch_row ()

The mysql_fetch_row () function retrieves a row from the result set as an enumeration array.

array mysql_fetch_row ( resource $result )

$ Result = mysql_query ("SELECT * FROM books"); $ row = mysql_fetch_row ($ result ); // Array ([0] => 1 [1] => PHP [2] => Electronic Industry Press [3] => Zhang San [4] => 80.00 [5] => 0 [6] => [7] => PHP related) print_r ($ row); $ row = mysql_fetch_row ($ result ); // Array ([0] => 3 [1] => JSP [2] => Electronic Industry Press [3] => Wang Wu [4] => 70.00 [5] => 0 [6] => [7] => JSP related) print_r ($ row );

Mysql_fetch_assoc ()

The mysql_fetch_assoc () function retrieves a row from the result set as an associated array.

array mysql_fetch_assoc ( resource $result )

$ Result = mysql_query ("SELECT * FROM books"); $ assoc = mysql_fetch_assoc ($ result ); // Array ([id] => 1 [bookname] => PHP [publisher] => Electronic Industry Press [author] => Zhang San [price] => 80.00 [ptime] => 0 [pic] => [detail] => PHP related) print_r ($ assoc); $ assoc = mysql_fetch_assoc ($ result ); // Array ([id] => 3 [bookname] => JSP [publisher] => Electronic Industry Press [author] => Wang Wu [price] => 70.00 [ptime] => 0 [pic] => [detail] => JSP related) print_r ($ assoc );

Mysql_fetch_array ()

The mysql_fetch_array () function retrieves a row from the result set as an associated array, or an array of numbers, or both. The second optional parameter result_type in mysql_fetch_array () is a constant and can accept the following values: MYSQL_ASSOC, MYSQL_NUM, and MYSQL_BOTH. The default value is MYSQL_BOTH.

array mysql_fetch_array ( resource $result [, int $ result_type ] )

$ Result = mysql_query ("SELECT * FROM books"); $ array = mysql_fetch_array ($ result ); // Array ([0] => 1 [id] => 1 [1] => PHP [bookname] => PHP [2] => Electronic Industry Press [publisher] => Electronic Industry Press [3] => Zhang San [author] => Zhang San [4] => 80.00 [price] => 80.00 [5] => 0 [ptime] => 0 [6] => [pic] => [7] => PHP related [detail] => PHP related) print_r ($ array); $ array = mysql_fetch_array ($ result ); // Array ([0] => 3 [id] => 3 [1] => JSP [bookname] => JSP [2] => Electronic Industry Press [publisher] => Electronic Industry Press [3] => Wang Wu [author] => Wang Wu [4] => 70.00 [price] => 70.00 [5] => 0 [ptime] => 0 [6] => [pic] => [7] => JSP-related [detail] => JSP-related) print_r ($ array );

Mysql_fetch_object ()

The mysql_fetch_object () function retrieves a row from the result set as an object.

object mysql_fetch_object ( resource $result )

$ Result = mysql_query ("SELECT * FROM books"); $ object = mysql_fetch_object ($ result ); // stdClass Object ([id] => 1 [bookname] => PHP [publisher] => Electronic Industry Press [author] => Zhang San [price] => 80.00 [ptime] => 0 [pic] => [detail] => PHP related) print_r ($ object); $ object = mysql_fetch_object ($ result ); // stdClass Object ([id] => 3 [bookname] => JSP [publisher] => Electronic Industry Press [author] => Wang Wu [price] => 70.00 [ptime] => 0 [pic] => [detail] => JSP related) print_r ($ object );

For the above four functions, the default Pointer Points to the first row record. After a row of records is obtained, the pointer is automatically moved down. If it is the last configuration, the function returns false. Generally, functions like mysql_fetch_assoc () that return joined arrays are more commonly used.

Mysql_data_seek ()

The mysql_data_seek () function can move the internal result pointer.

[Note] $ row_number starts from 0

bool mysql_data_seek ( resource $result , int $row_number )

$ Result = mysql_query ("SELECT * FROM books"); $ assoc = mysql_fetch_assoc ($ result); mysql_data_seek ($ result, 2); $ assoc = mysql_fetch_assoc ($ result ); array ([id] => 4 [bookname] => PHP [publisher] => Electronic Industry Press [author] => [price] => 80.00 [ptime] => 0 [pic] => [detail] => PHP related) print_r ($ assoc); mysql_data_seek ($ result, 0); $ assoc = mysql_fetch_assoc ($ result ); // Array ([id] => 1 [bookname] => PHP [publisher] => Electronic Industry Press [author] => Zhang San [price] => 80.00 [ptime] => 0 [pic] => [detail] => PHP related) print_r ($ assoc );

The following uses the while loop and mysql_fetch_assoc () functions to display the result set in a table.

<Style>. table {border: 1px solid black; border-collapse: collapse; table-layout: fixed ;}</style> $ result = mysql_query ("SELECT id, bookname, publisher, author, price FROM books "); echo '<table border =" 1 "width =" 800 "class =" table ">'; echo '<tr> '; echo '<th> NO. </th>'; echo '<th> title </th>'; echo '<th> press </th> '; echo '<th> author </th>'; echo '<th> price </th>'; echo '</tr> '; while ($ assoc = mysql_fetch_assoc ($ result) {echo '<tr>'; echo "<td >{$ assoc ['id']} </td> "; echo "<td >{$ assoc ['bookname']} </td>"; echo "<td >{$ assoc ['her her ']} </td> "; echo "<td >{$ assoc ['author']} </td>"; echo "<td >{$ assoc ['price']} </td> "; echo '</tr>';} echo '</table> ';

Mysql_free_result ()

The mysql_free_result () function is used to release the result memory.

bool mysql_free_result ( resource $result )

Mysql_free_result () is called only when considering how much memory will be occupied when a large result set is returned. All associated memory will be automatically released after the script ends.

 

Step 5: Close the database connection

Mysql_close ()

The mysql_close () function is used to close the MySQL connection.

bool mysql_close ([ resource $link_identifier = NULL ] )

Mysql_close () closes the non-persistent connection to the MySQL server associated with the specified connection ID. If link_identifier is not specified, the last opened connection is closed.

Therefore, a complete php program for operating database extension functions is as follows:

<? Php // connect to the database $ link = mysql_connect ('localhost', 'root', '*******'); if (! $ Link) {die ('Connection failed :'. mysql_error ();} // select the database mysql_select_db ('bookstore', $ link) or die ('the database bookstore cannot be selected :'. mysql_error (); // execute the SQL command $ insert = "insert into books (bookname, publisher, author, price, detail) values ('php', 'electronics Industry Publishing House ', 'zhang san', '80. 00 ', 'php relevance'), ('asp ', 'electronics Industry Publishing House', 'Lee 4', '90. 00', 'asp-related '), ('jsps', 'electronics industry publishers ', 'wang 5', '70. 00', 'jsp related ') "; $ result = mysql_query ($ insert); // operation result set $ result = mysql_query (" SELECT id, boo Kname, publisher, author, price FROM books "); echo '<table border =" 1 "width =" 800 "class =" table "> '; echo '<tr>'; echo '<th> NO. </th>'; echo '<th> title </th> '; echo '<th> press </th>'; echo '<th> author </th>'; echo '<th> price </th> '; echo '</tr>'; while ($ assoc = mysql_fetch_assoc ($ result) {echo '<tr> '; echo "<td >{$ assoc ['id']} </td>"; echo "<td >{$ assoc ['bookname']} </td> "; echo "<td >{$ assoc ['her her ']} </td>"; echo "<td> {$ assoc ['Author']} </td> "; echo" <td >{$ assoc ['price']} </td> "; echo '</tr> ';} echo '</table>'; // release the result set mysql_free_result ($ result); // close the database connection mysql_close ($ link);?>