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, themysqli_connectfunction will return a connection object$link. If the connection fails, it will returnfalse. If the connection is successful, you can execute SQL query statements through the$linkobject.

2. Query table data

1. Query all data in the table

Use SQL statementSELECT * FROM table_nameYou can query the table All data intable_name. In PHP, you can use themysqli_queryfunction 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"]. "
"; } } else { echo "0 条结果"; }

Among them, themysqli_queryfunction returns the result set of the query,mysqli_num_rowsThe function is used to get the number of rows in the query result set. Themysqli_fetch_assocfunction 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 statementSELECT column1, column2 FROM table_nameto query the table specified intable_namecolumn 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"]. "
"; } } else { echo "0 条结果"; }

3. Query data according to conditions

Use SQL statementSELECT * FROM table_name WHERE conditionsYou 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"]. "
"; } } else { echo "0 条结果"; }

Among them, theWHEREclause is used to specify the conditions of the query. For example,age > 18means 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 tabletable_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 conditionsYou 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); }

SETclause is used to specify the columns and values to be updated, and theWHEREclause is used to specify the conditions for the data to be updated.

DELETE FROM table_name WHERE conditionsto delete data in tabletable_name. For example:

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

WHEREclause is used to specify the conditions for the data to be deleted.

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!