How to use mysql_query for database query in PHP language development?

In PHP language development, it is very common to use MySQL database, and the mysql_query function is a method for PHP to connect to the MySQL database, and it is also one of the basic methods for data query. In this article, we will explore how to use the mysql_query function for database queries.

1. Understand the mysql_query function

mysql_query is a function in PHP used to send queries to the MySQL server. It is usually used to perform modification operations such as INSERT, UPDATE, DELETE, and SELECT query statements.

The basic syntax structure is as follows:

mysql_query(string $query , resource $connection = ?): mixed

Among them, "$query" is the query statement to be executed, which must be a valid SQL statement; "$connection" is the identifier to connect to the MySQL database , the default value is the most recently opened connection.

If the execution is successful, a MySQL resource identifier will be returned. Otherwise, false will be returned, which means the query fails to execute.

2. Use mysql_query to query

Before using mysql_query to query, you need to connect to the database first. You can connect to the database using mysqli_connect or PDO.

The sample code for connecting to the MySQL database is as follows:

// 服务器地址
$db_host = "localhost";
// 数据库用户名
$db_user = "root";
// 数据库密码
$db_password = "123456";
// 数据库名
$db_name = "mydatabase";

// 连接数据库
$link = mysql_connect($db_host, $db_user, $db_password) or die("连接失败！");

// 选择数据库
mysql_select_db($db_name, $link);

Next, we can use the mysql_query function to query. For example, query all student information in a student table:

// 查询所有学生信息
$sql = "SELECT * FROM student";
$result = mysql_query($sql);

// 遍历结果集并输出每个学生的信息
while ($row = mysql_fetch_assoc($result)) { 
    echo '学生ID：'.$row['id'].'，姓名：'.$row['name'].'，年龄：'.$row['age'].'<br>'; 
}

In the above code, "$sql" is the query statement to be executed. In this example, it queries all records in the "student" table; "$ result" is the result set returned by executing the query. At the same time, this result set can be traversed through the mysql_fetch_assoc function. The mysql_fetch_assoc function returns the current result row as an associative array and points the pointer to the next row. Therefore, all result sets can be traversed through the while loop.

3. Avoid SQL injection

When using the mysql_query function for data query, you need to pay attention to avoid SQL injection problems. SQL injection refers to an attack method in which hackers use SQL statements in a program to execute some illegal SQL statements or obtain sensitive data by entering certain special commands and characters. In order to ensure the security of the application, input parameters need to be preprocessed when performing SQL queries.

The following are several main methods to avoid SQL injection:

1. Use the PHP addslashes function

The function of the addslashes function is to remove single quotes (') in the string , double quotes ("), backslash () and other special characters are escaped to avoid SQL injection attacks.

Usage example:

// 防SQL注入处理函数
function filter($text) {
    if(!get_magic_quotes_gpc()) {
        $text = addslashes($text);    // 对单引号、双引号、反斜杠等进行转义处理
    }
    return $text;
}

// 读取提交的用户名和密码信息
$username = filter($_POST['username']);
$password = filter($_POST['password']);

// 查询用户信息
$sql = "SELECT * FROM user WHERE username='$username' AND password='$password'";
$result = mysql_query($sql);

2. Use PHP mysqli extension function

mysqli is a module in the PHP extension library. This module provides the API interface functions provided by MySQL and supports preprocessing to execute SQL query statements.

The sample code for using mysqli to query is as follows:

// 预处理查询
$stmt = mysqli_prepare($link, "SELECT * FROM user WHERE username=? AND password=?");
mysqli_stmt_bind_param($stmt, "ss", $username, $password);
mysqli_stmt_execute($stmt);

// 处理结果集
$result = mysqli_stmt_get_result($stmt);

// 遍历结果集并输出每行信息
while ($row = mysqli_fetch_assoc($result)) { 
    echo "用户名：".$row['username']."，密码：".$row['password']."<br>"; 
}

// 关闭会话句柄
mysqli_stmt_close($stmt);

3. Use PHP PDO extension function

PDO is an extension in PHP for operating databases. This extension supports a variety of different databases and provides a complete set of preprocessing methods. This can effectively avoid SQL injection attacks.

The sample code for querying using PDO is as follows:

// 连接数据库
$db_host = "localhost";
$db_name = "mydatabase";
$db_user = "root";
$db_password = "123456";
$dsn = "mysql:host={$db_host};dbname={$db_name}";
$conn = new PDO($dsn, $db_user, $db_password);

// 预处理查询
$stmt = $conn->prepare("SELECT * FROM user WHERE username=:username AND password=:password");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

// 处理结果集
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 遍历结果集并输出每行信息
foreach ($result as $row) {
    echo "用户名：".$row['username']."，密码：".$row['password']."<br>"; 
}

// 关闭连接
$conn = null;

4. Summary

This article introduces the method of using the mysql_query function for database query , and also proposes some methods to avoid SQL injection attacks. For beginners, they can learn and practice through the above methods. For developers who have already mastered this knowledge, they can further learn the use of other advanced PHP and MySQL. Tips. In any case, ensuring the security of the program and the correctness of the data is one of the important issues that developers always need to consider.

The above is the detailed content of How to use mysql_query for database query in PHP language development?. For more information, please follow other related articles on the PHP Chinese website!