Structure table mysql php

Как получить структуру таблицы и ее данные с помощью запроса MySQL?

Я делаю проект, где у меня нет доступа к phpmyadmin. Я хочу видеть структуру и данные таблицы. Как я могу сделать это, используя запрос MySQL? я использовал DESCRIBE tablename но это только отображало список столбцов. Есть идеи? Я могу использовать другой запрос SELECT * FROM tablename но я просто хочу знать, где есть какая-либо команда, которая будет служить цели? Мне также нужно знать тип из моей таблицы столбцов.

Решение

Чтобы получить список столбцов для таблицы, используйте оператор SQL DESCRIBE. DESCRIBE предоставляет информацию о столбцах в таблице: Синтаксис следующий:

+-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+----------------+ | product_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | url | varchar(100) | NO | UNI | NULL | | | name | varchar(50) | NO | | NULL | | | description | varchar(255) | NO | | NULL | | | price | decimal(10,2) | NO | | NULL | | | visible | tinyint(1) unsigned | NO | MUL | 1 | | +-------------+---------------------+------+-----+---------+----------------+ 

Чтобы получить список таблиц в базе данных, используйте этот оператор SQL:

Извлечение определения таблицы из существующей таблицы

SHOW CREATE TABLE TableName; 

Также проверьте эту ссылку для большего количества команд, связанных с таблицами MySQL: Табличные команды MySQL

Другие решения

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS; 

должен начать вас. См. Главу 20 в документации MySQL.

mysqli_fetch_fields может быть использован для получения метаданных для mysqli_result ответ.

$link = mysqli_connect("host"); $result = mysqli_query($link, "select * from foo"); $meta = mysqli_fetch_fields($result); foreach ($meta as $column) < echo "Name: name>\n"; echo "Table: table>\n"; echo "Max. Len: max_length>\n"; echo "Length: length>\n"; echo "charsetnr: charsetnr>\n"; echo "Flags: flags>\n"; echo "Type: type>\n"; > $result->data_seek(0); while ($row = $result->fetch_assoc()) < foreach ($row as $name =>$value) < echo "= \n"; > > mysqli_free_result($result); 

PDOStatement::getColumnMeta обеспечивает аналогичную функциональность для PDO запрос.

Источник

Get the structure of a MySQL table in PHP (PDO).

In this tutorial, I will be showing you how to get the structure / column details of a MySQL table using PHP’s PDO object.

Code snippet (be sure to read the comments):

query('DESCRIBE ' . $tableToDescribe); //Fetch our result. $result = $statement->fetchAll(PDO::FETCH_ASSOC); //The result should be an array of arrays, //with each array containing information about the columns //that the table has. var_dump($result); //For the sake of this tutorial, I will loop through the result //and print out the column names and their types. foreach($result as $column)< echo $column['Field'] . ' - ' . $column['Type'], '
'; >

A step-by-step guide to the code above:

1. We defined our MySQL connection details.
2. We connected to MySQL using the PDO object.
3. For this example, I’ve chosen the table “users”. However, you will want to change the $tableToDescribe variable to match the name of the table that you are wanting to describe.
4. We executed a DESCRIBE query with PDO.
5. We fetched the result.
6. I did a var_dump on the result array, just so you can see what type of data will be returned.
7. I looped through the result array, printing out the column name and its type (VARCHAR, INT, etc).

The DESCRIBE query will return an array of associative arrays, with each array representing a column in the table. This array will contain the following information:

• Field: The name of the field / column.
• Type: The type of column it is (VARCHAR, INT, TEXT, etc).
• Null: Whether the column can be NULL or not.
• Key: The type of index (if any) that has been applied to the column. A Primary Key will read “Pri”, whereas a regular index will read “MUL”. Unique non-Primary-Key indexes will read “UNI”.
• Default: The column’s default value.
• Extra: Extra information about the column. For example, if it’s an Auto Increment column, then this will read “auto_increment”.

Источник

How to Show the Structure of a MySQL Table Using PHP

In this article, we go over how to show the structure of a MySQL table using PHP.

By structure, we mean what columns the table is made up of and the data type and definition of those columns.

Thus, we can know the makeup, or structure, of the table.

The general format of the MySQL query to show the structure of a table is shown below.

So the following code above is the MySQL code to show the structure of the table named table_name.

We’ll now show how to tie this in with PHP.

So, first, we must connect to the database.

After we do this, we create a variable named $tablestructure. This $tablestructure variable is set equal to a mysql_query() function that has the parameter «EXPLAIN table_name». This MySQL query is a query to show the structure of a table.

Since it’s easier to visualize a table for these results, I put the data into table form.

Right underneath this, I create a series of variables that stores data from the table. This is very important.

The name of each column of the table is in the first index of the array. So I create a variable named $column_name and set it equal to $data[0].

The data type and definition of each column of the table is in the second index of the array. So I created a variable named $definition and set it equal to $data[1].

The null value (whether the column is NULL or NOT NULL) is in the third index of the array. So I created a variable named $null and set it equal to $data[2].

