How to create and manage database tables using PHP

How to use PHP to create and manage database tables

With the rapid development of the Internet, databases have become an indispensable part of various websites and applications. In PHP, we can use a database management system (DBMS) such as MySQL to create and manage database tables. This article will teach you how to use PHP to implement this function, with corresponding code examples.

1. Connect to the database

First, we need to connect to the database in PHP. You can use the mysqli extension or PDO provided by PHP to achieve this function. The following is sample code to connect to a MySQL database using the mysqli extension:

<?php
$servername = "localhost";
$username = "root";
$password = "";

// 创建连接
$conn = new mysqli($servername, $username, $password);

// 检查连接
if ($conn->connect_error) {
    die("连接数据库失败: " . $conn->connect_error);
}

echo "成功连接数据库";
?>

2. Creating Database Tables

Once connected to the database, the next step is to create the database tables. Here, we will use SQL statements to define the structure and fields of the table. The following is an example of creating a user table:

<?php
$sql = "CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    password VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === true) {
    echo "创建表成功";
} else {
    echo "创建表失败: " . $conn->error;
}
?>

3. Querying the database table

After creating the database table, we can use SQL statements to query the data in the table. The following is an example of querying all records in the user table:

<?php
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - 用户名: " . $row["username"]. " - 邮箱: " . $row["email"]. "<br>";
    }
} else {
    echo "没有找到记录";
}
?>

4. Insert data into the database table

When we have new data to add to the database table, we can Use SQL insert statements to achieve this. The following is an example of inserting a new user into the user table:

<?php
$sql = "INSERT INTO users (username, password, email)
VALUES ('john', 'password123', 'john@example.com')";

if ($conn->query($sql) === true) {
    echo "记录插入成功";
} else {
    echo "记录插入失败: " . $conn->error;
}
?>

5. Update the data in the database table

If you need to update the data in the database table, you can use SQL Update statement to achieve. The following is an example of updating the password in the user table:

<?php
$sql = "UPDATE users SET password='newpassword123' WHERE id=1";

if ($conn->query($sql) === true) {
    echo "记录更新成功";
} else {
    echo "记录更新失败: " . $conn->error;
}
?>

6. Delete data in the database table

If you need to delete data in the database table, you can use the SQL delete statement to fulfill. The following is an example of deleting a specific user record in the users table:

<?php
$sql = "DELETE FROM users WHERE id=1";

if ($conn->query($sql) === true) {
    echo "记录删除成功";
} else {
    echo "记录删除失败: " . $conn->error;
}
?>

7. Close the database connection

When the database operation is completed, the database connection should be closed to release resources. The following is a sample code to close the database connection:

<?php
$conn->close();
?>

Through the above steps, we can use PHP to create and manage database tables. These basic operations can help us build fully functional websites and applications. I hope this article will help you understand how to use PHP to create and manage database tables.

The above is the detailed content of How to create and manage database tables using PHP. For more information, please follow other related articles on the PHP Chinese website!