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 includemysqli_connect()andPDO. Next, takemysqli_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), anddatabase_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 statementINSERT 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 . "
" . mysqli_error($conn); }

This SQL statement will add a record to the data table namedusers. If the operation is successful, the program will outputNew record created successfully. If the operation fails, the error message will be output according to the seterror reporting.

Delete data

We can use the SQL statementDELETE FROMto 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 namedusers. If the deletion is successful, the program will outputRecord deleted successfully. If the operation fails, the error message will be output according to the seterror reporting.

Modify data

To modify a piece of data in the database, we can use the SQL statementUPDATE. 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 theagevalue of the record with id 2 in the data table namedusersto 31. If the operation is successful, the program will outputRecord updated successfully. If the operation fails, the error message will be output according to the seterror reporting.

Query data

Query operation is one of the most commonly used operations. We can use the SQL statementSELECTto 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"]. "
"; } } else { echo "0 results"; }

This program will obtain all data from the data table namedusersand output the results in a certain format. If the query result is empty, the program will output0 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 theprepare()function to define a prepared statement.?represents placeholders, and placeholders and variables in prepared statements can be bound together through thebind_param()function. Finally, use theexecute()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 themysqli_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!