PHP supports MySQL in the early stages, and in its 2nd edition it contains an API. Because the combination is too common, the extension is turned on by default. However, PHP 5 released an updated MySQL extension, called MySQL improved, abbreviated as MYSQLI.
Why do you want to make a new extension? There are two reasons for this. First of all, the rapid development of MySQL, those who rely on the old extension can not take advantage of new features, such as prepared statements, advanced connectivity options and security improvements. Second, while the old extension is certainly good to use, many people think that the process interfaces are obsolete, and they prefer object-oriented interfaces because they can be integrated more tightly than other applications, as well as expanding the interface as needed. To address these shortcomings, the MySQL developer decided that it was time to retrofit that extension, not only by modifying the internal behavior to achieve performance gains, but also by introducing additional features that facilitated the use of the available features in the newer version of MySQL.
A few key improvements:
# Object-oriented: The mysqli extension is encapsulated into a series of classes, which encourages the use of a programming paradigm that is more convenient and more efficient than a process approach that many think is more traditional than PHP. But those who prefer the process paradigm don't worry, too, because it also provides a traditional, process-oriented interface.
# Prepared statements: Ability to prevent SQL injection attacks. It eliminates the overhead and inconvenience of those queries that are repeatedly executed.
# Transactional support: Although PHP's initial MySQL extension also supports transactional functionality, the MYSQLI extension provides an object-oriented interface for these features.
# Enhanced Debugging Features: The mysqli extension has heard many ways to debug queries, making the development process more efficient.
# Embedded Server support: MySQL 4.0 release introduces an embedded MySQL server library where interested users can run a full MySQL server in a client application, such as a desktop program. The mysqli extension provides a number of methods for connecting and manipulating these embedded MySQL servers.
# Master/Slave support: Starting with MySQL 3.23.15, MySQL provides support for replication. With the mysqli extension, you can guarantee that the query will be transferred to a primary server in a replication configuration.
Users who are familiar with the initial 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 with PHP 5, MySQL support is not tied to standard PHP bundle bundles. Therefore, you need to explicitly configure PHP to take advantage of this extension.
1.1. Enable the mysqli extension in Linux/unix
Use the--WITH-MYSQLI logo when configuring PHP. It should point to the location of the Mysql_config program in MySQL 4.1 and later versions.
1.2. Open the mysqli extension on Windows
To modify the php.ini, cancel the preceding comment: Extension=php_mysqli.dll, if not, add this line. Of course, before you enable any extensions, make sure that the PHP extension_dir directive points to the appropriate directory.
1.3. Using the MySQL local driver
PHP has been asked to install the MySQL client library on the same server as the PHP program, regardless of whether the MySQL server is local or in another location. PHP 5.3 removed the requirement, and it introduced a new MySQL driver called MySQL Native Driver, also called Mysqlnd, which has a lot of advantages over the driver just mentioned. It is not a new API, but a new "catheter", and the existing API (MYSQL,MYSQLI,PDO_MYSQL) can use this catheter 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 several more, such as%>./configure--with-mysqli=mysqlnd--WITH-PDO-MYSQL=MYSQLND
MYSQLND drivers also have some limitations. Currently it does not provide compression and SSL support.
1.4. Manage User Rights
When a script Initializes a connection to the MySQL server, the permissions are passed and checked. The same is true when submitting commands that require permission checks. However, you only need to confirm the execution of the user at the time of connection, and the subsequent execution 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 (+) is not NULL,
Price DECIMAL (5,2) is not NULL,
PRIMARY KEY (ID)
)
===========================================================================
2. Using mysqli Extension
2.1. Establish and disconnect the connection
Connect to the server first, then select a database, and then close the connection. The 2 styles of object-oriented and procedural are all possible.
With the object-oriented interface and the MySQL server interaction, it is first instantiated with the constructor of the Mysqli class.
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 of the parameters of the constructor 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 a different server or choose another database, you can use the Connect () and select_db () methods. The parameters of the Connect () method are the same as the constructors for 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 finishes executing, any open database connections are closed automatically and the resources are restored. However, it is also possible that a page will need to use multiple database connections during the execution process, and these connections need to 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. $mysqli->close ().
2.2. Handling Connection Errors
Connection errors should be carefully monitored and responded to accordingly. The mysqli extension provides some features that can be used to catch error messages, and one way is to take advantage of exceptions. For example, Mysqli_connect_errno () and Mysqli_connect_error () can be used to diagnose and display MySQL connection error messages.
2.3. Get error messages
2.3.1, get error code
The errno () method returns the error code that was generated during the last MySQL function execution. 0 indicates no error.
$mysqli = new mysqli (' localhost ', ' catalog_user ', ' secret ', ' corporate ');
printf ("Mysql error number generated:%d", $mysqli->errno);
?>
2.3.2, getting error messages
The error () method returns the most recently generated fault message. An empty string is returned without error. The message language relies on the Mysql database server.
2.4. Store connection information in a separate file
In the thought of safe programming practice, it is a good idea to change the password regularly. There are a lot of scripts to access the database, a change is too much trouble. The solution is to have 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 the other file:
Include ' mysql.connect.php ';
Begin database selection and queries.
?>
==============================================================================
3. Interacting with the database
3.1. Send queries to the database
Use the query () method. The form is mixed query (string query [, int resultmode]). The optional Resultmode parameter is used to modify the behavior of the method, which has 2 kinds of values:
. Mysqli_store_result: is the default value. Returning the result set as a cache set means that the entire result set will be ready for navigation right away. While the memory consumption is somewhat 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 might want to know how many rows of data were returned from a query, or you might want to immediately transfer to a row in the result set.
. Mysqli_use_result: Returns the result set as a unbuffered set, which means that the data is fetched from the server as needed. For large result sets, this improves performance, but it cannot determine how many rows of data are returned and cannot be transferred to a row.
3.1.1 Getting data
$mysqli = new mysqli (' localhost ', ' catalog_user ', ' secret ', ' corporate ');
Create the query
$query = ' SELECT sku ', ' name ', ' Price ' 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, inserting, updating, deleting 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, assuming that the connecting user provides sufficient credentials, you can fully perform any queries you want to perform, including creating and modifying databases, tables, and indexes, and even completing MySQL administration tasks such as creating and empowering users.
3.1.3, releasing query memory
Sometimes when you get a very large result set, it is necessary to release the memory requested by the result set after the processing is complete, and the free () method will do the work, and then the result set will not be available. $result->free ();
3.2. Parse Query Results
3.2.1, fetching the result into an object
while ($row = $result->fetch_object ())
{
$name = $row->name;
$sku = $row->sku;
$price = $row->price;
printf ("(%s)%s:%s
", $sku, $name, $price)";
}
3.2.2, using indexes and associative arrays to get results
Fetch_array () is both, Fetch_row () is an indexed array.
Their methods are prototyped as follows:
Class Mysqli_result {
Mixed Fetch_array ([int resulttype])
}
Class Mysqli_result {
Mixed Fetch_row ()
}
Where the value of Resulttype can be Mysqli_assoc or Mysqli_num or Mysqli_both.
【
MYSQLI_ASSOC: Field name is key, field content is value.
Mysqli_num: The order is determined by the order of the field names specified by the query. If it is *, all fields are checked, that is, according to the order of the fields in the table definition.
Mysqli_both: is 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 Determining the number of rows selected | Number of rows affected
You want to know the number of rows returned by a select query or the number of rows affected by an 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 is%f product (s) priced above \$15.99.", $result->num_rows);
. The Affected_rows () method is used to determine the number of rows affected by the INSERT, update, and delete queries.
3.4. Use Prepared statements
It is common to repeatedly execute a query that uses different parameter values each time. However, using the traditional query () method coupled with loops is not only expensive (because you need to parse almost the same query to verify legitimacy), and coding is inconvenient (because you need to reconfigure the query for each iteration with the new value), MySQL 4.1 introduces prepared Statements, it can achieve these tasks with much lower overhead and less code.
There are 2 kinds of prepared statements:
. Bound parameters: It allows you to put a query on the MySQL server, simply send the changed data to the server repeatedly, and then integrate into the query execution. For example, suppose you create a Web program that allows users to manage store items, and for a quick start initialization process, you can create a form that accepts up to 20 product names, IDs, prices, and descriptions for this situation.
. Bound results: It allows you to bind PHP variables to the corresponding fields you get, thereby extracting data from the result set using an indexed array or associative array, and then using those variables if necessary.
3.4.1, preparing the statement for execution
Whether you're using Bound-parameter or Bound-result prepared statement, you need to prepare the statement for execution first, using the Prepare () method.
Create a new server connection
$mysqli = new mysqli (' localhost ', ' catalog_user ', ' secret ', ' corporate ');
Create the query and corresponding placeholders
$query = "Select SKU, Name, price, description
From the 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, it needs to be executed. When to execute depends on whether you want to use binding parameters or binding results. If it is the former, the statement is executed after the binding parameter. If the latter, this method is executed before the result of the binding. The execution of statements in 2 ways is done through the Execute () method.
3.4.3, reclaim Prepared Statement resources "with close () method"
3.4.4, binding parameters
When using Bound-parameter prepared statement, 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 data type of the subsequent variables (that is, &var1 、...、 &varn), which is required to ensure that data encoding is most effective when sent to the server. Currently supports 4 types of code.
. I: All INTEGER types
. d:double and FLOAT types
. B:blob types
. S: All other types (including strings)
As an example:
Create a new server connection
$mysqli = new mysqli (' localhost ', ' catalog_user ', ' secret ', ' corporate ');
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, binding 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 top 30 products in a product table. The following code binds the variable $sku, $name, and $price to the retrieved field.
Create a new server connection
$mysqli = new mysqli (' localhost ', ' catalog_user ', ' secret ', ' corporate ');
Create Query
$query = ' SELECT sku ', ' name ', ' Price ' 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, fetching data rows from Prepared statements
The fetch () method gets each row from prepared statement result and assigns the field to the binding result. The prototype is as follows:
Class Mysqli {
Boolean fetch ()
}
============================================================================
4. Perform database transactions
4.1. Turn on auto-commit mode
Class Mysqli {
Boolean autocommit (Boolean mode)
}
TRUE is enabled, FALSE is disabled.
4.2. Submit a transaction
Class Mysqli {
Boolean commit ()
}
4.3. Rolling back a transaction
Class Mysqli {
Boolean rollback ()
}
