MySQL Query to get the column names which are of a particular data type
To accomplish this , you should be familiar with how the data types are declared in the information schema table of the DB. To know the Data type name just run this query on your PHP admin or MySql Client:
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
This will return the result like:
COLUMN_NAME | DATA_TYPE
-----------------------------
Field1 | int
Field2 | int
Field3 | varchar
Field4 | text
Field5 | longtext
Field6 | float
So we will be keep the above query as the base and will add condition " AND DATA_TYPE = 'DATA_TYPE_NAME' ".
MySQL Query to get all fields in a table which are of Type INTEGER
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE = 'int'
OR
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE LIKE 'int%'
Result:
COLUMN_NAME | DATA_TYPE
-------------------------
Field1 | int
Field2 | int
Field3 | int
To get field names as a string to use in another query use this:
SELECT GROUP_CONCAT(COLUMN_NAME) as IntFields FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME' AND DATA_TYPE LIKE 'int%'
The result would be like this:
IntFields
----------------
Field1,Field2, Field3
make this as a php string and use the filed names in another select query like this:
$fields = "Field1,Field2, Field3"; // taken from result of above query
$qry =" SELECT ".$fields." FROM YOUR_TABLE_NAME WHERE YOUR_QUERY_CONDITION";
MySQL Query to get all VARCHAR Type fields in a table
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE LIKE 'varchar%'
MySQL Query to get all field names in a table which are of type TEXT
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE = 'text'
MySQL Query to get all field names in a table which are of type CHAR
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE LIKE 'char'
MySQL Query to get all field names in a table which are of type FLOAT
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE LIKE 'float%'
Other Options
To display Column names which are of Integer type , you can use
SHOW FIELDS FROM YOUR_DATABASE_NAME.YOUR_TABLE_NAME Where Type like 'int%';
SHOW COLUMNS FROM YOUR_DATABASE_NAME.YOUR_TABLE_NAME Where Type like 'int%';
EXPLAIN YOUR_DATABASE_NAME.YOUR_TABLE_NAME Where Type like 'int%';
DESCRIBE YOUR_DATABASE_NAME.YOUR_TABLE_NAME Where Type like 'int%';
To accomplish this , you should be familiar with how the data types are declared in the information schema table of the DB. To know the Data type name just run this query on your PHP admin or MySql Client:
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
This will return the result like:
COLUMN_NAME | DATA_TYPE
-----------------------------
Field1 | int
Field2 | int
Field3 | varchar
Field4 | text
Field5 | longtext
Field6 | float
So we will be keep the above query as the base and will add condition " AND DATA_TYPE = 'DATA_TYPE_NAME' ".
MySQL Query to get all fields in a table which are of Type INTEGER
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE = 'int'
OR
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE LIKE 'int%'
Result:
COLUMN_NAME | DATA_TYPE
-------------------------
Field1 | int
Field2 | int
Field3 | int
To get field names as a string to use in another query use this:
SELECT GROUP_CONCAT(COLUMN_NAME) as IntFields FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME' AND DATA_TYPE LIKE 'int%'
The result would be like this:
IntFields
----------------
Field1,Field2, Field3
make this as a php string and use the filed names in another select query like this:
$fields = "Field1,Field2, Field3"; // taken from result of above query
$qry =" SELECT ".$fields." FROM YOUR_TABLE_NAME WHERE YOUR_QUERY_CONDITION";
MySQL Query to get all VARCHAR Type fields in a table
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE LIKE 'varchar%'
MySQL Query to get all field names in a table which are of type TEXT
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE = 'text'
MySQL Query to get all field names in a table which are of type CHAR
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE LIKE 'char'
MySQL Query to get all field names in a table which are of type FLOAT
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME' AND TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE LIKE 'float%'
Other Options
To display Column names which are of Integer type , you can use
SHOW FIELDS FROM YOUR_DATABASE_NAME.YOUR_TABLE_NAME Where Type like 'int%';
SHOW COLUMNS FROM YOUR_DATABASE_NAME.YOUR_TABLE_NAME Where Type like 'int%';
EXPLAIN YOUR_DATABASE_NAME.YOUR_TABLE_NAME Where Type like 'int%';
DESCRIBE YOUR_DATABASE_NAME.YOUR_TABLE_NAME Where Type like 'int%';