PHP connection and operation of MySQL database basic tutorial _ php instance

This article mainly introduces the basic tutorial of connecting to and operating MySQL databases using PHP. This article describes mysql extension, database connection, and database interaction. For more information, see Start from here

What is the background database in my blog? That's right, it's MySQL. the script used on the server is PHP, and the whole framework is WordPress. Like couples, PHP and MySQL always work together. Now, let's take a look at PHP and summarize the actual use of MySQL, which is also counted as the entry to MySQL development. The cooperation between PHP and MySQL involves the following three methods:

1. mysql extension; but not recommended Currently;

2. mysqli extension. It also provides an object-oriented and process-oriented style. the MySQL version is required to be 4.1 or later;

3. the PDO extension defines a lightweight consistent interface for PHP to access the database. PDO_MYSQL is a specific implementation of this interface. Currently, only development is concerned. Because mysql extensions are no longer recommended, I will keep pace with the times and not summarize them. mysql and PDO methods are used more often, 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 execute other important tasks. The next blog will summarize the content related to PDO.

Use mysqli extension

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

The code is as follows:

Mysql> select * from tb_test;
+ ---- + ----------- + ---------- + ------------ +
| Id | firstname | lastname | email | phone |
+ ---- + ----------- + ---------- + ------------ +
| 1 | Young | Jelly | 123@qq.com | 1384532120 |
| 3 | Fang | Jone | 456@qq.com | 1385138913 |
| 4 | Yuan | Su | 789@qq.com | 1385138913 |
+ ---- + ----------- + ---------- + ------------ +
3 rows in set (0.00 sec)

1. establish and disconnect a connection

When interacting with a MySQL database, you must first establish a connection and then disconnect the connection. This includes connecting to the server, selecting a database, closing the connection, and releasing resources. To use an object-oriented interface to interact with the MySQL server, you must first instantiate the mysqli class through its constructor.

The code is as follows:

<? Php
// Instantiate the mysqli class
$ MysqliConn = new mysqli ();
// Connect to the server and select a database
$ MysqliConn-> connect ('127. 0.0.1 ', 'root', 'root', 'DB _ test ');
Printf ("MySQL error number: % d", $ mysqliConn-> errno );
// Or
// $ MysqliConn-> connect ("http: // 127.0.0.1", 'root', 'root ');
// $ MysqliConn-> select_db ('Db _ test ');

// Interact with the database

// Close the connection
$ MysqliConn-> close ();
?>

Once the database is selected, you can query the database. Once the script is executed, all opened database connections are automatically closed and resources are released. However, a page may require multiple database connections during execution, and each connection should be closed as appropriate. Even if only one connection is used, it should be closed at the end of the script. this is a good practice. In all circumstances, close () is responsible for closing the connection.

2. handle connection errors

Of course, if you cannot connect to the MySQL database, it is unlikely that you will continue the expected work on this page. Therefore, be sure to monitor connection errors and respond accordingly. The mysqli extension package contains many features that can be used to capture error messages. you can also use exceptions to achieve 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.

For more information about mysqli, see: http://php.net/manual/zh/book.mysqli.php

Interaction with databases

Most queries are related to creating, obtaining, updating, and deleting tasks, collectively referred to as CRUD. Here we will summarize the content related to CRUD.

1. send a query to the database

Method query () sends the query to the database. It is defined as follows:

The code is as follows:

Mixed mysqli: query (string $ query [, int $ resultmode = MYSQLI_STORE_RESULT])

The optional resultmode parameter can be used to modify the behavior of this method. it accepts two optional values. This article summarizes the differences between the two. Http://www.jb51.net/article/55792.htm?next is a simple example:

The code is as follows:

<? Php
// Instantiate the mysqli class
$ MysqliConn = new mysqli ();

// Connect to the server and select a database
// Incorrect password
$ MysqliConn-> connect ('127. 0.0.1 ', 'root', 'root', 'DB _ test ');
If ($ mysqliConn-> connect_error)
{
Printf ("Unable to connect to the database: % s", $ mysqliConn-> connect_error );
Exit ();
}

// Interact with the database
$ Query = 'SELECT firstname, lastname, email from tb_test ;';

// Send the query to MySQL
$ Result = $ mysqliConn-> query ($ query );

