Common usage of php native sql query statements

In PHP programming, SQL query statements are important tools often used by developers. PHP provides multiple ways to write SQL queries, one of which is to use native SQL queries.

Native SQL query statements refer to writing SQL statements directly in PHP code and then passing them to the database for execution. Compared with using frameworks or ORM libraries, native SQL query statements are simpler, clearer, and easier to understand and debug.

This article will introduce you to the common usage and precautions of PHP native SQL query statements.

1. Connect to the database

Before executing the SQL query statement, you need to connect to the database first. PHP provides a variety of ways to connect to the MySQL database, such as:

//连接 MySQL 数据库
$link = mysqli_connect("localhost", "root", "password", "test");
if (!$link) {
    die("连接失败: " . mysqli_connect_error());
}

Among them, the mysqli_connect function will return a connection object $link. If the connection fails, it will return false. If the connection is successful, you can execute SQL query statements through the $link object.

2. Query table data

1. Query all data in the table

Use SQL statement SELECT * FROM table_name You can query the table All data in table_name. In PHP, you can use the mysqli_query function to perform query operations, for example:

//查询表中所有数据
$result = mysqli_query($link, "SELECT * FROM table_name");
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 条结果";
}

Among them, the mysqli_query function returns the result set of the query, mysqli_num_rows The function is used to get the number of rows in the query result set. The mysqli_fetch_assoc function is used to return a piece of data in the query result set and convert it into an associative array.

2. Query part of the data in the table

Use the SQL statement SELECT column1, column2 FROM table_name to query the table specified in table_name column data. For example:

//查询表中指定列的数据
$result = mysqli_query($link, "SELECT name, age FROM table_name");
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "Name: " . $row["name"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "0 条结果";
}

3. Query data according to conditions

Use SQL statementSELECT * FROM table_name WHERE conditions You can query the table according to specified conditionstable_name Data in . For example:

//按照指定条件查询表中的数据
$result = mysqli_query($link, "SELECT * FROM table_name WHERE age > 18");
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "Name: " . $row["name"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "0 条结果";
}

Among them, the WHERE clause is used to specify the conditions of the query. For example, age > 18 means querying data whose age is greater than 18 years old.

3. Insert and update data

1. Insert data

Use SQL statementINSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) can insert data into table table_name. For example:

//插入数据到表中
$sql = "INSERT INTO table_name (name, age) VALUES ('张三', 20)";
if (mysqli_query($link, $sql)) {
    echo "执行成功";
} else {
    echo "执行失败：" . mysqli_error($link);
}

Among them, the columns and values ​​of inserted data should correspond one to one. Columns can only be inserted into specified columns, and uninserted columns will use their default values.

2. Update data

Use SQL statementUPDATE table_name SET column1 = value1, column2 = value2, ... WHERE conditions You can update the table## Data in #table_name. For example:

//更新表中的数据
$sql = "UPDATE table_name SET name = '李四' WHERE id = 1";
if (mysqli_query($link, $sql)) {
    echo "执行成功";
} else {
    echo "执行失败：" . mysqli_error($link);
}

The

SET clause is used to specify the columns and values ​​to be updated, and the WHERE clause is used to specify the conditions for the data to be updated.

4. Delete data

Use SQL statement

DELETE FROM table_name WHERE conditions to delete data in table table_name. For example:

//删除表中的数据
$sql = "DELETE FROM table_name WHERE age < 18";
if (mysqli_query($link, $sql)) {
    echo "执行成功";
} else {
    echo "执行失败：" . mysqli_error($link);
}

Among them, the

WHERE clause is used to specify the conditions for the data to be deleted.

5. Summary

This article introduces the common usage and precautions of PHP native SQL query statements. In programming, choosing the appropriate query method according to needs can help developers complete project development more quickly and efficiently. At the same time, for projects involving database operations, special attention needs to be paid to security issues, such as SQL injection, etc., to ensure the security of project data and user privacy.

The above is the detailed content of Common usage of php native sql query statements. For more information, please follow other related articles on the PHP Chinese website!