PHP connection MySQL Analysis

MySQL: Several steps to working with MySQL database in PHP scripts are as follows:

1. Connect the MySQL database server and determine if the connection is correct

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

3. Execute SQL command

4. Processing result Sets

5. Close the database connection

Connect to the MySQL database server and determine if the connection is correct

Mysql_connect ()

The mysql_connect () function is used to open a connection to the MySQL server. Returns a resource if successful, or false on failure

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 numeric encoding of the error message in the previous MySQL operation

int Mysql_errno ([resource $link _identifier])

Mysql_error ()

The Mysql_error () function returns the text error message generated by the previous MySQL operation. If no connection resource number is specified, the error message is extracted from the MySQL server using the last successfully opened connection

String Mysql_error ([resource $link _identifier])

<?php$link = mysql_connect (' localhost ', ' root ', ' 123456 '); Var_dump ($link);//resource (3, MySQL link) if (! $link) { Die    (' Connection failed: '. mysql_error ());}? >

Select a database and set character sets (optional) mysql_select_db ()

The mysql_select_db () function is used to select the 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 (' Cannot select Database bookstore: '. mysql_error ()); mysql_query (' Set names UTF8 ');//Set character sets (generally not used)?>

Execute SQL command

mysql_query ()

In PHP, the mysql_query () function sends SQL statements to the MySQL server and executes, mysql_query () returns a resource only for Select,show,describe, EXPLAIN, and other statements. Returns false for other types of SQL statements, such as INSERT, UPDATE, DELETE, DROP, and so on, mysql_query () returns TRUE on successful execution, false when error occurs

Mysql_affected_rows ()

The Mysql_affected_rows () function is used to obtain the number of record rows affected by the previous MySQL operation. The execution succeeds returns the number of rows affected, and if the last query fails, the function returns-1

Determining whether a data operation is successful is usually determined by determining whether the value of the Mysql_affected_rows () function is greater than

MYSQL_INSERT_ID ()

The mysql_insert_id () function is used to obtain the ID generated by the insert operation in the previous step

<?php$insert = "INSERT into books (BookName, publisher, author, price, detail) VALUES (' php ', ' electronics Press ', ' Zhang San ', ' 80.00 ', ' PHP-related '), (' ASP ', ' Electronics Press ', ' John Doe ', ' 90.00 ', ' ASP Related '), (' JSP ', ' Electronics Press ', ' Harry ', ' 70.00 ', ' jsp related ') '; $result = mysql_query ($ Insert), if ($result && mysql_affected_rows () > 0) {    //Refresh page two times, equivalent to inserting two data. The page shows that the data record was inserted successfully, and the last inserted data record ID is: 4    echo "Data record inserted successfully, last inserted data record ID is:". mysql_insert_id (). " <br> ";} else{    //If after data table is deleted, display data record insert failed, error number: 1146, error Reason: La table ' bookstore.books ' n ' existe pas    echo "data record insert failed, error number:". Mysql_errno (). ", Error Reason:". Mysql_error (). " <br> ";}? >

In fact, since the 4, 5, and 63 statements are inserted at the same time, the first ID is displayed as 4

Working with result sets

Executing the SELECT query command in a PHP script is also called the mysql_query () function, but unlike executing DML, the return value of the mysql_query () function is a reference pointer (result set) of a PHP resource after the SELECT command is executed. This return value can be used in various result set processing functions to process the various fields of the resulting data table

Mysql_num_fields ()

Mysql_num_fields () function Gets the number of fields in the result set

int Mysql_num_fields (Resource $result)

Mysql_num_rows ()

Mysql_num_rows () function gets the number of rows in the result set

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

Mysql_fetch_row ()

The Mysql_fetch_row () function takes a row from the result set as an enumerated 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 publishing house [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] = = Electronics Press [3] = Harry [4] = + 70.00 [5] = 0 [6] = [7] + JSP related ) Print_r ($row);

MYSQL_FETCH_ASSOC ()

MYSQL_FETCH_ASSOC () function gets a row from the result set as an associative array

Array Mysql_fetch_assoc (Resource $result)

$result = mysql_query ("SELECT * from books"); $assoc = Mysql_fetch_assoc ($result);//array ([id] = 1 [bookname] = P HP [Publisher] = Electronics 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] = Electronics Press [auth or] = Harry [Price] = 70.00 [ptime] + 0 [pic] = [detail] = JSP related) print_r ($ASSOC);

Mysql_fetch_array ()

The Mysql_fetch_array () function takes a row from the result set as an associative array, or as a numeric array, or both.

Mysql_free_result ()

The Mysql_free_result () function is used to release the resulting memory

BOOL Mysql_free_result (Resource $result)

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

To close a 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 that is associated with the specified connection identity. If Link_identifier is not specified, the last open connection is closed

PHP connection MySQL Analysis