List number of columns in mysql

Question

I want to list the number of columns in a MySQL database table with PHP. How can I achieve this?

I can't see why this post got upvoted just now (edit, which was retracted). Honestly, some effort on their part is required and a simple Google search will reveal many results. — Funk Forty Niner, Feb 18 '18 at 13:24

score 2 · Accepted Answer · answered Feb 18 '18 at 13:24

Try this query to get a list of each table in your current database along with its number of columns.

SELECT TABLE_NAME, COUNT(*) AS column_count
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA=DATABASE()
 GROUP BY TABLE_NAME

If you want just one table's results, try something like this.

SELECT COUNT(*) AS column_count
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mytable'

score 1 · Answer 2 · answered Feb 18 '18 at 13:26

1

If you want to count number of columns then use

SELECT COUNT(*)
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = 'database_name'
         AND table_name = 'tbl_name';

answered Feb 18 '18 at 13:26

Arsalan Akhtar

2 Answers2