How to operate MySQL database with PHP using mysqli-PHP Tutorial-php.cn

This article mainly introduces the method of using PHP to operate the MySQL database using mysqli. Interested friends can refer to it. I hope it will be helpful to everyone.

PHP's mysqli extension provides all the features of its predecessor version. In addition, because MySQL is already a full-featured database server, this adds some new features to PHP. Mysqli also supports these new features.

one. Establishing and Disconnecting Connections

When interacting with a MySQL database, the connection is established first and the connection is disconnected last. This includes connecting to the server and selecting a database, and finally closing the connection. As with almost all features of mysqli, this can be accomplished using an object-oriented approach or a procedural approach.

1. Create a mysqli object

$_mysqli = newmysqli();

2. Connect the host, user, password, and database of MySQL

$_mysqli->connect( 'localhost' , 'root' , 'yangfan' , 'guest' );

3. Create a mysqli object with connection parameters

$_mysqli = newmysqli( 'localhost' , 'root' , 'yangfan' , 'guest ' );

4. Select the database individually

$_mysqli->select_db( 'testguest' );

5. Disconnect MySQL

$_mysqli->close();

##2. Handling connection errors

If it cannot connect to the MySQL database, it is unlikely that the page will continue to do its intended job. Therefore, be sure to monitor connection errors and react accordingly. The Mysqli extension contains many features that can be used to capture error information, such as the mysqli_connect_errno() and mysqli_connect_error() methods.

The mysqli_connect_errno() function returns the error number returned by connecting to the database.

Mysqli_connect_error() function returns the error code returned by connecting to the database.

if(mysqli_connect_errno()) {

echo&#39; 数据库连接错误，错误信息： &#39; .mysqli_connect_error();

exit();

}

Copy after login

The errno attribute returns the error number during database operation.

Theerror attribute returns the error code during database operation.

if( $_mysqli ->errno) {

echo&#39; 数据库操作时发生错误，错误代码是： &#39; . $_mysqli ->error;

}

Copy after login

Three. Interacting with the database

The vast majority of queries are related to creation, retrieval, update, and deletion tasks, which are collectively referred to as CRUD.

1. Obtain data

Most of the work of web page programs is to obtain and format the requested data. To do this, you need to send a SELECT query to the database, then iteratively process the results, output each row to the browser, and output it according to your own requirements.

// 设置一下编码 utf8

$_mysqli->set_charset( "utf8" );

// 创建一句 SQL 语句

$_sql = "SELECT* FROM t g_user" ;

// 执行 sql 语句把结果集赋给$_result

$_result = $_mysqli->query( $_sql );

// 将结果集的第一行输出

print_r( $_result->fetch_row());

// 释放查询内存 ( 销毁 )

$_result->free();

Copy after login

2. Parse the query results

Once the query is executed and the results are ready Set, you can parse the obtained result lines below. You can use multiple methods to get the fields in each row. Which method you choose depends mainly on personal preference, because only the method of referencing the fields differs.

Put the result set into the object

Since you may use the object-oriented syntax of mysqli, you can completely manage the result set in an object-oriented manner. This can be done using the fetch_object() method.

// Pack the result set into an object

$_row = $_reslut->fetch_object();

// Output a field (attribute) in the object

echo $_row->tg_username;

// Traverse all user names

while (!! $_row =$_reslut ->fetch_object()) {

echo$_row ->tg_username. '
' ;

}

Use index arrays and associative arrays

// Pack the result set into an array (index association)

$_row = $_reslut->fetch_array();

// Output the field with index 3 (attribute )

echo $_row [ 3 ];

// Pack the result set into an index array

$_row = $_reslut->fetch_row();

echo $_row [ 3 ];

// Pack the result set into an associative array

$_row = $_reslut->fetch_assoc();

echo $_row ['tg_username' ];

3. Determine the selected rows and affected rows

Usually you want to be able to determine the SELECT query The number of rows returned or affected by an INSERT, UPDATE, or DELET query. We can use the two attributes num_rows and affected_rows

