Home  >  Article  >  Backend Development  >  How to query data through database in php

How to query data through database in php

PHPz
PHPzOriginal
2023-04-12 09:17:271578browse

With the rapid development of the Internet, PHP is a simple and easy-to-learn language. More and more people are beginning to learn and use PHP. One of the main applications is querying the database through the database. This article will introduce in detail how to use PHP for database queries, as well as some practical tips and best practices.

1. Database introduction

The database is a data storage and management system in a computer system. They can store data in tables and use various algorithms and data structures to improve data access speed and efficiency. In real applications, relational databases are the most commonly used database. For example, MySQL is a commonly used relational database that is widely used in web development.

2. Connect to the database

PHP provides many functions to connect to various types of databases. Below we take MySQL as an example to introduce how to connect to the database.

Before connecting to the MySQL database, you need to install MySQL and obtain the following information:

  • Host name
  • Account number
  • Password
  • Database name

The following is the PHP code used to connect to the MySQL database:

$host = 'localhost';
$user = 'root';
$password = 'yourPassword';
$dbname = 'yourDatabaseName';

// 建立连接
$conn = mysqli_connect($host, $user, $password, $dbname);

// 检查连接是否成功
if (!$conn) {
    die('连接失败: ' . mysqli_connect_error());
}
echo "连接成功";

The above code will connect to the specified MySQL database and perform some simple error handling .

3. Query the database

After connecting to the MySQL database, you can use PHP functions to query and update the database. The following is a basic example of querying data through a SELECT statement:

$sql = "SELECT * FROM users"; // 查询所有用户
$result = mysqli_query($conn, $sql);

// 迭代输出结果
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "未找到任何记录";
}

The above code will retrieve all data from the users table, iterate through each row of results, and output it to the page. The mysqli_fetch_assoc() function converts the query results into an associative array.

4. Insert and update data

Once connected to the MySQL database, you can insert and update data. The following is an example of executing INSERT and UPDATE statements:

// 插入数据
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
    echo "新记录插入成功";
} else {
    echo "插入记录时发生错误: " . mysqli_error($conn);
}

// 更新数据
$sql = "UPDATE users SET email='newemail@example.com' WHERE id=2";
if (mysqli_query($conn, $sql)) {
    echo "记录更新成功";
} else {
    echo "更新记录时发生错误: " . mysqli_error($conn);
}

The above code will insert a new record into the users table and then update the email address of the record with id 2.

5. Close the database connection

When you finish operating the database, you need to close the database connection. The following is an example of closing a MySQL database connection:

mysqli_close($conn);

6. Error handling best practices

In practical applications, error handling is very important because it protects your database from attacks and mistake. Here are some error handling best practices:

  1. Use try-catch statements to catch database errors.
// try-catch捕获数据库错误
try {
    $sql = "SELECT * FROM users WHERE id = 1";
    $result = mysqli_query($conn, $sql);
    if (!$result) {
        throw new Exception(mysqli_error($conn));
    }
} catch (Exception $e) {
    echo "无法检索数据:" . $e->getMessage();
}
  1. Use prepared statements to prevent SQL injection attacks.
// 预处理语句预防SQL注入攻击
$stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE username=?");
mysqli_stmt_bind_param($stmt, 's', $username);
mysqli_stmt_execute($stmt);

7. Conclusion

PHP is a language that is both powerful and easy to learn, and has a wide range of applications in database query and update. Used in combination with relational databases such as MySQL, PHP can help developers access and manage database information effectively. Through the introduction and examples of this article, you should have a certain understanding of how to connect and query the MySQL database. At the same time, we also provide some practical tips and best practices so that you can better use PHP to process database information.

The above is the detailed content of How to query data through database in php. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn