Use PHP to operate the database

With the continuous advancement of Internet technology, databases have become an indispensable part of Web applications. Therefore, learning how to use PHP to operate databases is one of the skills that every web developer needs to master. In this article, we will share some basic knowledge and practical tips on how to use PHP to operate databases.

1. Connect to the database

First, we need to use the functions provided by PHP to connect to the database. Commonly used connection functions include mysqli_connect() and PDO. Next, take mysqli_connect() as an example:

$conn = mysqli_connect("localhost", "username", "password", "database_name");

This function accepts four parameters: localhost (the address of the database server), username (user name to connect to the database), password (password to connect to the database), and database_name (name of the database to be connected). When the connection is successful, the function will return a connection object that we can use to query and modify data in the database.

2. Add, delete, modify and query operations

The most common operations using PHP to operate the database include adding, deleting, modifying, querying, etc. We will introduce the implementation of these operations separately. method.

Add data

To add a piece of data to the database, we can use the SQL statement INSERT INTO. For example:

$sql = "INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 30)";
if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

This SQL statement will add a record to the data table named users. If the operation is successful, the program will output New record created successfully. If the operation fails, the error message will be output according to the set error reporting.

Delete data

We can use the SQL statement DELETE FROM to delete one or more records. For example:

$sql = "DELETE FROM users WHERE id=1";
if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

This SQL statement will delete the record with id 1 in the data table named users. If the deletion is successful, the program will output Record deleted successfully. If the operation fails, the error message will be output according to the set error reporting.

Modify data

To modify a piece of data in the database, we can use the SQL statement UPDATE. For example:

$sql = "UPDATE users SET age=31 WHERE id=2";
if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

This SQL statement will update the age value of the record with id 2 in the data table named users to 31. If the operation is successful, the program will output Record updated successfully. If the operation fails, the error message will be output according to the set error reporting.

Query data

Query operation is one of the most commonly used operations. We can use the SQL statement SELECT to query the data in the database. For example:

$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"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "0 results";
}

This program will obtain all data from the data table named users and output the results in a certain format. If the query result is empty, the program will output 0 results.

3. Preprocessed statements

Using prepared statements allows us to operate the database more safely. Prepared statements can prevent SQL injection attacks by using placeholders instead of variables. The following is an example of using prepared statements:

$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $age);

$name = "John Doe";
$email = "john@example.com";
$age = 30;

$stmt->execute();

echo "New records created successfully";

In this example, we use the prepare() function to define a prepared statement. ? represents placeholders, and placeholders and variables in prepared statements can be bound together through the bind_param() function. Finally, use the execute() function to execute the prepared statement.

4. Close the database connection

After completing the database operation, we need to explicitly close the database connection to release server resources. Use the mysqli_close() function to close the database connection:

mysqli_close($conn);

Through the introduction of this article, we have learned how to use PHP to operate the database. Through these basic knowledge and practical skills, we can complete database operations in web development more efficiently. Of course, if you want to learn how to use the database in depth, you need to read more relevant materials and accumulate experience in practice.

The above is the detailed content of Use PHP to operate the database. For more information, please follow other related articles on the PHP Chinese website!