Basic tutorial on PHP connection and operation of MySQL database, basic tutorial on mysql

Start here

My blog, what is the backend database? That's right, it's MySQL, the script used on the server side is PHP, and the entire framework uses WordPress. PHP and MySQL are like a couple, always working together. Now here, we will gather PHP and summarize the actual use of MySQL, which can also be regarded as an introduction to MySQL development. Regarding the cooperation between PHP and MySQL, there are no more than the following three methods:

1.mysql extension; but its use is no longer recommended;

2.mysqli extension; provides both object-oriented style and process-oriented style; requires MySQL version 4.1 and above;

3. The PDO extension defines a lightweight consistent interface for PHP to access the database; PDO_MYSQL is its specific implementation. We are only concerned with development for now. Since the mysql extension is no longer recommended, I will keep pace with the times and will not make a summary. Mysqli and PDO methods are used more often, so this article will summarize how to use the mysqli extension to connect to the database server, how to query and obtain data, and how to perform other important tasks. The next blog post will summarize the relevant content of PDO.

Use mysqli extension

First look at the test data in the following test database db_test:

1. Establishing and disconnecting

When interacting with a MySQL database, the connection must be established first and disconnected last; this includes connecting to the server and selecting a database, and finally closing the connection and releasing resources. Choosing to use the object-oriented interface to interact with the MySQL server, you first need to instantiate the mysqli class through its constructor.

Once the database is successfully selected, you can then perform database queries against this database. Once the script has finished executing, all open database connections are automatically closed and resources are released. However, it is possible that a page requires multiple database connections during execution, and each connection should be closed appropriately. Even if only one connection is used, it is a good practice to close it at the end of the script. In any case, close() is responsible for closing the connection.

2. Handling connection errors

Of course, if you cannot connect to the MySQL database, it is unlikely that you can continue to complete the expected work on this page. Therefore, be sure to monitor connection errors and react accordingly. The mysqli extension package contains many features that can be used to capture error messages. You can also use exceptions to do this. For example, you can use the mysqli_connect_errno() and mysqli_connect_error() methods to diagnose and display information about a MySQL connection error.

Detailed information about mysqli can be viewed here: http://php.net/manual/zh/book.mysqli.php

Interacting with the database

The vast majority of queries are related to create, get, update, and delete tasks, which are collectively called CRUD. Here we begin to summarize CRUD-related content.

1. Send query to database

The method query() is responsible for sending the query to the database. It is defined as follows:

The optional parameter resultmode can be used to modify the behavior of this method. It accepts two possible values. This article summarizes the differences between the two. http://www.bkjia.com/article/55792.htm; The following is a simple usage example:

2. Insert, update and delete data

Insert, update and delete are completed using insert, update and delete queries, which are actually the same as select queries. The sample code is as follows:

3. Release query memory

Sometimes a particularly large result set may be obtained, and once processing is completed, it is necessary to release the memory requested by the result set. The free() method can complete this task for us. For example:

4. Parse query results

Once the query has been executed and the result set has been prepared, it is time to parse the resulting rows. You can use multiple methods to get the fields in each row. Which method you choose mainly comes down to personal preference, because only the method of referencing the fields differs.

(1) Put the result into the object

Use the fetch_object() method to complete. The fetch_object() method is usually called in a loop. Each call causes the next row in the returned result set to be filled in with an object. This object can then be accessed according to PHP's typical object access syntax. For example:

(2) Use index array and associative array to obtain results

The mysqli extension package also allows the use of associative arrays and index arrays to manage result sets through the fetch_array() method and the fetch_row() method respectively. The fetch_array() method can actually obtain each row of the result set as an associative array, a numeric index array, or both. It can be said that fetch_row() is a subset of fetch_array. By default, fetch_array() will obtain both associative arrays and index arrays. You can pass parameters in fetch_array to modify this default behavior.

MYSQLI_ASSOC, returns rows as an associative array, with keys represented by field names and values ??represented by field contents;
MYSQLI_NUM, returns the row as a numeric index array, the order of its elements is determined by the order of the field names specified in the query;
MYSQLI_BOTH is the default option.

Identify selected rows and affected rows

It is often desirable to be able to determine the number of rows returned by a select query, or the number of rows affected by insert, update, or delete.

(1) Determine the number of rows returned

The num_rows attribute is useful if you want to know how many rows the select query statement returned. For example:

Remember, num_rows is only useful when determining the number of rows obtained by a select query. If you want to obtain the number of rows affected by insert, update, or delete, you must use the affected_rows attribute summarized below.

(2) Determine the number of affected rows

The affected_rows attribute is used to get the number of rows affected by insert, update or delete. See the code above for a code example.

Execute database transaction

There are 3 new methods that enhance PHP’s ability to execute MySQL transactions, namely:

1.autocommit function, enable automatic submission mode;

The autocommit() function controls the behavior of MySQL auto-commit mode. The parameters passed in determine whether to enable or disable auto-commit; pass in TRUE to enable auto-commit, and pass in false to disable auto-commit. Whether enabled or disabled, TRUE will be returned on success and FALSE on failure.

2.commit function, commits the transaction; submits the current transaction to the database, returns TRUE if successful, otherwise returns FALSE.

3.rollback function, rolls back the current transaction, returns TRUE if successful, otherwise returns FALSE.

Regarding transactions, I will continue to summarize them later. Here is a brief summary of these three APIs.

It won’t end

This is just the beginning of learning MySQL and it will not end. Keep up the good work.

$conn=mysql_pconnect("localhost","root","123456");//Open the connection
mysql_select_db("database name",$conn);//Connect to the specified database
mysql_query ("set names utf8");//Set character encoding
$sql="";
$R=mysql_query($sql);//Execute SQL statements to return the result set
while($v= mysql_fetch_array($R)){
echo "field name".$v['title'];
}

$hostname = "localhost";//Host address, generally no need to change
$database = "zx_title";//Database table name
$username = "root" ;//User name, the default is generally root
$password = "123";//Password for mysql database
$conn= mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error() ,E_USER_ERROR);
?>