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 programs. 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
Historically, PHP has required the MySQL client library to 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 will be 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 you need to 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
[, String Dbname [, int Port, [String Socket]]]]]
The users who have used PHP and MySQL in the past will find many of the constructor function. The parameters 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 completes execution, any open database connections will be automatically closed and the resources will be closed. will be 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 error message
2.3.1. Get 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 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.
?>
============ ================================================== =================
3. Interact with the database
3.1. Send a query to the database
Use the query() method. Its form is mixed query(string query [, int resultmode]). The optional resultmode parameter is used to modify the behavior of this method. It has 2 values:
. MYSQLI_STORE_RESULT: is the default value. Returning the result set as a cached set means that the entire result set is immediately ready for navigation. Although somewhat memory intensive, it allows you to use the entire result set at once, so it is useful when you are trying 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 jump to a specific row in the result set.
. MYSQLI_USE_RESULT: Returns the result set as an unbuffered set, which means that data will be obtained from the server as needed. For large result sets, this improves performance, but it cannot determine how many rows of data are returned, nor can it adjust to a specific row.
3.1.1 Get data
$mysqli = new mysqli('localhost', 'catalog_user', 'secret', 'corporate');

// 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())
                                                                                                                                                                                                                               Insert, update, 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, assuming the connecting user provides sufficient credentials, you are completely You can perform any query you wish, including creating and modifying databases, tables, and indexes, and even complete MySQL administration tasks such as creating and granting permissions to users.

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

3.2. Parse the query results
3.2.1. Fetch the results into the 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. Get results using indexes and associative arrays
fetch_array() is both, fetch_row() is an index array.
Their method prototypes are as follows:
class mysqli_result {
mixed fetch_array ([int resulttype] )
}
class mysqli_result {
mixed fetch_row()
}
The value of resulttype can be MYSQLI_ASSOC or MYSQLI_NUM or MYSQLI_BOTH.
[
MYSQLI_ASSOC: The field name is the key, and the field content is the value.
MYSQLI_NUM: The order is specified by the query. of The order of field names is determined. If it is *, that is, all fields are searched, which is based on the field order in the table definition.
】
$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 rows selected | the 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 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 very common to repeatedly execute a query, using different parameter values ​​each time. However, using the traditional query() method plus a loop is not only expensive (because almost the same query needs to be parsed repeatedly to verify the validity), but also inconvenient to code (because it needs to be re-used with new values ​​for each iteration). Configure queries), MySQL 4.1 introduced prepared statements, which can achieve the above tasks with much lower overhead and less code.

There are 2 types of prepared statements:

. Bound parameters: It allows you to put 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 create a web program that allows users to manage store items. To quickly start the initialization process, you can create a form that accepts the name, ID, price, and description of up to 20 products, which would be suitable for this situation.

. Bound results: It allows you to bind PHP variables to the corresponding fields fetched, thereby extracting data from the result set using indexed arrays or associative arrays, and then using these variables when necessary.

3.4.1. Prepare Statement for execution

Whether you use bound-parameter or bound-result prepared statement, you need to prepare the statement for execution first, that is, use 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 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. When to execute depends on whether you want to use bound parameters or bound results. If it's the former, the statement will be executed after the parameters are bound. If the latter is the case, this method will be executed before the result is bound. The execution of statements in both methods is completed through the execute() method.

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

3.4.4. Bind parameters

When using bound-parameter prepared statement, you need to call the bind_param() method to Variable names are bound to corresponding fields. Its prototype is as follows:

class stmt {
boolean bind_param(string types, mixed &var1 [, mixed &varN])
}

The types parameter represents the data type of each subsequent variable (that is, &var1,..., &varN) , this parameter is required to ensure the most efficient encoding of data when sent to the server. Currently 4 types of codes are supported.

. i: All INTEGER types

.d: DOUBLE and FLOAT types

.b: BLOB types

.s: All other types (including strings)

For 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=?,
                                                                                                   using NULL, sku=? 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 $sku = $skuarray[$x];
$ name = $namearray[$x];
                  $price =  $pricearray[$x];
// Close the connection
$mysqli->close();
?>



3.4.5. Bind variables

When the query is ready and executed, you can bind some variables to in the retrieved field. The bind_result() method is used. Its 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 fields.

 
    // Create a new server connection 
    $mysqli = new mysqli('localhost', 'catalog_user', 'secret', 'corporate'); 
 
    // 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、从 Prepared Statements 获取数据行

    fetch() 方法从 prepared statement result 中获取每一行，并将字段赋值到绑定结果中。其原型如下：

class mysqli { 
    boolean fetch() 
}

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

 

4、执行数据库事务

4.1、开启自动提交模式

class mysqli { 
    boolean autocommit(boolean mode) 
}

传 TRUE 就是启用，FALSE 就是禁用。

 

4.2、提交一个事务

class mysqli { 
    boolean commit() 
}

 

4.3、回滚一个事务

class mysqli { 
    boolean rollback()
}