Backend Development
PHP Tutorial
PHP implements the method of using mysqli to operate MySQL database
PHP implements the method of using mysqli to operate MySQL database
This article mainly introduces the method of using PHP to operate 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' 数据库连接错误，错误信息： ' .mysqli_connect_error();
exit();
}if( $_mysqli ->errno) {
echo' 数据库操作时发生错误，错误代码是： ' . $_mysqli ->error;
}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();
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 objectSince 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 objectecho $_row->tg_username;// Traverse all user nameswhile (!! $_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 $_mysqliecho $_mysqli->affected_rows;
4. 移动指针的操作和获取字段
当你并不想从第一条数据开始获取 ，或者并不想从第一个字段获取 ， 你可以使用数据指针移动或者字段指针移动的方式调整到恰当的位置。 当然 ， 你还可以获取字段的名称及其相关的属性。
// 计算有多少条字段
echo $_reslut->field_count;
// 获取字段的名称
$_field = $_reslut->fetch_field();
echo $_field->name;
// 遍历字段
while (!! $_field =$_reslut ->fetch_field()) {
echo$_field ->name. '<br />' ;
}
// 一次性取得字段数组
print_r( $_reslut->fetch_fields());
// 移动数据指针
$_reslut->data_seek( 5 );
// 移动字段指针
$_reslut->field_seek( 2 );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'sql 语句有误！ ' ;
}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' 完美提交！ ' ;
}else {
$_mysqli->rollback();
echo' 程序出现异常！ ' ;
}
}
} else {
echo"SQL 语句有误： " . $_mysqli ->errno. $_mysqli ->error;
}
// 最后还必须开启自动提交
$_mysqli->autocommit( true );以上就是本文的全部内容，希望对大家的学习有所帮助。
相关推荐：
php、mysql查询当天,查询本周,查询本月的数据实例详解
The above is the detailed content of PHP implements the method of using mysqli to operate MySQL database. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1662
14
1419
52
1311
25
1262
29
1234
24
MySQL and phpMyAdmin: Core Features and Functions
Apr 22, 2025 am 12:12 AM
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
The Continued Use of PHP: Reasons for Its Endurance
Apr 19, 2025 am 12:23 AM
What’s still popular is the ease of use, flexibility and a strong ecosystem. 1) Ease of use and simple syntax make it the first choice for beginners. 2) Closely integrated with web development, excellent interaction with HTTP requests and database. 3) The huge ecosystem provides a wealth of tools and libraries. 4) Active community and open source nature adapts them to new needs and technology trends.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
The Compatibility of IIS and PHP: A Deep Dive
Apr 22, 2025 am 12:01 AM
IIS and PHP are compatible and are implemented through FastCGI. 1.IIS forwards the .php file request to the FastCGI module through the configuration file. 2. The FastCGI module starts the PHP process to process requests to improve performance and stability. 3. In actual applications, you need to pay attention to configuration details, error debugging and performance optimization.
Explain the purpose of foreign keys in MySQL.
Apr 25, 2025 am 12:17 AM
In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.
How to safely store JavaScript objects containing functions and regular expressions to a database and restore?
Apr 19, 2025 pm 11:09 PM
Safely handle functions and regular expressions in JSON In front-end development, JavaScript is often required...
Compare and contrast MySQL and MariaDB.
Apr 26, 2025 am 12:08 AM
The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.
SQL vs. MySQL: Clarifying the Relationship Between the Two
Apr 24, 2025 am 12:02 AM
SQL is a standard language for managing relational databases, while MySQL is a database management system that uses SQL. SQL defines ways to interact with a database, including CRUD operations, while MySQL implements the SQL standard and provides additional features such as stored procedures and triggers.
