How to create and manage data tables using PHP

PHP is a very powerful programming language that is widely used in website and application development. Among them, creating and managing database tables is a very important aspect in PHP programming. In this article, we will focus on how to use PHP to create and manage data tables.

1. What is a data table?

A database table is a structured data collection used to store data. Usually, a database table is composed of many data records with common attributes, such as employee table, student table, etc.

2. Create a table using PHP

The first step to create a database table is to connect to the database. This can be done through PHP's built-in function mysqli_connect(). The following is a simple example:

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

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接是否成功
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

// 在数据库中创建表
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if (mysqli_query($conn, $sql)) {
    echo "MyGuests表创建成功";
} else {
    echo "创建表错误: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

In this example, we successfully created a table named MyGuests, which contains five data columns: id, firstname, lastname, email, and reg_date. Among them, id is the unique identifier of each record in the table, and reg_date is the date timestamp.

When we run this code, this table will be created in the database we are connected to, and the message "MyGuests table created successfully" will be output on the page.

3. Insert data into the table

We have successfully created the MyGuests table, now we need to insert some data into this table. This can be done by using PHP's built-in function mysqli_query(). Here is an example:

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

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// 插入数据
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (&#39;John&#39;, &#39;Doe&#39;, &#39;john@example.com&#39;)";

if (mysqli_query($conn, $sql)) {
    echo "新记录插入成功";
} else {
    echo "插入记录错误: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

In this example, we insert a new record into the MyGuests table. This record contains a firstname, lastname and e-mail address.

4. Query data records

Now that we have successfully inserted a record into the MyGuests table, the next step is to query the data records in this table. This can be done by using PHP's built-in function mysqli_query(). The following is an example:

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

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

// 查询记录
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 输出数据每行
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "查询结果为空";
}

mysqli_close($conn);
?>

In this example, we use the SELECT statement to query the data records in the MyGuests table. We get all the records in the table and output them to the page.

5. Conclusion

Through the study of this article, we have learned how to use PHP and MySQL to create and manage database tables. These skills are very important in website and app development, so it is recommended that you learn and master these skills in depth.

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