How to list columns in MySQL/MariaDB. How to check if a column exists in a table in PHP

Consider a situation where you need to find out whether a certain column exists in a table that is located in a database managed by a MySQL or MariaDB DBMS. In this post, we'll look at the SQL query syntax for getting a list of columns and searching for columns by name. We will also look at how to check the presence of a column in a MySQL or MariaDB table from PHP code.

How to list all columns of a table in MySQL/MariaDB

Using an SQL query, you can get a list of all the columns in the database at once.

The following example will select the mydb database to use and display all the columns of the mytable table:

USE mydb;
SHOW COLUMNS FROM mytable;

The query can be written in one line; for this, the “FROM” operator is used twice – first, the table is indicated after it, and then after another “FROM” the database containing this table is indicated:

SHOW COLUMNS FROM mytable FROM mydb;

You can use the more descriptive DATABASE.TABLE syntax:

SHOW COLUMNS FROM mydb.mytable;

All 3 examples will produce the same result.

Note: in the SQL queries shown above and in all subsequent SQL queries, you can replace “FROM” with the keyword “IN” – the result will be identical. For example:

SHOW COLUMNS IN mytable FROM mydb;
SHOW COLUMNS IN mydb.mytable;

Note 2: You can also replace “COLUMNS” with “FIELDS” and again get the same result. For example:

SHOW FIELDS FROM mytable FROM mydb;
SHOW FIELDS FROM mydb.mytable;

How to find table columns by name pattern

By using the “LIKE” operator as a search string, you can use wildcard patterns, for example:

SHOW COLUMNS FROM mytable LIKE '%id'

This example will list all columns whose name ends with “id”.

If you don't use wildcards, the “LIKE” operator is the equivalent of “=”.

How to find columns by exact name match

You can choose from one of the following options.

SQL query with the “LIKE” operator without specifying wildcards:

SHOW COLUMNS FROM mytable LIKE 'id'

SQL query with “WHERE” operator:

SHOW COLUMNS FROM mytable WHERE field = 'column name';

Both of these SQL queries will give the same result.

How to search by table attributes in MySQL/MariaDB

Using “WHERE” you can also search by table attributes, for example:

SHOW COLUMNS FROM table WHERE type LIKE 'varchar%'

This example will find all columns with a VARCHAR data type.

For details about searching by table attributes, see the link: https://dev.mysql.com/doc/refman/9.0/en/show-columns.html

How to check if a certain column (field) exists in a table in PHP

The following code checks whether the test_column column exists in the TestTABLE table which is located in the TestDB database:

$db_user = 'root';
$db_password = '';
$db_name = 'TestDB';
$mysqli = new mysqli("localhost", $db_user, $db_password, $db_name);
if ($mysqli->connect_errno) 
{
    printf("Не удалось подключиться: %s\n", $mysqli->connect_error);
    exit();
}

$query = "SHOW COLUMNS FROM TestTABLE WHERE field = 'test_column';";
$result = $mysqli->query($query);
if (mysqli_num_rows($result)==0) 
{ 
    echo 'The column does not exist' . PHP_EOL;
}
else
{
    echo 'The column exists' . PHP_EOL;
}