54

For example :

tbl_ifo

id | name  | age | gender 
----------------------------
1  | John  |  15 |  Male
2  | Maria |  18 |  Female
3  | Steph |  19 |  Female
4  | Jay   |  21 |  Male

How can I count the columns of this table using mysql?

rjmcb
  • 3,595
  • 9
  • 32
  • 46

8 Answers8

88
SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'tbl_ifo'
Ibrahim Azhar Armar
  • 25,288
  • 35
  • 131
  • 207
swapnesh
  • 26,318
  • 22
  • 94
  • 126
  • 6
    @codeMan 's point is very necessary. If you have multiple DBs with same table name then his point is must. – Ankit Sep 07 '15 at 21:46
53

I think you need also to specify the name of the database:

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'SchemaNameHere'
  AND table_name = 'TableNameHere'

if you don't specify the name of your database, chances are it will count all columns as long as it matches the name of your table. For example, you have two database: DBaseA and DbaseB, In DBaseA, it has two tables: TabA(3 fields), TabB(4 fields). And in DBaseB, it has again two tables: TabA(4 fields), TabC(4 fields).

if you run this query:

SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'TabA'

it will return 7 because there are two tables named TabA. But by adding another condition table_schema = 'SchemaNameHere':

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'DBaseA'
  AND table_name = 'TabA'

then it will only return 3.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • I think your `'DatabaseNameHere'` should be replaced with `'SchemaNameHere'` – vol7ron May 08 '12 at 03:46
  • Adding database name will provide more accurate results if you're running this in an environment where you might have multiple of the same app installed, thus multiple X tables are in the schema. – Brian Litzinger Mar 23 '17 at 18:54
7
$cs = mysql_query("describe tbl_info");
$column_count = mysql_num_rows($cs);

Or just:

$column_count = mysql_num_rows(mysql_query("describe tbl_info"));
Toivo EU
  • 71
  • 1
  • 2
3

To count the columns of your table precisely, you can get form information_schema.columns with passing your desired Database(Schema) Name and Table Name.


Reference the following Code:

SELECT count(*)
FROM information_schema.columns
WHERE table_schema = 'myDB'  
AND table_name = 'table1';
AKZap
  • 1,181
  • 6
  • 17
  • 31
  • 1
    your answer is just the same as johntotetwoo – rjmcb May 08 '12 at 06:00
  • 1
    yes, because i can't submit my answer due to my connection. but after my submit, there are two answers and one is accepted already :D – AKZap May 08 '12 at 06:55
1

I have a more general answer; but I believe it is useful for counting the columns for all tables in a DB:

SELECT table_name, count(*)
FROM information_schema.columns
GROUP BY table_name;
Douglas.Sesar
  • 4,214
  • 3
  • 29
  • 36
0

Simply use mysql_fetch_assoc and count the array using count() function

Ramandeep sohi
  • 11
0

this query may help you

SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'database' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tbl_ifo'
varsha
  • 1,620
  • 1
  • 16
  • 29
-1

I think you want to know the total entries count in a table! For that use this code..

SELECT count( * ) as Total_Entries FROM tbl_ifo;

Tinu Mathai
  • 88
  • 2