// Iterative processing result set
While (list ($ firstname, $ lastname, $ email) = $ result-> fetch_row ())
{
Printf ("% s's email: % s
", $ Firstname, $ lastname, $ email );
}

// Close the connection
$ MysqliConn-> close ();
?>

2. INSERT, UPDATE, and delete data

Insert, update, and delete operations are performed after the insert, update, and delete queries are completed. they are actually the same as select queries. The sample code is as follows:

The code is as follows:

<? Php
// Instantiate the mysqli class
$ MysqliConn = new mysqli ();

// Connect to the server and select a database
// Incorrect password
$ MysqliConn-> connect ('127. 0.0.1 ', 'root', 'root', 'DB _ test ');
If ($ mysqliConn-> connect_error)
{
Printf ("Unable to connect to the database: % s", $ mysqliConn-> connect_error );
Exit ();
}

// Interact with the database
$ Query = 'SELECT firstname, lastname, email from tb_test ;';
// Send the query to MySQL
$ Result = $ mysqliConn-> query ($ query );

// Iterative processing result set
While (list ($ firstname, $ lastname, $ email) = $ result-> fetch_row ())
{
Printf ("% s's email: % s
", $ Firstname, $ lastname, $ email );
}

$ Query = "delete from tb_test where firstname = 'yuanyuan ';";
$ Result = $ mysqliConn-> query ($ query );

// Tell the user how many rows are affected
Printf ("% d row (s) have been deleted.
", $ MysqliConn-> affected_rows );
// Re-query the result set
$ Query = 'SELECT firstname, lastname, email from tb_test ;';

// Send the query to MySQL
$ Result = $ mysqliConn-> query ($ query );

// Iterative processing result set
While (list ($ firstname, $ lastname, $ email) = $ result-> fetch_row ())
{
Printf ("% s's email: % s
", $ Firstname, $ lastname, $ email );
}
// Close the connection
$ MysqliConn-> close ();
?>

3. release query memory

Sometimes a very large result set may be obtained. 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:

The code is as follows:

// Interact with the database
$ Query = 'SELECT firstname, lastname, email from tb_test ;';

// Send the query to MySQL
$ Result = $ mysqliConn-> query ($ query );

// Iterative processing result set
While (list ($ firstname, $ lastname, $ email) = $ result-> fetch_row ())
{
Printf ("% s's email: % s
", $ Firstname, $ lastname, $ email );
}
$ Result-> free ();

4. parse query results

Once the query is executed and the result set is ready, the obtained results can be parsed below. You can use multiple methods to obtain fields in each row. the method you choose depends on your preferences, because the method for referencing fields is different.

(1) put the result into the object

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

The code is as follows:

// Interact with the database
$ Query = 'SELECT firstname, lastname, email from tb_test ;';

// Send the query to MySQL
$ Result = $ mysqliConn-> query ($ query );

// Iterative processing result set
While ($ row = $ result-> fetch_object ())
{
$ Firstname = $ row-> firstname;
$ Lastname = $ row-> lastname;
$ Email = $ row-> email;
}
$ Result-> free ();

(2) obtain results using index arrays and associated arrays

The mysqli extension package also allows you to use the join array and index array to manage the result set by using the fetch_array () method and the fetch_row () method. The fetch_array () method can actually retrieve all rows of the result set as an associated array, a numeric index array, or both. fetch_row () is a subset of fetch_array. By default, fetch_array () obtains the associated array and index array at the same time. you can input parameters in fetch_array to modify this default behavior.

MYSQLI_ASSOC returns a row as an associated array. The key is represented by the field name and the value is represented by the field content;
MYSQLI_NUM: returns a row as a numeric index array. the element sequence is determined by the sequence of field names specified in the query;
MYSQLI_BOTH is the default option.

Determine the selected row and the affected row

You usually want to determine the number of rows returned by the select query, or the number of rows affected by the insert, update, or delete statements.

(1) determine the number of returned rows

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

The code is as follows:

// Interact with the database
$ Query = 'SELECT firstname, lastname, email from tb_test ;';

// Send the query to MySQL
$ Result = $ mysqliConn-> query ($ query );

// Obtain the number of rows
$ Result-> num_rows;

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

(2) determine the number of affected rows

The affected_rows attribute is used to obtain the number of rows affected by insert, update, or delete. For the sample code, see the above code.

Execute database transactions

There are three new methods to enhance the PHP MySQL transaction execution function, respectively:

1. autocommit function, enabling the automatic submission mode;

The autocommit () function controls the MySQL automatic submission mode. the input parameters determine whether to enable or disable automatic submission. if the input value is TRUE, automatic submission is started. if the input value is false, automatic submission is disabled. Whether enabled or disabled, TRUE is returned for success, and FALSE is returned for failure.

2. the commit function submits transactions. if the current transaction is committed to the database, TRUE is returned. otherwise, FALSE is returned.

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

I will continue to summarize the transactions. here we will briefly summarize these three APIs.

Not End

This is only the beginning of MySQL learning and will not end. Primary encouragement.