// When using a query, if you want to know how many rows were queried by SELECT, you can use num_rows.

echo $_reslut->num_rows;

// When using queries, if you want to know the number of rows affected by SELECT, INSERT, UPDATE, and DELETE queries, you can use affected_rows; note that it It is an attribute under $_mysqli

echo $_mysqli->affected_rows;

4. 移动指针的操作和获取字段

当你并不想从第一条数据开始获取，或者并不想从第一个字段获取，你可以使用数据指针移动或者字段指针移动的方式调整到恰当的位置。当然，你还可以获取字段的名称及其相关的属性。

// 计算有多少条字段

echo $_reslut->field_count;

// 获取字段的名称

$_field = $_reslut->fetch_field();

echo $_field->name;

// 遍历字段

while (!! $_field =$_reslut ->fetch_field()) {

echo$_field ->name. &#39;<br />&#39; ;

}

// 一次性取得字段数组

print_r( $_reslut->fetch_fields());

// 移动数据指针

$_reslut->data_seek( 5 );

// 移动字段指针

$_reslut->field_seek( 2 );

Copy after login

5. 执行多条 SQL 语句

有的时候，我们需要在一张页面上同时执行多条 SQL 语句，之前的方法就是分别创建多个结果集然后使用。但这样资源消耗很大，也不利于管理。PHP 提供了执行多条 SQL 语句的方法 $_mysqli->multi_query() ;

// 创建多条 SQL 语句

$_sql .="SELECT * FROM tg_user;" ;

$_sql .="SELECT * FROM tg_photo;" ;

$_sql .="SELECT * FROM tg_article" ;

// 开始执行多条 SQL 语句

if ( $_mysqli->multi_query( $_sql )) {

//开始获取第一条 SQL 语句的结果集

$_result= $_mysqli ->store_result();

print_r($_result ->fetch_array());

//将结果集指针移到下一个

$_mysqli->next_result();

$_result= $_mysqli ->store_result();

print_r($_result ->fetch_array());

$_mysqli->next_result();

$_result= $_mysqli ->store_result();

print_r($_result ->fetch_array());

} else {

echo&#39;sql 语句有误！ &#39; ;

}

Copy after login

6. 执行数据库事务

事务 (transaction)是作为整个一个单元的一组有序的数据库操作。如果一组中的所有操作都成功，则认为事务成功，即使只有一个失败操作，事务也不成功。如果所有操作成功完成，事务则提交 (commit) ，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚 (roll back)，该事务所有操作的影响都将取消。

首先，您的 MySQL 是InnoDB 或 BDB 引擎的一种，一般来说，你安装了 AppServ 的集成包，你选择 InnoDB的引擎的数据库即可。如果你建立的表不是 InnoDB ，可以在 phpmyadmin里修改。

// 首先你必须关闭自动提交数据

$_mysqli->autocommit( false );

// 创建一个 SQL 语句，必须同时运行成功，不能出现一个成功，一个失败

$_sql .="UPDATE tg_friend SET tg_state=tg_state+5 WHERE tg_id=1;" ;

$_sql .="UPDATE tg_flower SET tg_flower=tg_flower-5 WHERE tg_id=1;" ;

// 执行两条 SQL 语句

if ( $_mysqli->multi_query( $_sql )) {

//获取第一条 SQL 一影响的行数

$_success= $_mysqli ->affected_rows == 1 ? true : false ;

//下移，第二条 SQL

$_mysqli->next_result();

//获取第二条 SQL 影响的行数

$_success2 = $_mysqli ->affected_rows == 1 ? true : false ;

//判断是否都正常通过了，两个 SQL

if( $_success && $_success2 ) {

$_mysqli->commit();

echo&#39; 完美提交！ &#39; ;

}else {

$_mysqli->rollback();

echo&#39; 程序出现异常！ &#39; ;

}

}

} else {

echo"SQL 语句有误： " . $_mysqli ->errno. $_mysqli ->error;

}

// 最后还必须开启自动提交

$_mysqli->autocommit( true );

Copy after login

以上就是本文的全部内容，希望对大家的学习有所帮助。