Tips on using PHP database operation functions

PHP is a popular server-side scripting language that can be used to build various types of web applications, including dynamic websites, web applications, and e-commerce sites. The database is the core of any ambitious web application, used to store and manage the data required by the application.

PHP has many functions and extensions for interacting with databases. In this article, we will introduce some tips on using PHP database operation functions to help you better use these functions to interact with the database.

1. Connecting to the database

The most commonly used function to connect to the database using PHP is mysqli_connect(). The prototype of this function is:

mysqli mysqli_connect ( string $host , string $username , string $passwd , string $dbname [, int $port = 3306 [, string $socket = '' ]] )

where $host represents the host name or IP address of the database, $username and $passwd are the database username and password, $dbname is the name of the database to be connected, and $port is the port number. , $socket is the socket file.

Connect to database example:

// 连接数据库
$mysqli = mysqli_connect("localhost", "root", "password", "mydatabase");

// 检查连接是否成功
if (!$mysqli) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

2. Execute SQL statements

Once you successfully connect to the database, you can execute a variety of SQL statements. There are two basic ways to execute SQL statements using PHP: mysqli_query() and mysqli_prepare().

2.1 mysqli_query()

mysqli_query() function prototype is as follows:

mixed mysqli_query ( mysqli $link , string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )

Among them, $link represents the mysqli object connected to the database, and $query is the SQL statement to be executed. , $resultmode specifies the type of result set.

Examples of executing SQL query statements:

$sql = "SELECT id, name FROM users";
$result = mysqli_query($mysqli, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 results";
}

Examples of executing SQL insert, update and delete statements:

// 插入数据
$sql = "INSERT INTO users (name, age) VALUES ('John', 25)";
if (mysqli_query($mysqli, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
}

// 更新数据
$sql = "UPDATE users SET age=30 WHERE name='John'";
if (mysqli_query($mysqli, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($mysqli);
}

// 删除数据
$sql = "DELETE FROM users WHERE id=3";
if (mysqli_query($mysqli, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($mysqli);
}

2.2 mysqli_prepare()

mysqli_prepare() function Used to prepare SQL query statements, but not directly execute it. This can help prevent SQL injection attacks. You need to bind parameters through the mysqli_stmt_bind_param() function and execute the query using the mysqli_stmt_execute() function.

The mysqli_prepare() function prototype is as follows:

mysqli_stmt mysqli_prepare ( mysqli $link , string $query )

Among them, $link represents the mysqli object connected to the database, and $query is the SQL query statement to be executed.

Execute SQL query statement example:

$sql = "SELECT id, name, age FROM users WHERE age > ?";
$stmt = mysqli_prepare($mysqli, $sql);

if ($stmt) {
    mysqli_stmt_bind_param($stmt, "i", $age);
    $age = 20;
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $id, $name, $age);

    while (mysqli_stmt_fetch($stmt)) {
        echo "id: " . $id . ", name: " . $name . ", age: " . $age;
    }

    mysqli_stmt_close($stmt);
} else {
    echo "Error in preparing statement: " . mysqli_error($mysqli);
}

3. Database transaction

When processing database operations, you may need to execute multiple SQL statements in one transaction . At this time, database transactions will be very useful.

Using PHP to perform database transactions requires the use of the following functions:

• mysqli_autocommit(): Set whether to automatically commit the transaction.
• mysqli_commit(): Commit the transaction.
• mysqli_rollback(): Roll back the transaction.

Example:

// 关闭自动提交事务
mysqli_autocommit($mysqli, FALSE);

// 执行多个SQL语句
mysqli_query($mysqli, "INSERT INTO users (name, age) VALUES ('John', 25)");
mysqli_query($mysqli, "UPDATE users SET age=30 WHERE name='John'");

// 如果所有SQL语句都成功执行，则提交事务
mysqli_commit($mysqli);
echo "Transaction committed successfully";

// 如果任何一个SQL语句执行失败，则回滚事务
mysqli_rollback($mysqli);
echo "Transaction rolled back because of failure";

4. Close the database connection

After completing the database operation, be sure to close the database connection to release resources.

Use the mysqli_close() function to close the database connection.

// 关闭数据库连接
mysqli_close($mysqli);
echo "Connection closed";

Summary

This article introduces the techniques of connecting to the database, executing SQL statements, transactions and closing database connections in PHP. PHP provides a variety of functions and extensions for interacting with databases. Being proficient in the use of these functions can greatly simplify the work of writing Web applications. At the same time, be careful to follow best security practices in database operations to reduce the risk of SQL injection and other security vulnerabilities.

The above is the detailed content of Tips on using PHP database operation functions. For more information, please follow other related articles on the PHP Chinese website!