How PHP+MySQL implements database addition, deletion, modification and query operations

PHP and MySQL are the most popular technologies in modern web development. By using these two technologies, developers can build dynamic web applications that include data storage and retrieval. This article will introduce how to use PHP and MySQL to implement add, delete, modify and query operations in the database.

1. Environment configuration

Before we start, we need to confirm that the development environment for PHP and MySQL has been configured. If not, please install and configure it yourself. In order to test the code, we use the local environment for development, and assume that you have already set up the web server, PHP and MySQL locally.

2. Create the database

First, we need to create the database. Please use the MySQL client to log in to the server and run the following command in the console:

CREATE DATABASE test;

This will create a database named "test". Next, we need to switch to this database:

USE test;

Next, we will create a data table named "users", which will contain the user's information.

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This will create a data table named "users", which contains four fields: id, name, email and password. id is an auto-incrementing integer used as a unique identifier for the user. The name and email fields store the user's name and email address respectively. The password field stores the user's password, which needs to be hashed and stored. The created_at field is used to store the creation time of the user account.

3. Configure database connection

In PHP, we use the mysqli extension to manage our database connection. First, we need to define some constants to store the configuration values ​​​​of the database connection. In this example, we will connect to the local MySQL server with the username root, password empty, and database name test:

define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'test');

Next, we use the mysqli_connect() function to connect to the database server:

$mysqli = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

If the connection fails, an error message will be returned. If successful, the following four basic database operations can be performed: create, read, update, and delete.

4. Implement add, delete, modify and query operations

1. Add to database

The following functions are used to add user information to the data In the table:

function create_user($name, $email, $password) {
global $mysqli;

$hashed_password = password_hash($password, PASSWORD_DEFAULT);

$stmt = $mysqli->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $hashed_password);
$stmt->execute();

return $mysqli->insert_id;
}

This function requires three parameters: username, email address and password. It first hashes the password using the password_hash() function. Then, use the prepare() function to prepare a SQL query that will add the username, email address, and hashed password to the data table. The bind_param() function binds parameters to query placeholders and executes the query. Finally, use the insert_id() function to obtain the new user's unique identifier.

2. Get data from the database

The following function obtains user information in the database through the user's ID:

function get_user($id) {
global $mysqli;

$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();

$result = $stmt->get_result();

if ($result->num_rows === 0) {
    return null;
}

return $result->fetch_assoc();
}

This function requires one parameter: The user's unique identifier. It uses the prepare() function to prepare a SQL query that will select user information in the data table that matches the provided ID. The bind_param() function binds parameters to query placeholders and executes the query. Then use the get_result() function to get the query results and return an associative array (if the record is found) or null (if the record is not found).

3. Update database

The following function is used to update user information in the database:

function update_user($id, $name, $email, $password) {
global $mysqli;

$hashed_password = password_hash($password, PASSWORD_DEFAULT);

$stmt = $mysqli->prepare("UPDATE users SET name = ?, email = ?, password = ? WHERE id = ?");
$stmt->bind_param("sssi", $name, $email, $hashed_password, $id);
$stmt->execute();

return $stmt->affected_rows === 1;
}

This function requires four parameters: user ID, user name , email address and password. It first hashes the password using the password_hash() function. Then, use the prepare() function to prepare a SQL query that will update the user information in the data table that matches the provided ID. The bind_param() function binds parameters to query placeholders and executes the query. Finally, use the affected_rows() function to check whether the update is successful and return a Boolean value.

4. Delete data from the database

The following function is used to delete user information from the database:

function delete_user($id) {
global $mysqli;

$stmt = $mysqli->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();

return $stmt->affected_rows === 1;
}

This function requires one parameter: user ID. It uses the prepare() function to prepare a SQL query that will delete user information matching the provided ID from the data table. The bind_param() function binds parameters to query placeholders and executes the query. Finally, use the affected_rows() function to check whether the deletion is successful and return a Boolean value.

5. Debugging and Optimization

In any web development process, debugging and optimization are very important. In order to debug our code, we can use the error_reporting() and ini_set() functions. These functions are used to set the error reporting level and settings for displaying error messages. In order to optimize our code, we should avoid concatenated strings in database queries as much as possible, and should use the prepare() function and bind_param() function instead.

6. Summary

By using PHP and MySQL, we can easily create web applications with data storage and retrieval capabilities. In this article, we covered how to use these two technologies to implement basic database operations: create, read, update, and delete. By implementing the above code, you will learn how to connect to the database, create database tables, add, read, update and delete user data. These skills will be very useful in your future web development jobs.

The above is the detailed content of How PHP+MySQL implements database addition, deletion, modification and query operations. For more information, please follow other related articles on the PHP Chinese website!