MySQL get column names – BEST 5 WAYS TO “mysql get column names”

you can mysql get column names using The best way is to use the INFORMATION_SCHEMA metadata virtual database. here you can get MySQL Query to Get Column Names.

mysql get column names Examples

How to Get the Column Names from Table in MySQL using PHP?

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'talimblaster_database' AND TABLE_NAME = 'products_tbl'

MySQL query to get column names?

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='talimblaster_database' 
    AND `TABLE_NAME`='products_tbl';

mysql get column names To get all tables with columns upcomming or created_at in the database talimblaster_database:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('upcomming','created_at')
        AND TABLE_SCHEMA='talimblaster_database';

How to get all columns’ names for all the tables in MySQL?

select * from information_schema.columns
where table_schema = 'talimblaster_database'
order by products_tbl,ordinal_position

Get Columns from Table using PHP and MySQL : “mysql get column names”

index.php

<?php
// Database configuration
$dbHost     = "localhost";
$dbUsername = "admin_v1";
$dbPassword = "We7887#[email protected]";
$dbName     = "talimblaster_database";

$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

$query = $db->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'talimblaster_database' AND TABLE_NAME = 'products_tbl'");

while($row = $query->fetch_assoc()){
    $result[] = $row;
}

$columnArr = array_column($result, 'COLUMN_NAME');

Also Read: MySql Comma Separated Column Join Using PHP Laravel with an example.

Read Also:  laravel 6 auth

MySQL SHOW COLUMNS and DESCRIBE: List All Columns in a Table

Use the DESCRIBE statement.

mysql> DESCRIBE products_tbl;

MySQL SHOW COLUMNS command

SHOW COLUMNS FROM products_tbl;

SHOW COLUMNS FROM talimblaster_database.products_tbl;

OR

SHOW COLUMNS FROM products_tbl IN talimblaster_database;

SHOW COLUMNS FROM products_tbl;

SHOW FULL COLUMNS FROM products_tbl;

The SHOW COLUMNS command

SHOW COLUMNS FROM products_tbl LIKE pattern;

SHOW COLUMNS FROM products_tbl WHERE expression;

show only columns that start with the letter c, you use the LIKE operator

Read Also:  Multiple checkbox in php Example with Demo

SHOW COLUMNS FROM payments LIKE 'c%';

MySQL: get column names and datatypes of a table

I will be creating a Database table named members_data as well as mysql get column names.

CREATE TABLE members_data (
member_id INT AUTO_INCREMENT,
member_name VARCHAR(255),
member_address VARCHAR(255),
member_grade VARCHAR(50),
member_subject VARCHAR(255),
 PRIMARY KEY (member_id)
);

Get column names and datatypes – using information schema

SELECT COLUMN_NAME , DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = Database()
AND TABLE_NAME = 'members_data' ;

Alternatively

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_SCHEMA = Database()
AND TABLE_NAME = 'members_data' ;

Get column names and datatypes – using SHOW COLUMNS

SHOW COLUMNS FROM members_data;

SHOW FULL COLUMNS FROM members_data;

https://dev.mysql.com/doc/refman/8.0/en/show-columns.html

I hope you get an idea about mysql get column names.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.