1

I'm begginer in MYSQL. I need to retrieve the COLUMN NAMES of columns which have value "TRUE"? I found this code but I don't understand how to user it:

 SELECT `COLUMN_NAME` 
 FROM `INFORMATION_SCHEMA`.`COLUMNS` 
 WHERE `TABLE_NAME`='tablename'

Thanks in advance

Joni Bolkvadze
  • 21
  • 5
  • just use `desc tablename` . To find the columns in the table. – Torrezzzz Jul 22 '14 at 11:52
  • @user3411648 How does this answer the question? – adaam Jul 22 '14 at 11:54
  • What he is asking and desc table are the same . You cant search by column in database . – Torrezzzz Jul 22 '14 at 12:00
  • @user3411648 Granted, his original question is a little ambiguous and possibly he doesn't understand that columns != rows, but to me it seems like he is looking to find any column names that have a value of 'TRUE' in the rows of the table. – adaam Jul 22 '14 at 12:09
  • **" he is looking to find any column names that have a value of 'TRUE' in the rows of the table. "** Yes, that's what I need – Joni Bolkvadze Jul 22 '14 at 12:11

2 Answers2

1

I think this will help you MySQL query to get column names?

With the following you will get all information about the specific column

SELECT * 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';
    AND `COLUMN_NAME` = 'namecolumn'

If you want the names of all columns which have the value true in a row you could use foreach if you use php and write all keys in an array for example where the value is true

$list = array();
foreach($rows as $key => $value)
{
   if($value === true) $list[] = $key;
}
Community
  • 1
  • 1
Fritz
  • 831
  • 7
  • 23
0

This code shows all column names from a specific table, the schema INFORMATION_SCHEMA stores information about the items created on your database server(schemas, tables, columns etc.)

The statement above will show you alle columns from tablename, theres also a more simple syntax to do this SHOW COLUMNS FROM tablename (MySQL Manual)

How do you want to determine that the value of a column is true? Maybe there a two rows in your table with the values TRUE and FALSE for one column (TRUE in one row and FALSE in the other row)

With one Row in PHP

If there is only one row in your table execute the SHOW COLUMNS FROM tablename do a fetch_array on the result and then foreach and save only the keys where the value is TRUE.

If you try to store something like a configuration use a key value table and query it using where clause, this will be more effective.

kevdev
  • 1,054
  • 8
  • 9