The primary key value (whether the column is a primary key or not) is in the fourth index of the array. So I created a variable named $primarykey and set it equal to $data[3].

the default value (whether the column has a default value or not) is in the fifth index of the array. So I created a variable named $default and set it equal to $data[4].

The extra value (such as whether the column is set to auto_increment) is in the sixth index of the array. So I created a variable named $extra and set it equal to $data[5].

Cumulatively, all these variables represent the structure of a table. They represent the column name, its data type and definition, whether it is null or not, a primary key or not, has a default value or not, and whether it has an extra features such as auto_increment.

So now all of the table structure is in tabular form.

Example

So below is a table called usercomments with the following table structure.

Using the PHP code above, we can get the complete table structure of the above table. This is shown below.

Источник

How do I get the MySQL table structure in PHP? Plus a list of all tables?

found it at http://www.electrictoolbox.com/mysql-table-structure-describe/,What query do I need to run in PHP to get the structure of a given table in the database? And what query do I need to run to get a list of all the tables?,To get a list of tables on the database, use this SQL statement:,To get a list of columns for a table, use the DESCRIBE SQL statement. The syntax is as follows:

To get a list of columns for a table, use the DESCRIBE SQL statement. The syntax is as follows:

To get a list of tables on the database, use this SQL statement:

Answer by Armani Whitehead

To get a list of columns for a table, use the DESCRIBE SQL statement. The syntax is as follows:,To get a list of tables on the database, use this SQL statement:,See: How to find all the tables in MySQL with specific column names in them?,Also take a look in the information_schema database. Lots of very useful information about your databases, tables, indexes, etc.

To get a list of columns for a table, use the DESCRIBE SQL statement. The syntax is as follows:

To get a list of tables on the database, use this SQL statement:

$q = mysql_query('DESCRIBE tablename'); while($row = mysql_fetch_array($q)) < echo "- \n"; > 

To get the CREATE syntax use

SHOW CREATE TABLE table_name; 

Answer by Lennon Trejo

Armed with this information, we can execute a query that will list all tables in a specific database along with the disk space (size) of each. We can even get a bit fancier and convert the normal size values from bytes into something more useful and understandable to most people like megabytes.,This will return not only the size of the table, but also the table name and parent database it is associated with.,If you’re running into an issue where your database is growing in size but you don’t know which table is the culprit, it may be useful to query for the size of all tables within all databases in the entire system. This can be accomplished easily with the following query:, Grant table-level permissions in SQL Server After launching and connecting to SQL Server Management Studio, create a new login and select the database that is connected to Chartio.

SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "bookstore" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC; 

Answer by Zayd Patrick

What if you forget the name of a database or table, or what the structure of a given table is (for example, what its columns are called)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports. , To find out what tables the default database contains (for example, when you are not sure about the name of a table), use this statement: , If you want to find out about the structure of a table, the DESCRIBE statement is useful; it displays information about each of a table’s columns: , The name of the column in the output produced by this statement is always Tables_in_db_name, where db_name is the name of the database. See Section 13.7.7.39, “SHOW TABLES Statement”, for more information.

You have previously seen SHOW DATABASES , which lists the databases managed by the server. To find out which database is currently selected, use the DATABASE() function:

mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+

To find out what tables the default database contains (for example, when you are not sure about the name of a table), use this statement:

mysql> SHOW TABLES; +---------------------+ | Tables_in_menagerie | +---------------------+ | event | | pet | +---------------------+

If you want to find out about the structure of a table, the DESCRIBE statement is useful; it displays information about each of a table’s columns:

mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+

Answer by Sonny Hicks

For each table, the number of columns it should contain, as well as the column names,We created this table with only five columns. You can have plenty of columns (up to 255), but you shouldn’t have too many. If a table has too many columns, it can be cumbersome to use and the table will be sluggish when it’s accessed. It’s better to break data into multiple tables.,The last column is for the description of each bird. It’s a TEXT data type, which means that it’s a variable-width column, and it can hold up 65,535 bytes of data for each row. This will allow us to enter plenty of text about each bird. We could write multiple pages describing a bird and put it in this column.,The next step for structuring a database is to create tables. Although this can be complicated, we’ll keep it simple to start. We’ll initially create one main table and two smaller tables for reference information. The main table will have a bunch of columns, but the reference tables will have only a few columns.

DROP DATABASE rookery; CREATE DATABASE rookery CHARACTER SET latin1 COLLATE latin1_bin;

SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | rookery | | mysql | | test | +--------------------+

Answer by Yousef Roberts

The syntax to add multiple columns in a table in MySQL (using the ALTER TABLE statement) is:,The syntax to modify multiple columns in a table in MySQL (using the ALTER TABLE statement) is:,The syntax to add a column in a table in MySQL (using the ALTER TABLE statement) is:,The syntax to drop a column in a table in MySQL (using the ALTER TABLE statement) is:

The syntax to add a column in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id;

Источник