mysql

How to fetch column names runtime

My MySQL database has column names like clnt_1001,clnt_1002 .... so how can I fetch the value from all clients runtime? ,

where clients can be added later, like where column name like '%CLNT_%' and id =2001;(all values in 'CLNT_' are integers)

Solution

  • You will need to query INFORMATION_SCHEMA to get the column names and use it in your SELECT query, e.g.:

    SELECT GROUP_CONCAT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'your_table'
AND COLUMN_NAME like 'CLNT_%';

    This will give you (comma separated) column names, you can then use the result of this query to construct the SELECT query, e.g.:

    SELECT <columns> 
FROM your_table;

    Here, you can replace <columns> with result of the first query and get the data for all the columns.

    Here's MySQL documentation on INFORMATION_SCHEMA tables.