check columns of a DB and output the no's 2

[dkemas]

I have a table that features 20 columns, the results of these are either y, n or na.

The vast majority of the results are y so a typical row would be

id | col1 | col2 | col3 | col4 | col5 | col6 etc (the actual comumn names are not sequential, I just did this for ease)
123 | y | y | y | n | y | y

I have been asked to output which columns have a n, so in my example above I need to echo

"There is 1 answer of NO, the row with NO is
col4"

another example

456 | y | n | y | n | y | y

"There are 2 answers of NO, the rows with NO is
col2
col4"

Now my initial thought would be to use an if statement on each column and build up a string to output but is there an easier way?

 

[vacunita]

You need to do this for every row? Or just a specified one? That is is your query a general query tat retrieves many rows or only one at a time?

Assuming you are retrieving a single row from the DB and need to get the columns with the n values:

[COLOR=#009900]$row[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_fetch_row[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$resultset[/color][COLOR=#990000],[/color]MYSQL_ASSOC[COLOR=#990000]);[/color]
[COLOR=#009900]$noes[/color][COLOR=#990000]=[/color][COLOR=#0000FF][b]array()[/b][/color][COLOR=#990000];[/color]
[b][COLOR=#0000FF]foreach[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$row[/color] [b][COLOR=#0000FF]as[/color][/b] [COLOR=#009900]$colName[/color] [COLOR=#990000]=>[/color] [COLOR=#009900]$colvalue[/color][COLOR=#990000])[/color]
[COLOR=#FF0000]{[/color]
  [b][COLOR=#0000FF]if[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$colvalue[/color] [COLOR=#990000]==[/color] [COLOR=#FF0000]"n"[/color][COLOR=#990000])[/color]
  [COLOR=#FF0000]{[/color]
[tab][COLOR=#009900]$noes[/color][COLOR=#990000][][/color] [COLOR=#990000]=[/color]  [COLOR=#009900]$colName[/color][COLOR=#990000];[/color]
  [COLOR=#FF0000]}[/color]
[COLOR=#FF0000]}[/color]

[b][COLOR=#0000FF]if[/color][/b][COLOR=#990000]([/color][b][COLOR=#000000]count[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$noes[/color][COLOR=#990000])[/color] [COLOR=#990000]>[/color] [COLOR=#993399]0[/color][COLOR=#990000])[/color]
[COLOR=#FF0000]{[/color]
  [b][COLOR=#0000FF]echo[/color][/b] [COLOR=#FF0000]"There are "[/color] [COLOR=#990000].[/color] [b][COLOR=#000000]count[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$noes[/color][COLOR=#990000])[/color] [COLOR=#990000].[/color] [COLOR=#FF0000]" answers of NO, the columns with NO are:<br/> "[/color] [COLOR=#990000].[/color]  [b][COLOR=#000000]printf[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$noes[/color][COLOR=#990000],[/color][COLOR=#993399]1[/color][COLOR=#990000]);[/color]
[COLOR=#FF0000]}[/color]

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[dkemas]

Thank you for the reply, it got me close. The column names aren't printing to show the no's, instead it outputs '5'.

Here's what I have (I need this to send an email but for the moment am just outputing to screen)

$rowIPC = mysql_fetch_array($resultIPC,MYSQL_ASSOC);
//now need to find which has a no to put into the email
$noesIPC=array();
foreach($rowIPC as $colNameIPC => $colvalueIPC) {
if($colvalueIPC == "n") {
$noesIPC[] =  $colNameIPC;
}
}

$rowRS = mysql_fetch_array($resultRS,MYSQL_ASSOC);
//now need to find which has a no to put into the email
$noesRS=array();
foreach($rowRS as $colNameRS => $colvalueRS) {
if($colvalueRS == "n") {
$noesRS[] =  $colNameRS;
}
}

if(count($noesIPC) > 0) {
$mail->Body   .= "There were " . count($noesIPC) . "NO's which were for questions;<br /><br />";
$mail->Body   .= "".printf($noesIPC,1)."";
} 

if(count($noesRS) > 0) {
$mail->Body   .= "There were " . count($noesRS) . "NO's which were for questions;<br /><br />";
$mail->Body   .= "".printf($noesRS,1)."";
}

The output is

There were 7 NO's which were for questions;
5

There were 4 NO's which were for questions;
5

The count is correct but it isn't giving me the column names.

The output should be

There were 7 NO's which were for questions;
mycolname1, mycolname5, mycolname7 etc etc

Thanks again

 

[dkemas]

Just having a play and if I use print_r instead of printf I get the output of the array but with lots of other stuff which will confuse the email recipients. Is there a way to just get the column names?

Thanks

 

[jpadie]

change the last lines of vacunita's answer as follows

[b][COLOR=#0000FF]if[/color][/b][COLOR=#990000]([/color][b][COLOR=#000000]count[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$noes[/color][COLOR=#990000])[/color] [COLOR=#990000]>[/color] [COLOR=#993399]0[/color][COLOR=#990000])[/color][COLOR=#FF0000]{[/color]
  [b][COLOR=#0000FF]echo[/color][/b] [COLOR=#FF0000]"There are "[/color] [COLOR=#990000].[/color] [b][COLOR=#000000]count[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$noes[/color][COLOR=#990000])[/color] [COLOR=#990000].[/color] [COLOR=#FF0000]" answers of NO, the columns with NO are:<br/> "[/color] [COLOR=#990000].[/color]  [b][COLOR=#000000]implode[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]", "[/color][COLOR=#990000],[/color] [COLOR=#009900]$noes[/color][COLOR=#990000]);[/color]
[COLOR=#FF0000]}[/color]

and use mysql_fetch_assoc rather than mysql_fetch_array/mysql_fetch_row

this also assumes that you write your query so that it only returns one row.

 

[dkemas]

That is great, thanks both

 

[vacunita]

That was my mistake, it should have been print_r rather than printf. not sure why I used that.

And yes you could have used implode or any other method to get the names out of the array.

As for the fetch_array(...,MYSQL_ASSOC) vs fetch_assoc, don't know if there's a performance difference. Personally I always use fetch_array without defining the type so it brings back both types, numeric indices, and field names.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[jpadie]

I suggested assoc so I could use implode on the key names. If we'd used fetch_row then I thing you get numeric keys only and with _array you'd get both unless you specified a second argument.
I don't know about performance of the mysql_fetch commands any more. They used to be better optimised that mysql_result but I've never done any testing for myself. I tend to use PDO these days.

 

[vacunita]

Wow!, talk about messed up. I was intending to use mysql_fetch_array, not mysql_fetch_row. Yes, Row brings back numeric indices. Array brings back both unless you specify the extra argument, and assoc brings back the field names, or aliases used in the query.

I'm going to blame Bluefish's function completion for the misused functions. Have to look closer at what it completes next time.

I tend to use PDO these days.

Apparently PHP.net is now pushing it and OO mysqli over the regular mysql library for some reason. They haven't deprecated it, they just discourage its use.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[dkemas]

Thanks both for some great information, good to look in on that discussion and learn a few things