PHP connection and operation MySQL Database basics Tutorial _php Instance

Start from here

My blog, what is the background database? Yes, it is MySQL, server-side use of the script is PHP, the entire framework using WordPress. PHP and MySQL are like couples, always working together. Now here, on the collection of PHP, summed up the actual use of MySQL, also counted as the introduction of MySQL development. About PHP and MySQL cooperation, no less than the following three ways:

1.mysql extension, but currently deprecated;

2.mysqli expansion, while providing object-oriented style and process-oriented style; MySQL version 4.1 and above required;

The 3.PDO extension defines a lightweight, consistent interface for PHP access to the database, and Pdo_mysql is a concrete implementation of it. Here for the time being only concerned development. Since MySQL extensions have not been recommended, I will keep up with the times, and Mysqli and PDO use more, so this article will summarize how to use mysqli extensions to connect to the database server, how to query and get data, and how to perform other important tasks. The next blog post will summarize the content of PDO.

Using the mysqli extension

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

Copy Code code 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

When interacting with a MySQL database, you first establish a connection and finally disconnect; This includes connecting to the server and selecting a database, and finally shutting down the connection and releasing resources. Choose to interact with the MySQL server using an object-oriented interface, first by instantiating the Mysqli class with its constructor.

Copy Code code 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 ');

Interacting with a database

Close connection
$mysqliConn->close ();
?>

Once you have successfully selected the database, you can then perform a database query on the database. Once the script completes, all open database connections are automatically closed and resources are freed. However, it is possible for a page to have multiple database connections during execution, and each connection should be properly closed. Even if you use only one connection, you should close it at the end of the script, which is a good practice. 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 will be able to continue with the expected work on this page. Therefore, be sure to monitor connection errors and respond accordingly. The Mysqli expansion pack contains many features that can be used to catch error messages, and you can 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.

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

Interacting with a database

Most queries are related to creating, fetching, updating, and deleting tasks, which are collectively known as CRUD. Here we start summarizing the crud-related content.

1. Send a query to the database

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

Copy Code code as follows:

Mixed Mysqli::query (string $query [, int $resultmode = Mysqli_store_result])

Optional parameter Resultmode can be used to modify the behavior of this method, which accepts two desirable values. This article sums up the difference between the two. http://www.jb51.net/article/55792.htm; Here is a simple example of use:

Copy Code code as follows:

<?php
Instantiate the Mysqli class
$mysqliConn = new mysqli ();

Connect to the server and select a database
Bad password
$mysqliConn->connect (' 127.0.0.1 ', ' root ', ' root ', ' db_test ');
if ($mysqliConn->connect_error)
{
printf ("Unable to connect to" database:%s, $mysqliConn->connect_error);
Exit ();
}

Interacting with a database
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send query to MySQL
$result = $mysqliConn->query ($query);

Iterative processing of result sets
while (list ($firstname, $lastname, $email) = $result->fetch_row ())
{
printf ("%s ' s email:%s<br/>", $firstname, $lastname, $email);
}

Close connection
$mysqliConn->close ();
?>

2. Inserting, updating, and deleting data

Inserts, updates, and deletes are done using the INSERT, UPDATE, and delete queries, which are actually the same as the select query. The sample code is as follows:

Copy Code code as follows:

<?php
Instantiate the Mysqli class
$mysqliConn = new mysqli ();

Connect to the server and select a database
Bad password
$mysqliConn->connect (' 127.0.0.1 ', ' root ', ' root ', ' db_test ');
if ($mysqliConn->connect_error)
{
printf ("Unable to connect to" database:%s, $mysqliConn->connect_error);
Exit ();
}

Interacting with a database
$query = ' SELECT FirstName, LastName, email from tb_test; ';
Send query to MySQL
$result = $mysqliConn->query ($query);

Iterative processing of result sets
while (list ($firstname, $lastname, $email) = $result->fetch_row ())
{
printf ("%s ' s email:%s<br/>", $firstname, $lastname, $email);
}

$query = "Delete from tb_test where FirstName = ' Yuan ';";
$result = $mysqliConn->query ($query);

Tell the user how many rows are affected
printf ("%d row (s) have been deleted.<br/>", $mysqliConn->affected_rows);
Requery result set
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send query to MySQL
$result = $mysqliConn->query ($query);

Iterative processing of result sets
while (list ($firstname, $lastname, $email) = $result->fetch_row ())
{
printf ("%s ' s email:%s<br/>", $firstname, $lastname, $email);
}
Close connection
$mysqliConn->close ();
?>

3. Free query Memory

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

Copy Code code as follows:

Interacting with a database
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send query to MySQL
$result = $mysqliConn->query ($query);

Iterative processing of result sets
while (list ($firstname, $lastname, $email) = $result->fetch_row ())
{
printf ("%s ' s email:%s<br/>", $firstname, $lastname, $email);
}
$result->free ();

4. Parse Query Results

Once you have executed the query and prepared the result set, you can resolve the resulting row of the obtained results. You can use more than one method to get the fields in each row, depending on your preference, depending on the method of referencing the field.

(1) Placing the result in the object

Use the Fetch_object () method to complete. The Fetch_object () method is typically called in a loop, and each invocation causes the next row in the returned result set to be populated with an object, which can then be accessed according to the typical PHP object access syntax. For example:

Copy Code code as follows:

Interacting with a database
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send query to MySQL
$result = $mysqliConn->query ($query);

Iterative processing of result sets
while ($row = $result->fetch_object ())
{
$firstname = $row->firstname;
$lastname = $row->lastname;
$email = $row->email;
}
$result->free ();

(2) using indexed arrays and associative arrays to get results

The Mysqli expansion pack also allows the Fetch_array () method and the Fetch_row () method to manage the result set separately using associative arrays and indexed arrays. The Fetch_array () method can actually fetch each row of the result set as an associative array, a numeric index array, or both, so that Fetch_row () is a subset of Fetch_array. By default, Fetch_array () gets both an associative array and an array of indexes, which can be passed in Fetch_array to modify the default behavior.

Mysqli_assoc, the row is returned as an associative array, the key is represented by the field name, and the value is represented by the field contents;
Mysqli_num, returns the row as a numeric indexed array, whose element order is determined by the order of the field names specified in the query;
Mysqli_both, is the default option.

Determine the selected row and affected rows

You typically want 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 property is useful if you want to know how many rows are returned by a SELECT query statement. For example:

Copy Code code as follows:

Interacting with a database
$query = ' SELECT FirstName, LastName, email from tb_test; ';

Send query to MySQL
$result = $mysqliConn->query ($query);

Get number of rows
$result->num_rows;

Remember that num_rows is useful only when determining the number of rows fetched by a select query, and if you want to get the number of rows affected by an INSERT, update, or delete, use the Affected_rows attribute summarized below.

(2) Determine the number of rows affected

The Affected_rows property is used to get the number of rows affected by the INSERT, update, or delete. Code examples see the code above.

Performing Database transactions

There are 3 new ways to enhance PHP's ability to perform MySQL transactions, respectively:

1.autocommit function, enable autocommit mode;

The autocommit () function controls the behavior of the MySQL autocommit mode, which determines whether to start or disable autocommit by the parameters passed in, and when true, initiates autocommit and, in false, disables autocommit. If enabled or disabled, it returns true on success and false when it fails.

2.commit function, commits the transaction, submits the current transaction to the database, returns True when it succeeds, or returns false.

3.rollback function that rolls back the current transaction, returns True when it succeeds, or returns false.

About the transaction, I will continue to summarize later, here is a brief summary of these three APIs.

It's not over.

This is just the beginning of MySQL learning, will not end. And then the next.