Using PHP and MySQL

PHP has supported MySQL since its early days, and included an API in its second version. Because the combination of the two is so common, this extension is enabled by default. However, PHP 5 released a newer MySQL extension called MySQL Improved, or mysqli for short.

Why release a new extension? The reasons are twofold. First, MySQL is evolving rapidly, and users who rely on older extensions are unable to take advantage of new features such as prepared statements, advanced connection options, and security improvements. Second, while the old extension was certainly fine to use, many people considered the procedural interface obsolete and preferred the object-oriented interface for not only tighter integration with other applications but the ability to extend it as needed. interface. To address these shortcomings, the MySQL developers decided it was time to revamp that extension, not only modifying the internal behavior to improve performance, but also introducing additional features that facilitate the use of features available in newer versions of MySQL.

Several key improvements:

# Object-oriented: MySQL extensions are encapsulated into a series of classes, thus encouraging the use of a programming paradigm that many people consider to be more convenient and efficient than PHP's traditional procedural approach. But those who prefer the procedural paradigm don't worry, because it also provides a traditional procedural interface.

# prepared statements: Can prevent SQL injection attacks. It eliminates the overhead and inconvenience of those queries that are executed repeatedly.

# Transaction support: Although PHP's original MySQL extension can also support transaction functions, the mysqli extension provides an object-oriented interface to these functions.

# Enhanced debugging capabilities: The mysqli extension provides many methods for debugging queries, making the development process more efficient.

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

# Master/slave support: Starting from MySQL 3.23.15, MySQL provides support for replication. Using the mysqli extension, you can ensure that queries are routed to the master server in a replicated configuration.

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

1. Prerequisites for installation

Starting from PHP 5, MySQL support is not bundled with the standard PHP distribution package. Therefore, PHP needs to be configured explicitly to take advantage of this extension.

1.1. Enable mysqli extension in Linux/UNIX

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

1.2. To enable the mysqli extension on Windows

You need to modify php.ini and uncomment this line: extension=php_mysqli.dll. If not, add this line. Of course, before enabling any extension, make sure that PHP's extension_dir directive points to the appropriate directory.

1.3. Use MYSQL local driver

For a long time, PHP requires that the MySQL client library be installed on the server running the PHP program, regardless of whether the MYSQL server happens to be local or elsewhere. PHP 5.3 removes this requirement and introduces a new MySQL driver called MySQL Native Driver, also called mysqlnd, which has many advantages over the driver just mentioned. It is not a new API, but a new "conduit" that existing APIs (mysql, mysqli, PDO_MySQL) can utilize to communicate with a MySQL server. It is recommended to use mysqlnd instead of other drivers (unless you have a very good reason).

To use mysqlnd with an extension, you need to recompile PHP, for example: --with-mysqli=mysqlnd. You can also specify a few more, such as %>./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd. The mysqlnd driver also has some restrictions. Currently it does not provide compression or SSL support.

1.4. Managing user permissions

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

1.5. Use sample data

It’s easy to add some examples when learning new knowledge. Database: corporate; 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 connections

First connect to the server, then select a database, and then close the connection. Object-oriented and procedural are both possible styles.

To use the object-oriented interface to interact with the MySQL server, you must first instantiate it using the constructor of the mysqli class.

mysqli([string host [, string username [, string pswd

]                                                                                ) Many parameters of the function are the same as the traditional mysql_connect() function.

$mysqli = new mysqli('localhost', 'catalog_user', 'secret', 'corporate');

If at some point, you want to switch to another server or choose another database, you can use connect() and select_db() method. The parameters of the connect() method are the same as 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', '' , 'corporate');

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

or

// Connect to the database server

$mysqli = new mysqli('localhost', 'catalog_user', 'secret');

// Select the database

$mysqli->select_db('corporate');

Once the script is completed When executed, any open database connections are automatically closed and resources are restored. However, it is also possible that a page needs to use multiple database connections during execution, and these connections need to be closed correctly. Even if only one connection is used, it is a good practice to close it at the end of the script. $mysqli->close().

2.2. Handling connection errors

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

2.3. Get the error message

2.3.1. Get the error code

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

$mysqli = new mysqli('localhost', 'catalog_user', 'secret', 'corporate');

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

?>

2.3.2. Get the error message

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

2.4. Store connection information in a separate file

In terms of secure programming practices, it is a good idea to change passwords regularly. There are also many scripts that need to access the database, and it is too troublesome to modify them one by one. The solution is to store it in a separate file and include it in your current file if necessary.

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

$mysqli = new mysqli('localhost', 'catalog_user', 'secret', ' corporate');

?>

Then include it in other files:

include 'mysql.connect.php';

// begin database selection and queries.

?> ;

==================== Not finished yet, to be continued