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 = "";

// 創(chuàng)建連接
$conn = new mysqli($servername, $username, $password);

// 檢查連接
if ($conn->connect_error) {
    die("連接數(shù)據(jù)庫失敗: " . $conn->connect_error);
}

echo "成功連接數(shù)據(jù)庫";
?>

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 "創(chuàng)建表成功";
} else {
    echo "創(chuàng)建表失敗: " . $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!