How to modify the structure and data of data table in php

PHP is a popular programming language that can perform various operations, including operating databases. In this article, we will discuss how PHP can modify database tables. MySQL is used as a database example here.

1. Connect to the database

In PHP, you first need to connect to the database. You can use the following code to open a MySQL connection:

$db_host = 'localhost'; //主机地址
$db_user = 'root'; //用户名
$db_pwd = '123456'; //密码
$db_name = 'test'; //数据库名

$conn = mysqli_connect($db_host, $db_user, $db_pwd, $db_name);
if (!$conn) {
    die('数据库连接失败: ' . mysqli_connect_error());
}

After the connection is successful, you can start modifying the database table.

2. Modify the table structure

If we want to modify the structure of the database table, such as adding, modifying or deleting columns, we can use the following code:

$sql = "ALTER TABLE table_name ADD column_name datatype";
$sql = "ALTER TABLE table_name MODIFY column_name datatype";
$sql = "ALTER TABLE table_name DROP column_name";

Among them, table_name is the name of the table to be modified, column_name is the name of the column to be modified, and datatype is the data type to be set. These values ​​can be changed as needed.

For example, to add an integer column named "age", you can execute the following code:

$sql = "ALTER TABLE users ADD age INT(11)";
if (mysqli_query($conn, $sql)) {
    echo "列添加成功";
} else {
    echo "列添加失败: " . mysqli_error($conn);
}

The above code adds an integer column named "age" to the table "users" column and set its data type to integer.

If you want to modify the data type of the column, you can use the following code:

$sql = "ALTER TABLE users MODIFY age INT(11)";
if (mysqli_query($conn, $sql)) {
    echo "列修改成功";
} else {
    echo "列修改失败: " . mysqli_error($conn);
}

If you want to delete the column, you can use the following code:

$sql = "ALTER TABLE users DROP age";
if (mysqli_query($conn, $sql)) {
    echo "列删除成功";
} else {
    echo "列删除失败: " . mysqli_error($conn);
}

3. Modify table data

In addition to modifying the table structure, we can also modify the data in the table through PHP. You can use the following code to update records in the table:

$sql = "UPDATE table_name SET column1=value1, column2=value2 WHERE condition";

Among them, table_name is the name of the table to be updated, column1 and column2 are the column names to be updated, value1 and value2 are the values ​​to be updated, and condition is the update condition. These values ​​can be changed as needed.

For example, to set the "email" column of all users older than 30 years old in the table "users" to "newemail@example.com", you can execute the following code:

$sql = "UPDATE users SET email='newemail@example.com' WHERE age>30";
if (mysqli_query($conn, $sql)) {
    echo mysqli_affected_rows($conn) . " 行受到影响";
} else {
    echo "更新失败: " . mysqli_error($conn);
}

Above The code sets the "email" column of all users older than 30 in the table "users" to "newemail@example.com".

4. Execute multiple SQL statements

If we need to execute multiple SQL statements, we can use the mysqli_multi_query() function, for example:

$sql = "UPDATE users SET age=20 WHERE id=1;";
$sql .= "UPDATE users SET age=30 WHERE id=2;";
$sql .= "UPDATE users SET age=40 WHERE id=3;";

if (mysqli_multi_query($conn, $sql)) {
    do {
        if ($result = mysqli_store_result($conn)) {
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($conn));
} else {
    echo "更新失败: " . mysqli_error($conn);
}

Above The code will set the ages of users with IDs 1, 2 and 3 in the table "users" to 20, 30 and 40 years old respectively.

Summary

PHP is a popular programming language that can perform various operations, including operations on databases. This article describes how to use PHP to modify the structure and data of a MySQL database table. These operations include adding, modifying, and deleting columns, as well as updating table structure and data. Learning these skills can help you better manage your database, thereby increasing your work efficiency.

The above is the detailed content of How to modify the structure and data of data table in php. For more information, please follow other related articles on the PHP Chinese website!