Use PHP and MySQL

: This article mainly introduces the use of PHP and MySQL. if you are interested in the PHP Tutorial, you can refer to it. PHP supports MySQL at the early stage. in its 2nd version, it contains an API. Because the combination of the two is too common, the extension is enabled by default. However, PHP 5 released an updated MySQL extension called MySQL Improved, or mysqli for short.

Why new extensions? There are two reasons. First, with the rapid development of MySQL, new features such as prepared statements, advanced connection options, and improved security cannot be used by users who rely on the old extension. Second, although the old extension certainly works well, many people think that procedural interfaces are outdated. they prefer object-oriented interfaces because they can not only integrate more closely with other applications, you can also extend this interface as needed. To solve these problems, MySQL developers decided it was time to modify the extension, not only Modifying internal behaviors to improve performance, additional features are introduced to promote the use of available features in the updated version of MySQL.

Key improvements:

# Object-oriented: mysqli extensions are encapsulated into a series of classes to encourage the use of a programming model that is considered more convenient and efficient than the traditional PHP procedural method. But do not worry about those who like the procedural model, because it also provides a traditional procedural interface.

# Prepared statements: prevents SQL injection attacks. It eliminates the overhead and inconvenience of repeated queries.

# Transaction support: Although PHP's initial MySQL extension also supports transaction functions, mysqli extension provides an object-oriented interface for these functions.

# Enhanced debugging: mysqli extension has heard of many methods for debugging and query, making the development process more efficient.

# Embedded server support: MySQL 4.0 release introduces an embedded MySQL server Library. interested users can run a complete MYSQL server in a client application, such as a desktop program. The mysqli extension provides some methods for connecting and operating these embedded MySQL servers.

# Master/slave support: MySQL provides replication support starting from MySQL 3.23.15. With mysqli extension, you can ensure that the query will be forwarded to the master server in a replication configuration.

Those who are familiar with the original MySQL extension will find that the enhanced mysqli extension is very familiar, almost the same naming convention. For example, the database connection function is called mysqli_connect instead of mysql_connect.

1. prerequisites for installation

Since PHP 5, MySQL does not support standard PHP distribution packages. Therefore, you need to explicitly configure PHP to use this extension.

1.1 enable mysqli extension in Linux/UNIX

Use the -- with-mysqli identifier when configuring PHP. It should point to the position of the mysql_config program in MySQL 4.1 and later versions.

1.2 enable mysqli extension on Windows

Modify php. ini to cancel the comment in front of this line: extension = php_mysqli.dll. if not, add this line. Before enabling any extensions, make sure that the extension_dir command of PHP directs to the appropriate directory.

1.3 Use the MYSQL local driver

For a long time, PHP requires that the MySQL client library be installed on the server that runs the PHP program, regardless of whether the MYSQL server is locally or elsewhere. In addition to this requirement, PHP 5.3 introduces a new MySQL Driver called MySQL Native Driver and mysqlnd, which has many advantages over the Driver just mentioned. It is not a new API, but a new "catheter". The existing API (mysql, mysqli, PDO_MySQL) can use this catheter to communicate with a MySQL server. We recommend that you use mysqlnd instead of other drivers (unless you have a good reason ).

To use mysqlnd with an extension, you need to re-compile PHP, for example, -- with-mysqli = mysqlnd. You can also specify a few more parameters, such as %>./configure -- with-mysqli = mysqlnd -- with-pdo-mysql = mysqlnd

The mysqlnd driver also has some limitations. Currently, it does not support compression and SSL.

1.4. Manage user permissions

When a script initializes a connection to the MySQL server, the permissions are passed and verified. The same is true when you submit commands that require permission verification. However, you only need to confirm the execution user during the connection; unless a new connection is established later, the subsequent execution of the script will always be the user.

1.5 sample data

It's easy to add some examples when learning new knowledge. Database: initialize ate; Table: products

Create table products (

Id int not null AUTO_INCREMENT,

Sku VARCHAR (8) not null,

Name VARCHAR (100) not null,

Price DECIMAL (5, 2) not null,

Primary key (id)

)

========================================================== ==========================================

2. use mysqli extension

2.1 establish and disconnect a connection

Connect to the server, select a database, and then close the connection. Object-oriented and procedural styles are acceptable.

Use an object-oriented interface to interact with the MySQL server. First, use the constructor of the mysqli class to instantiate it.

Mysqli ([string host [, string username [, string pswd

[, String dbname [, int port, [string socket])

Users who have used PHP and MySQL in the past will find that many parameters of the constructor are the same as those of the traditional mysql_connect () function.

$ Mysqli = new mysqli ('localhost', 'catalog _ user', 'secret', 'catalog ate ');

If you want to switch to another server or select another database at a certain time point, you can use the connect () and select_db () methods. The parameters of the connect () method are the same as those of the constructor of the mysqli class.

// Instantiate the mysqli class

$ Mysqli = new mysqli ();

// Connect to the database server and select a database

$ Mysqli-> connect ('localhost', 'root', '', 'deleted ');

----------------------------------------------------------------------------------

Or

// Connect to the database server

$ Mysqli = new mysqli ('localhost', 'catalog _ user', 'secret ');

// Select the database

$ Mysqli-> select_db ('initate ');

Once the script is executed, any opened database connection is automatically closed and the resources are restored. However, it is also possible that a page requires multiple database connections during execution, and these connections must be properly closed. Even if only one connection is used, it should be closed at the end of the script. this is a good practice. $ Mysqli-> close ().

2.2 handle connection errors

Connection errors should be carefully monitored and corresponding countermeasures should be taken. The mysqli extension provides some features that can be used to capture error messages. Another method is to use exceptions. For example, mysqli_connect_errno () and mysqli_connect_error () can be used to diagnose and display MySQL connection error information.

2.3. get error messages

2.3.1. get error codes

The errno () method returns the error code generated during the last MySQL function execution. 0 indicates no error.

$ Mysqli = new mysqli ('localhost', 'catalog _ user', 'secret', 'catalog ate ');

Printf ("Mysql error number generated: % d", $ mysqli-> errno );

?>

2.3.2. get error messages

The error () method returns the most recently generated error message. If no error is returned, an empty string is returned. The message language depends on the Mysql database server.

2.4 store connection information in an independent file

In security programming practices, regular password change is a good idea. There are also a lot of scripts to access the database, which is too troublesome to modify one by one. The solution is to have a separate file and include it in your current file if necessary.

For example, you can place the mysqli constructor in a header file (mysql. connect. php ):

$ Mysqli = new mysqli ('localhost', 'catalog _ user', 'secret', 'catalog ate ');

?>

Include it in other files:

Include 'MySQL. connect. php ';

// Begin database selection and queries.

?>

============================ Unfinished, to be continued

The above introduces the use of PHP and MySQL, including some content, hope to be helpful to friends who are interested in PHP tutorials.