Get table column names in MySQL?
PhpSqlMysqlPhp Problem Overview
Is there a way to grab the columns name of a table in MySQL using PHP?
Php Solutions
Solution 1 - Php
You can use DESCRIBE:
DESCRIBE my_table;
Or in newer versions you can use INFORMATION_SCHEMA:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';
Or you can use SHOW COLUMNS:
SHOW COLUMNS FROM my_table;
Or to get column names with comma in a line:
SELECT group_concat(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';
Solution 2 - Php
The following SQL statements are nearly equivalent:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
[AND table_schema = 'db_name']
[AND column_name LIKE 'wild']
SHOW COLUMNS
FROM tbl_name
[FROM db_name]
[LIKE 'wild']
Reference: INFORMATION_SCHEMA COLUMNS
Solution 3 - Php
I made a PDO function which returns all the column names in an simple array.
public function getColumnNames($table){
$sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = :table";
try {
$core = Core::getInstance();
$stmt = $core->dbh->prepare($sql);
$stmt->bindValue(':table', $table, PDO::PARAM_STR);
$stmt->execute();
$output = array();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$output[] = $row['COLUMN_NAME'];
}
return $output;
}
catch(PDOException $pe) {
trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
}
}
The output will be an array:
Array (
[0] => id
[1] => name
[2] => email
[3] => shoe_size
[4] => likes
... )
Sorry for the necro but I like my function ;)
P.S. I have not included the class Core but you can use your own class.. D.S.
Solution 4 - Php
This solution is from command line mysql
mysql>USE information_schema;
In below query just change <--DATABASE_NAME--> to your database and <--TABLENAME--> to your table name where you just want Field values of DESCRIBE statement
mysql> SELECT COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA = '<--DATABASE_NAME-->' AND TABLE_NAME='<--TABLENAME-->';
Solution 5 - Php
There's also this if you prefer:
mysql_query('SHOW COLUMNS FROM tableName');
Solution 6 - Php
It's also interesting to note that you can use
EXPLAIN table_name
which is synonymous with DESCRIBE table_name
and SHOW COLUMNS FROM table_name
although EXPLAIN is more commonly used to obtain information about the query execution plan.
Solution 7 - Php
How about this:
SELECT @cCommand := GROUP_CONCAT( COLUMN_NAME ORDER BY column_name SEPARATOR ',\n')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';
SET @cCommand = CONCAT( 'SELECT ', @cCommand, ' from my_database.my_table;');
PREPARE xCommand from @cCommand;
EXECUTE xCommand;
Solution 8 - Php
I needed column names as a flat array, while the other answers returned associative arrays, so I used:
$con = mysqli_connect('localhost',$db_user,$db_pw,$db_name);
$table = 'people';
/**
* Get the column names for a mysql table
**/
function get_column_names($con, $table) {
$sql = 'DESCRIBE '.$table;
$result = mysqli_query($con, $sql);
$rows = array();
while($row = mysqli_fetch_assoc($result)) {
$rows[] = $row['Field'];
}
return $rows;
}
$col_names = function get_column_names($con, $table);
$col_names now equals:
(
[0] => name
[1] => parent
[2] => number
[3] => chart_id
[4] => type
[5] => id
)
Solution 9 - Php
Look into:
mysql_query('DESCRIBE '.$table);
Solution 10 - Php
The MySQL function describe table should get you where you want to go (put your table name in for "table"). You'll have to parse the output some, but it's pretty easy. As I recall, if you execute that query, the PHP query result accessing functions that would normally give you a key-value pair will have the column names as the keys. But it's been a while since I used PHP so don't hold me to that. :)
Solution 11 - Php
The mysql_list_fields
function might interest you ; but, as the manual states :
> This function is deprecated. It is
> preferable to use mysql_query()
to
> issue a SQL SHOW COLUMNS FROM table [LIKE 'name']
statement instead.
Solution 12 - Php
You may also want to check out mysql_fetch_array()
, as in:
$rs = mysql_query($sql);
while ($row = mysql_fetch_array($rs)) {
//$row[0] = 'First Field';
//$row['first_field'] = 'First Field';
}
Solution 13 - Php
in mysql to get columns details and table structure by following keywords or queries
1.DESC table_name
2.DESCRIBE table_name
3.SHOW COLUMNS FROM table_name
4.SHOW create table table_name;
5.EXPLAIN table_name
Solution 14 - Php
you can get the entire table structure using following simple command.
DESC TableName
or you can use following query.
SHOW COLUMNS FROM TableName
Solution 15 - Php
$col = $db->query("SHOW COLUMNS FROM category");
while ($fildss = $col->fetch_array())
{
$filds[] = '"{'.$fildss['Field'].'}"';
$values[] = '$rows->'.$fildss['Field'].'';
}
if($type == 'value')
{
return $values = implode(',', $values);
}
else {
return $filds = implode(',', $filds);
}
Solution 16 - Php
this worked for me..
$sql = "desc MyTableName";
$result = @mysql_query($sql);
while($row = @mysql_fetch_array($result)){
echo $row[0]."<br>";
}
Solution 17 - Php
I have write a simple php script to fetch table columns through PHP: Show_table_columns.php
<?php
$db = 'Database'; //Database name
$host = 'Database_host'; //Hostname or Server ip
$user = 'USER'; //Database user
$pass = 'Password'; //Database user password
$con = mysql_connect($host, $user, $pass);
if ($con) {
$link = mysql_select_db($db) or die("no database") . mysql_error();
$count = 0;
if ($link) {
$sql = "
SELECT column_name
FROM information_schema.columns
WHERE table_schema = '$db'
AND table_name = 'table_name'"; // Change the table_name your own table name
$result = mysql_query($sql, $con);
if (mysql_query($sql, $con)) {
echo $sql . "<br> <br>";
while ($row = mysql_fetch_row($result)) {
echo "COLUMN " . ++$count . ": {$row[0]}<br>";
$table_name = $row[0];
}
echo "<br>Total No. of COLUMNS: " . $count;
} else {
echo "Error in query.";
}
} else {
echo "Database not found.";
}
} else {
echo "Connection Failed.";
}
?>
Enjoy!
Solution 18 - Php
mysqli fetch_field() worked for me:
if ($result = $mysqli -> query($sql)) {
// Get field information for all fields
while ($fieldinfo = $result -> fetch_field()) {
printf("Name: %s\n", $fieldinfo -> name);
printf("Table: %s\n", $fieldinfo -> table);
printf("Max. Len: %d\n", $fieldinfo -> max_length);
}
$result -> free_result();
}
Source: https://www.w3schools.com/pHP/func_mysqli_fetch_field.asp
Solution 19 - Php
The easy way, if loading results using assoc is to do this:
$sql = "SELECT p.* FROM (SELECT 1) as dummy LEFT JOIN `product_table` p on null";
$q = $this->db->query($sql);
$column_names = array_keys($q->row);
This you load a single result using this query, you get an array with the table column names as keys and null as value. E.g.
Array(
'product_id' => null,
'sku' => null,
'price' => null,
...
)
after which you can easily get the table column names using the php function array_keys($result)