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

1. Environment configuration

Before you start, you need to make sure that the development environment for PHP and MySQL has been configured. If not, please install and configure it yourself. We develop in a local environment and assume that you have already configured the web server, PHP and MySQL for code testing.

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

We use the mysqli extension in PHP to control our database connection. First, we need to define some constants to store the configuration values ​​​​of the database connection. In this example, we use the user root and the password is empty to connect to the local MySQL server and access the database 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. Upon success, you will be able to perform the four basic database operations of create, read, update, and delete.

4. Implement add, delete, modify and query operations

1. Add to database

The following functions are used Add user information to the data 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. Through the bind_param() function, you can bind query parameters to placeholders and perform query operations. 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. This operation prepares a SQL query and uses the prepare() function to select user information matching the given ID in the data table. 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, username, email address and password. It first hashes the password using the password_hash() function. Next, use the prepare() method to prepare a SQL query that will update the user information matching the provided ID. The bind_param() function binds parameters to query placeholders and executes the query.. Finally, use the affected_rows() function to verify whether the update operation was 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. This code calls the prepare() function to prepare a SQL query that will delete user information from 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 deletion is successful and return a Boolean value.

5. Debugging and Optimization

In any web development process, debugging and optimization are very important. We can use the error_reporting() and ini_set() functions to debug our code. 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.

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!