Home  >  Article  >  Backend Development  >  PHP method to obtain field names and detailed information of mysql data table, mysql field_PHP tutorial

PHP method to obtain field names and detailed information of mysql data table, mysql field_PHP tutorial

WBOY
WBOYOriginal
2016-07-13 10:18:17918browse

PHP method to obtain the field name and detailed information of the mysql data table, mysql field

First we need to understand the SQL statements for querying MySQL database/table related information:

Copy code The code is as follows:

SHOW DATABASES //List MySQL Server databases.
SHOW TABLES [FROM db_name]                                                                                                      
SHOW CREATE TABLES tbl_name //Export data table structure.
SHOW TABLE STATUS [FROM db_name] //List the data table and table status information.
SHOW COLUMNS FROM tbl_name [FROM db_name] //List data table fields
SHOW FIELDS FROM tbl_name [FROM db_name], DESCRIBE tbl_name [col_name].
SHOW FULL COLUMNS FROM tbl_name [FROM db_name]//List fields and details
SHOW FULL FIELDS FROM tbl_name [FROM db_name] //List complete field attributes
SHOW INDEX FROM tbl_name [FROM db_name] //List table indexes.
SHOW STATUS                                                                                                                                                            
SHOW VARIABLES                                                                                                                                                                
SHOW PROCESSLIST //List execution commands.
SHOW GRANTS FOR user //List a user’s permissions

As you can see from the above SQL statement, we can use SHOW FULL COLUMNS to list fields and detailed information. Sample code:

Copy code The code is as follows:

$rescolumns = mysql_query("SHOW FULL COLUMNS FROM ".TB_NAME."") ;
while($row = mysql_fetch_array($rescolumns)){
// echo 'Field name: '.$row['Field'].'-Data type: '.$row['Type'].'-Comment: '.$row['Comment'];
// echo '

';
print_r($row);
}

Print results:

Copy code The code is as follows:

Array ( [0] => id [Field] => id [1] => char(2) [Type] => char(2) [2] => utf8_general_ci [Collation] => utf8_general_ci [3] => NO [Null] => NO [4] => PRI [Key] => PRI [5] => [Default] => [6] => [Extra] = > [7] => select,insert,update,references [Privileges] => select,insert,update,references [8] => [Comment] => )

Array ( [0] => title [Field] => title [1] => char(50) [Type] => char(50) [2] => utf8_general_ci [Collation] = > utf8_general_ci [3] => YES [Null] => YES [4] => [Key] => [5] => [Default] => [6] => [Extra] => [7] => select,insert,update,references [Privileges] => select,insert,update,references [8] => Recommended storage: title, name and other information [Comment] => Recommendation Storage: title, name and other information)

Array ( [0] => des [Field] => des [1] => varchar(255) [Type] => varchar(255) [2] => utf8_general_ci [Collation] = > utf8_general_ci [3] => YES [Null] => YES [4] => [Key] => [5] => [Default] => [6] => [Extra] => [7] => select,insert,update,references [Privileges] => select,insert,update,references [8] => [Comment] => )

…………

Supplementary information:

Of course you can also use mysql_list_fields — to list the fields in the MySQL results. mysql_list_fields() obtains information about a given table name. The parameters are the database name and table name, and returns a result pointer.

However, the mysql_list_fields() function is deprecated. It is better to use mysql_query() to issue a SQL statement SHOW COLUMNS FROM table [LIKE 'name'] instead. For details, please refer to the PHP help documentation: PHP: mysql_list_fields - Manua

How to query the MYSQL database in PHP code to get the ID number of the largest record in a field in the data table. For example: see the question supplement

I did the test like this:
create table music(
id varchar(10),
title varchar(100),
name varchar(10)
);
insert into music values('2','farewell','12354');
insert into music values('15','friend','5454');
insert into music values('161 ','Farewell','4668');

$conn=mysql_connect("localhost:3307","Database username","Database password");
mysql_select_db("date");
$result=mysql_query("select max(id+0) max_id from music",$conn);
$field=mysql_fetch_row($result);
print_r($ field);
?>
Result: Array ( [0] => 161 )
Because max() cannot be used for varchar in mysql, the id type is converted by id+0 to solve this problem. If the id is an integer when you create the table, you can use max() directly. For detailed explanation, see: hb.qq.com/a/20110624/000061.htm.

php obtains all data table information in the mysql database

mysql_query executes sql statements and show tables

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/885652.htmlTechArticleHow PHP obtains the field names and detailed information of the mysql data table. First, we need to understand the query of the MySQL database for the mysql field. /SQL statement for table related information: Copy the code as follows...
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn