php sql query database table structure

In the process of PHP development, database operations are often involved, and querying the table structure is also a common requirement during the development process. This article will introduce how to use PHP to operate the database and query the table structure.

1. Connect to the database

Before performing any database operations, we need to connect to the database first. PHP supports multiple ways to connect to the database. Here we take the MySQL database as an example. The code to connect to the MySQL database is as follows:

<?php
$serverName = "localhost";     // 数据库服务器名
$userName = "username";        // 用户名
$password = "password";        // 密码
$databaseName = "database";    // 数据库名

// 创建连接
$conn = new mysqli($serverName, $userName, $password, $databaseName);

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
?>

The mysqli extension is used here to connect to the MySQL database, where $serverName is the database server name, $userName is the user name, $password is the password, and $databaseName is the database name. Create a connection object through new mysqli(). If the connection is successful, "Connection successful" will be output. Otherwise, an error message will be output and program execution will be terminated.

2. Query the table structure

After connecting to the database, we can use SQL statements to perform various operations, including querying the database table structure. There are many ways to query the table structure. Here are two common methods.

1. Use SHOW COLUMNS to query

SHOW COLUMNS is MySQL’s own query table structure statement, which can query the fields, data types, indexes, default values ​​and other information of the table. The table structure code of query table users is as follows:

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

if ($result->num_rows > 0) {
    // 输出表结构信息
    while($row = $result->fetch_assoc()) {
        echo "字段名: " . $row["Field"]. " - 数据类型: " . $row["Type"]. "<br>";
    }
} else {
    echo "没有结果";
}
$conn->close();
?>

To use SHOW COLUMNS to query the table structure, you need to execute a select statement, where FROM is followed by the table name. In the above code, $conn->query($sql) is executed to query the results, and $result->num_rows is used to determine whether there are results. If there are results, use the while loop to traverse the result set and output the field names and data types, and finally close the database connection through $conn->close().

2. Use INFORMATION_SCHEMA query

In addition to using the query statement that comes with MySQL, we can also use the INFORMATION_SCHEMA database to query the table structure. This method is not limited to the MySQL version. . The table structure code of query table users is as follows:

<?php
$sql = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出表结构信息
    while($row = $result->fetch_assoc()) {
        echo "字段名: " . $row["COLUMN_NAME"]. " - 数据类型: " . $row["DATA_TYPE"]. "<br>";
    }
} else {
    echo "没有结果";
}
$conn->close();
?>

What needs to be executed to use INFORMATION_SCHEMA query is a select statement, in which FROM is followed by the COLUMNS table in the INFORMATION_SCHEMA database, and the condition is TABLE_NAME = 'table name'. In the above code, $conn->query($sql) is executed to query the results, and $result->num_rows is used to determine whether there are results. If there are results, use the while loop to traverse the result set and output the field names and data types, and finally close the database connection through $conn->close().

3. Summary

This article introduces two common methods of using PHP to query the table structure of MySQL database, namely using MySQL’s own SHOW COLUMNS and using INFORMATION_SCHEMA query. In actual development, choosing a more suitable method according to specific needs can improve development efficiency. At the same time, you need to pay attention to database security when performing any database operations, and set database usernames and passwords to improve security.

The above is the detailed content of php sql query database table structure. For more information, please follow other related articles on the PHP Chinese website!