How to query field name in php

In the process of developing a website using PHP, querying the names of various fields in the database is a very common task. Whether it is for display on the page or for subsequent data processing, it is necessary to obtain the field names in the database table. This article will introduce how to use PHP to query field names in a database table.

1. Use DESC command
   DESC is a command in MySQL, which can return table-related information, including table structure, fields, etc. You can use the DESC command to obtain all field names of a table. The specific implementation is as follows:

<?php
//连接数据库
$conn = mysqli_connect("localhost", "root", "123456", "test");
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

$sql = "DESC users";
$result = mysqli_query($conn, $sql);

//循环输出结果，即所有字段名
while($row = mysqli_fetch_assoc($result)) {
    echo $row[&#39;Field&#39;] . "<br />";
}

mysqli_close($conn);
?>

In the above code, the DESC users statement is used to obtain the structural information of the users table, and the mysqli_query() function is used to execute SQL statement, the mysqli_fetch_assoc() function is used to fetch a row from the result set as an associative array. Since an associative array is returned, you can use $row['Field'] to get the field name and then output it.

2. Use the SHOW COLUMNS command
   The SHOW COLUMNS command can also be used to query the field names of the table, and is simpler and clearer than the DESC command. The specific implementation code is as follows:

<?php
//连接数据库
$conn = mysqli_connect("localhost", "root", "123456", "test");
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

$sql = "SHOW COLUMNS FROM users";
$result = mysqli_query($conn, $sql);

//循环输出结果，即所有字段名
while($row = mysqli_fetch_assoc($result)) {
    echo $row[&#39;Field&#39;] . "<br />";
}

mysqli_close($conn);
?>

In the above code, the SHOW COLUMNS FROM users statement is used to obtain all field information of the users table, the mysqli_query() function is used to execute SQL statements, and the mysqli_fetch_assoc() function is used to Take a row from the result set as an associative array. Similarly, you can use $row['Field'] to get the field name and then output it.

3. Using information_schema database
   information_schema is a system database in MySQL, which contains many detailed information about databases, tables, fields, etc. By querying the tables in the information_schema database, you can obtain relevant information about all tables, including field names. The specific implementation code is as follows:

<?php
//连接数据库
$conn = mysqli_connect("localhost", "root", "123456", "test");
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

$sql = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = &#39;users&#39;";
$result = mysqli_query($conn, $sql);

//循环输出结果，即所有字段名
while($row = mysqli_fetch_assoc($result)) {
    echo $row[&#39;COLUMN_NAME&#39;] . "<br />";
}

mysqli_close($conn);
?>

In the above code, the SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'users' statement is used to obtain all field information of the users table, and the mysqli_query() function is used to execute SQL statement, the mysqli_fetch_assoc() function is used to fetch a row from the result set as an associative array. Since an associative array is returned, you can use $row['COLUMN_NAME'] to get the field name and then output it.

Summary:
The above three methods can be used to query the field names in the MySQL table. Among them, using the information_schema database can obtain field information of all tables, which is more flexible, but will consume a certain amount of performance. Using the DESC and SHOW COLUMNS commands is relatively simpler and more direct, with relatively better performance. At the same time, during the implementation process, attention must also be paid to preventing security issues such as SQL injection.

The above is the detailed content of How to query field name in php. For more information, please follow other related articles on the PHP Chinese website!