How to modify the database with php mysqli statement

PHP is a popular open source server-side scripting language that enables interactivity and dynamics on the Web. When processing database operations, PHP provides rich operation functions through the mysqli extension library. In this article, we will explore how to modify data in a database using the mysqli extension library in PHP.

1. Introduction to mysqli

Different from PHP’s mysql extension, the mysqli extension interface provides support for MySQL 4.1 and higher. The mysqli extension is the default extension in PHP 5. Starting from PHP 7, the mysql extension has been deprecated. The mysqli extension provides better performance, more features, and better security.

2. Mysqli modification statement

In PHP, the mysqli modification statement needs to be implemented through the mysqli_query() function. When using this function, you need to pass two parameters: the connection object and the SQL statement to be executed. An example is as follows:

<?php
    // 连接数据库
    $mysqli = new mysqli(&#39;localhost&#39;, &#39;username&#39;, &#39;password&#39;, &#39;dbname&#39;);

    // 检测连接是否成功
    if ($mysqli->connect_errno) {
        die('连接数据库失败：' . $mysqli->connect_error);
    }

    // 执行SQL语句
    $sql = "UPDATE users SET username='new_username' WHERE id=1";
    if ($mysqli->query($sql) === true) {
        echo "修改成功";
    } else {
        echo "修改失败";
    }

    // 关闭数据库连接
    $mysqli->close();
?>

Instructions:

First, use the mysqli class to create a connection object. If the connection fails, the program will throw an exception.

Next, build the SQL statement to be executed. Here we use the UPDATE statement to update the user name of the user with id 1 in the table.

Then, call the mysqli_query() function to execute the SQL statement. The function will return true if the query is successful, otherwise it will return false.

Finally, close the database connection.

3. Prevent SQL injection

When using the mysqli extension, we need to pay attention to the problem of SQL injection attacks. SQL injection attack is a network attack targeting database applications, which is used to trick the application into performing the actions expected by the attacker by injecting malicious SQL code. To prevent SQL injection, we can take the following measures:

1. Use the mysqli_prepare() function

mysqli_prepare() function to prepare a SQL statement and return a statement processing object. It executes a prepared SQL statement that allows us to do some binding between sending the request and receiving the response.

The example is as follows:

<?php
    // 连接数据库
    $mysqli = new mysqli(&#39;localhost&#39;, &#39;username&#39;, &#39;password&#39;, &#39;dbname&#39;);

    // 检测连接是否成功
    if ($mysqli->connect_errno) {
        die('连接数据库失败：' . $mysqli->connect_error);
    }

    // 准备SQL语句
    $stmt = $mysqli->prepare("UPDATE users SET username=? WHERE id=?");

    // 绑定参数
    $username = 'new_username';
    $id = 1;
    $stmt->bind_param("si", $username, $id);

    // 执行SQL语句
    if ($stmt->execute()) {
        echo "修改成功";
    } else {
        echo "修改失败";
    }

    // 关闭数据库连接
    $mysqli->close();
?>

In the above example, we use the mysqli_prepare() function to prepare a SQL statement, then bind the parameters to the statement, and finally call the execute() method Execute SQL statements.

2. Use the mysqli_real_escape_string() function

The mysqli_real_escape_string() function can escape special characters in a string to avoid SQL injection attacks.

The example is as follows:

<?php
    // 连接数据库
    $mysqli = new mysqli(&#39;localhost&#39;, &#39;username&#39;, &#39;password&#39;, &#39;dbname&#39;);

    // 检测连接是否成功
    if ($mysqli->connect_errno) {
        die('连接数据库失败：' . $mysqli->connect_error);
    }

    // 处理传递过来的参数
    $username = mysqli_real_escape_string($mysqli, $_POST['username']);
    $id = mysqli_real_escape_string($mysqli, $_POST['id']);

    // 构建SQL语句
    $sql = "UPDATE users SET username='$username' WHERE id='$id'";

    // 执行SQL语句
    if ($mysqli->query($sql) === true) {
        echo "修改成功";
    } else {
        echo "修改失败";
    }

    // 关闭数据库连接
    $mysqli->close();
?>

In the above example, we first use the mysqli_real_escape_string() function to escape the passed parameters, and then build the SQL statement.

4. Summary

In this article, we introduced how to use the mysqli extension library in PHP to modify database statements. We explored how to use the mysqli_query() function to execute SQL statements and covered how to prevent SQL injection attacks. Hope this article is helpful to you.

The above is the detailed content of How to modify the database with php mysqli statement. For more information, please follow other related articles on the PHP Chinese website!