How to get the column name from the table using where condition too

Question

currently I have a cop_config table. What I want is to fetch some (not all) column names using where condition too. Right now, I am only able to get all column name using the following code..

 $cop_value = "SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'cop_config' and is_nullable = 'NO'";
      $cop_result = $conn->query($cop_value);
      while($cop_row = $cop_result->fetch_assoc()){
      $cop_row = implode(',', $cop_row);
      echo $cop_row;
    }

currenlty my output is :

iddevice_keyfirst_pulse1first_pulse2first_pulse3first_pulse4first_pulse5first_pulse6first_pulse7first_pulse8second_pulse1second_pulse2second_pulse3second_pulse4second_pulse5second_pulse6second_pulse7second_pulse8

here is the table :

from the table what I want is to get only those column name according to device_key(in cop_config table) which has 1 in value. I am know my problem explanation is not so good please look at the table picture for better understanding tnx alot for your concern.

like in device_key YMR200, I just want to get the column name -- first_pulse1 & second_pulse2

similary for device_key VTA600 I want get the column name -- first_pulse3 & second_pulse4.

------------------------------☺☻♀♥♣♦♣WÇ-------------------------------- after a long search and google currently I am using

      $cop_value = "SELECT 'first_pulse1' from cop_config where first_pulse1 = 1 and device_key = 'VTA600' union 
select 'first_pulse2' from cop_config where first_pulse2 = 1 and device_key = 'VTA600' union 
select 'second_pulse1' from cop_config where second_pulse1 = 1 and device_key = 'VTA600' union 
select 'second_pulse2' from cop_config where second_pulse2 = 1 and device_key = 'VTA600'";


     $cop_result = $conn->query($cop_value);
      while($cop_row = $cop_result->fetch_assoc()){

      echo $cop_row;
    }

I knwo I have made blunder mistake here while fetching the value ... but I am dont know how to fetch the value from the query . Plz help thanks ..

Did you search for PL/SQL based solutions ? What I could think of is using a cursor to iterate over the rows and get status of each device_key in other columns. It should work if you have a fixed number of columns. — Arun, Mar 02 '17 at 05:29
tnx for your response. I will google PL/SQL and let you know if this work — metalhead101, Mar 02 '17 at 05:57

score 3 · Answer 1 · edited May 23 '17 at 12:26

3

try
for sql server:

 SELECT
  sys.columns.name AS ColumnName
FROM
  sys.columns
WHERE
  sys.columns.name = 'first_pulse1'
  and
  exists(select top 1 * from cop_config where device_key = 'YMR200')

for Oracle: see this

PLSQL: see this

for mySQL: see this

edited May 23 '17 at 12:26

Community

1
1

answered Mar 02 '17 at 06:49

ARr0w

1,701
15
31

tncx for your response. But still I am getting unexpected 'top' identifier – metalhead101 Mar 02 '17 at 08:38
See other links i've posted after their tags? – ARr0w Mar 02 '17 at 10:11
I have gone through all of them but none of them seem very promising so I have moved on to union method..SELECT 'first_pulse1' from cop_config where first_pulse1 = 1 and device_key = 'VTA600' union select 'first_pulse2' from cop_config where first_pulse2 = 1 and device_key = 'VTA600' union select 'second_pulse1' from cop_config where second_pulse1 = 1 and device_key = 'VTA600' union select 'second_pulse2' from cop_config where second_pulse2 = 1 and device_key = 'VTA600' – metalhead101 Mar 02 '17 at 10:16
In mysql workbench its is working as it should be in my real time php file its not running. I am lacking on how to fetch the value from the query. If you got any idea please help – metalhead101 Mar 02 '17 at 10:17
I have updated my question plz go through it once if you solution help... – metalhead101 Mar 02 '17 at 10:20
first try the query again removing 'top'. If still not works. Well, you keep searching and as soon as i will come up with something working i'll share it. – ARr0w Mar 02 '17 at 10:35

score 1 · Answer 2 · answered Mar 03 '17 at 07:18

Finally after long googling, I found this solution and its working. But I am not sure is it the right way of doing is it. Currently I am using union to get the data as required. here is the code

 $cop_value = "SELECT 'first_pulse1' from cop_config t where first_pulse1 = 1 and device_key = 'YMR200' union 
select 'first_pulse2' from cop_config t where first_pulse2 = 1 and device_key = 'YMR200' union
select 'first_pulse3' from cop_config t where first_pulse3 = 1 and device_key = 'YMR200' union
select 'first_pulse4' from cop_config t where first_pulse4 = 1 and device_key = 'YMR200' union
select 'first_pulse5' from cop_config t where first_pulse5 = 1 and device_key = 'YMR200' union
select 'first_pulse6' from cop_config t where first_pulse6 = 1 and device_key = 'YMR200' union 
select 'first_pulse7' from cop_config t where first_pulse7 = 1 and device_key = 'YMR200' union
select 'first_pulse8' from cop_config t where first_pulse8 = 1 and device_key = 'YMR200' union
select 'second_pulse1' from cop_config t where second_pulse1 = 1 and device_key = 'YMR200' union 
select 'second_pulse2' from cop_config t where second_pulse2 = 1 and device_key = 'YMR200' union
select 'second_pulse3' from cop_config t where second_pulse3 = 1 and device_key = 'YMR200' union
select 'second_pulse4' from cop_config t where second_pulse4 = 1 and device_key = 'YMR200' union
select 'second_pulse5' from cop_config t where second_pulse5 = 1 and device_key = 'YMR200' union
select 'second_pulse6' from cop_config t where second_pulse6 = 1 and device_key = 'YMR200' union
select 'second_pulse7' from cop_config t where second_pulse7 = 1 and device_key = 'YMR200' union
select 'second_pulse8' from cop_config t where second_pulse8 = 1 and device_key = 'YMR200'";


      $cop_result = $conn->query($cop_value);
      while($cop_row = $cop_result->fetch_assoc()){
      $cop_row = implode(',', $cop_row);
      echo $cop_row;
    }

and this is the output for device_key YMR200

first_pulse1second_pulse2

How to get the column name from the table using where condition too

2 Answers2