To get total number of columns in mysql

Question

This is the sql query of my table.

$date= date('d-m-Y');

SELECT SQL_CALC_FOUND_ROWS t.* ,count(*) as count 
FROM(

  SELECT client_id,clientplan_id as sl,client_name,plan_name,DATE_FORMAT(clientplan_startdate,'%d-%m-%Y') as clientplan_startdate,DATE_FORMAT(clientplan_enddate,'%d-%m-%Y') as clientplan_enddate,server_name,clientplan_id 
  FROM clientplan 
  join client on client.client_id=clientplan.clientplan_clientid 
  left join plan on clientplan.clientplan_planid=plan.plan_id left 
  join server on client.client_server=server.server_id 
  WHERE client_status!=0 AND clientplan_status!=2 
  ORDER BY clientplan_id desc)as t 

GROUP BY t.client_id 
HAVING clientplan_enddate='$date' 
ORDER BY client_name asc LIMIT 0, 1000

Then how can I get the number of columns in this list.

possible dublicate: http://stackoverflow.com/questions/14885279/how-to-count-the-columns-of-a-mysql-query-when-the-number-of-columns-returned-is - also: your first line indicates that you use PHP.. the PHP mysql driver (mysqli, PDO) have functions to get the number of columns in a resultset (for example mysqli_num_fields($query)) — cypherabe, Aug 19 '15 at 10:44

score 0 · Answer 1 · answered Aug 19 '15 at 10:51

One method is to create a view and count the columns in the view:

create view v_table as
    . . .;

The information about views is also contained in INFORMATION_SCHEMA.COLUMNS, so the following should provide what you want:

select count(*)
from information_schema.columns
where table_name = 'v_table' and table_catalog = ???;

To get total number of columns in mysql

1 Answers1