Use PHP and MySQL

Source: Internet
Author: User
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, PHP5 released an update... PHP that supports MySQL at the early stage and contains an API in its 2nd version. 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.
?>
========================================================== ==============================================
3. interaction with databases
3.1 send queries to databases
Use the query () method. The format is mixed query (string query [, int resultmode]). The optional resultmode parameter is used to modify the behavior of the method. It has two values:
. MYSQLI_STORE_RESULT: The default value. Returning a result set as a cache set means that the entire result set is ready for navigation immediately. Although the memory consumption is a little large, it allows you to use the entire result set immediately, so it is useful when you try to analyze and manage the result set. For example, you may want to know how many rows of data are returned from a query, or you may want to adjust to a row in the result set immediately.
. MYSQLI_USE_RESULT: returns the result set as an unbuffered set, which means that data is obtained from the server as needed. For a large result set, this improves performance, but it cannot decide how many rows of data are returned or be tuned to a certain row.
3.1.1 obtain data

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

// Create the query
$ Query = 'SELECT sku, name, price FROM products ORDER by name ';

// Send the query to MySQL
$ Result = $ mysqli-> query ($ query, MYSQLI_STORE_RESULT );

// Iterate through the result set
While (list ($ sku, $ name, $ price) = $ result-> fetch_row ())
Printf ("(% s) % s: \ $ % s
", $ Sku, $ name, $ price );

?>

3.1.2 insert, update, and delete data
The query () method is also used.
$ Result = $ mysqli-> query ($ query, MYSQLI_STORE_RESULT );
Printf ("% d rows have been deleted.", $ mysqli-> affected_rows );
Of course, if the connected user provides sufficient creden,, you can execute any queries you want to execute, including creating and modifying databases, tables and indexes, or even completing MySQL management tasks, for example, create and grant permissions to users.

3.1.3 release query memory
Sometimes you get a very large result set. after processing, it is necessary to release the memory requested by the result set and use the free () method, then the result set cannot be used. $ Result-> free ();

3.2 parse query results
3.2.1 capture results to objects
While ($ row = $ result-> fetch_object ())
{
$ Name = $ row-> name;
$ Sku = $ row-> sku;
$ Price = $ row-> price;
Printf ("(% s) % s: % s
", $ Sku, $ name, $ price )";
}

3.2.2 obtain results using indexes and associated arrays
Both fetch_array () and fetch_row () are index arrays.
Their method prototype is as follows:
Class mysqli_result {
Mixed fetch_array ([int resulttype])
}
Class mysqli_result {
Mixed fetch_row ()
}
The resulttype value can be MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.

MYSQLI_ASSOC: the field name is the key, and the field content is the value.
MYSQLI_NUM: the sequence is determined by the sequence of specified field names in the query. If it is *, that is, to query all fields, it is based on the field sequence defined in the table.
MYSQLI_BOTH: The default value.
]
$ Query = 'SELECT sku, name FROM products order by name ';
$ Result = $ mysqli-> query ($ query );
While ($ row = $ result-> fetch_array (MYSQLI_ASSOC ))
{
$ Name = $ row ['name'];
$ Sku = $ row ['sku '];
Echo "Product: $ name ($ sku)
";
}
Or
While ($ row = $ result-> fetch_array (MYSQLI_NUM ))
{
$ Sku = $ row [0];
$ Name = $ row [1];
$ Price = $ row [2];
Printf ("(% s) % s: % d
", $ Sku, $ name, $ price );
}

3.3 determine the number of selected rows | affected number of rows

You want to know the number of rows returned by a SELECT query or the number of rows affected by the INSERT, UPDATE, or DELETE query.

The. num_rows () method is used to determine how many rows of data are returned from a SELECT query statement. For example:

$ Query = 'SELECT name FROM products WHERE price> 15.99 ';
$ Result = $ mysqli-> query ($ query );

Printf ("There are % f product (s) priced above \ $15.99.", $ result-> num_rows );

The. affected_rows () method is used to determine the number of rows affected by INSERT, UPDATE, and DELETE queries.

3.4 Use Prepared Statements

It is common to repeatedly execute a query with different parameter values each time. However, using the traditional query () method, coupled with loops, is not only costly (because it is necessary to repeatedly parse almost the same query to check validity ), in addition, encoding is not convenient (because you need to re-configure the query with the new value for each iteration). MySQL 4.1 introduces prepared statements, which can implement the preceding tasks with much lower overhead and less code.

There are two types of prepared statements:

. Bound parameters: it allows you to store a query on the MySQL server. you only need to repeatedly send the changed data to the server and then integrate it into the query for execution. For example, suppose you have created a web program that allows users to manage store items. to quickly start the initialization process, you can create a form, A maximum of 20 product names, IDs, prices, and descriptions are allowed.

. Bound results: it allows you to bind PHP variables to the obtained fields, and then extract data from the result set using the index array or associated array, and then use these variables when necessary.

3.4.1 prepare Statement for execution

Whether you use bound-parameter or bound-result prepared statement, you must first prepare the statement for execution, that is, use the prepare () method.

// Create a new server connection
$ Mysqli = new mysqli ('localhost', 'catalog _ user', 'secret', 'catalog ate ');

// Create the query and corresponding placeholders
$ Query = "SELECT sku, name, price, description
FROM products order by sku ";
// Create a statement object
$ Stmt = $ mysqli-> stmt_init ();

// Prepare the statement for execution
$ Stmt-> prepare ($ query );
.. Do something with the prepared statement

// Recuperate the statement resources
$ Stmt-> close ();

// Close the connection
$ Mysqli-> close ();

?>

3.4.2 execute Prepared Statement

Once the statement is ready, you need to execute it. The execution time depends on whether you want to bind the parameter or bind the result. If it is the former, the statement will be executed after the parameter is bound. If it is the latter, this method will be executed before the binding result. In the two methods, statement execution is completed through the execute () method.

3.4.3 reclaim Prepared Statement resources [use the close () method]

3.4.4. bind parameters

When bound-parameter prepared statement is used, you need to call the bind_param () method to bind the variable name to the corresponding field. The prototype is as follows:

Class stmt {
Boolean bind_param (string types, mixed & var1 [, mixed & varN])
}

The types parameter represents the subsequent variables (that is, & var1 ,..., & varN). This parameter is required to ensure the most effective data encoding when sent to the server. Currently, four types of codes are supported.

. I: all INTEGER types

. D: DOUBLE and FLOAT types

. B: BLOB type

. S: all other types (including strings)

For example:

// Create a new server connection
$ Mysqli = new mysqli ('localhost', 'catalog _ user', 'secret', 'catalog ate ');

// Create the query and corresponding placeholders
$ Query = "insert into products SET id = NULL, sku = ?,
Name = ?, Price =? ";

// Create a statement object
$ Stmt = $ mysqli-> stmt_init ();

// Prepare the statement for execution
$ Stmt-> prepare ($ query );

// Bind the parameters
$ Stmt-> bind_param ('ssd ', $ sku, $ name, $ price );

// Assign the posted sku array
$ Skuarray = $ _ POST ['sku '];

// Assign the posted name array
$ Namearray = $ _ POST ['name'];

// Assign the posted price array
$ Pricearray =$ _ POST ['price'];

// Initialize the counter
$ X = 0;

// Cycle through the array, and iteratively execute the query
While ($ x <sizeof ($ skuarray )){
$ Sku = $ skuarray [$ x];
$ Name = $ namearray [$ x];
$ Price = $ pricearray [$ x];
$ Stmt-> execute ();

}

// Recuperate the statement resources
$ Stmt-> close ();

// Close the connection
$ Mysqli-> close ();

?>

3.4.5 bind variables

When the query is ready and executed, you can bind some variables to the retrieved fields. The bind_result () method is used. The prototype is as follows:

Class mysqli_stmt {
Boolean bind_result (mixed & var1 [, mixed & varN])
}

For example, suppose you want to return a list of the first 30 products in the products table. The following code binds the variables $ sku, $ name, and $ price to the retrieved field.


// Create a new server connection
$ Mysqli = new mysqli ('localhost', 'catalog _ user', 'secret', 'catalog ate ');

// Create query
$ Query = 'SELECT sku, name, price FROM products order by sku ';

// Create a statement object
$ Stmt = $ mysqli-> stmt_init ();

// Prepare the statement for execution
$ Stmt-> prepare ($ query );

// Execute the statement
$ Stmt-> execute ();

// Bind the result parameters
$ Stmt-> bind_result ($ sku, $ name, $ price );

// Cycle through the results and output the data

While ($ stmt-> fetch ())
Printf ("% s, % s, % s
", $ Sku, $ name, $ price );

// Recuperate the statement resources
$ Stmt-> close ();

// Close the connection
$ Mysqli-> close ();

?>

3.4.6. get data rows from Prepared Statements

The fetch () method obtains each row from the prepared statement result and assigns a value to the binding result. The prototype is as follows:

Class mysqli {
Boolean fetch ()
}

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

4. execute database transactions

4.1 enable the automatic submission mode

Class mysqli {
Boolean autocommit (boolean mode)
}

If it is set to TRUE, it is enabled. if it is set to FALSE, it is disabled.

4.2 submit a transaction

Class mysqli {
Boolean commit ()
}

4.3 roll back a transaction

Class mysqli {
Boolean rollback ()
}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.