Counting Columns in MySQL Database that has values stored

[RootChaos]

Hi Guys

I'mlooking for a piece of code that would count the number of columns in a row that has data in them.

My table has the following columns :-

pid
q1
q2
q3
q4
q5
q6
q7
q8

I want to count the columns q1 to q8, but only those with some values in them. The blank ones should be left out...

Any help much appreciated !


Thanks

RC

[TyeM]

Try something this


$numfields = mysql_num_fields($query_result);

$row = 0; //row to check in result set, you could loop here through all rows

$count = 0;
for($i=1; $i < $numfields; $i++){ //start at field 1, bypass field 0(pid)
//check if field empty
if(empty(mysql_result($query_result, $row, $i)){
$name[$count] = mysql_field_name($query_result, $i); //store field name that is empty
$count++; //add empty field to count number of empty fields

}

}



This is coming from memory so I could be wrong.

[TyeM]

If you have '0' as a value in the columns use what is below instead. Empty() function considers 0 to empty.

if(mysql_result($query_result, $row, $i) == ''){
$name[$count] = mysql_field_name($query_result, $i); //store field name that is empty
$count++; //add empty field to count number of empty fields

}

[RootChaos]

This is my code in PHP

$qry_loaded_questions = "SELECT * from questionaire where pid = '$pid'";
$exec_qry_loaded_questions = mysql_query($qry_loaded_questions);

$numfields = mysql_num_fields($exec_qry_loaded_questions);

$row = 0; //row to check in result set, you could loop here through all rows

$count = 0;
for($i=3; $i < $numfields; $i++){ //start at field 1, bypass field 0(pid)
//check if field empty
if(empty(mysql_result($exec_qry_loaded_questions, $row, $i))){
$name[$count] = mysql_field_name($exec_qry_loaded_questions, $i); //store field name that is empty
$count++; //add empty field to count number of empty fields

}

}


This is my MySQL Table Config
'qid', 'int(10) unsigned', '', 'PRI', '', 'auto_increment'
'pid', 'char(3)', '', '', '', ''
'comments', 'varchar(255)', '', '', '', ''
'q1', 'varchar(255)', '', '', '', ''
'q2', 'varchar(255)', '', '', '', ''
'q3', 'varchar(255)', '', '', '', ''
'q4', 'varchar(255)', '', '', '', ''
'q5', 'varchar(255)', '', '', '', ''
'q6', 'varchar(255)', '', '', '', ''
'q7', 'varchar(255)', '', '', '', ''
'q8', 'varchar(255)', '', '', '', ''

This is the contents of pid = '16'

15, '16', '', 'This is Question 1', 'This is Question 2', 'This is Question 3', '', '', '', '', ''

Hence, there is only 3 questions out of the 8 filled in. The answer should be 3.

I get an error : Parse error: parse error, unexpected T_STRING, expecting T_VARIABLE or '$' in /work1/webs/consumerconnect.co.za/customer_portal/scripting/rating_calculations.php on line 19


If I replace the IF Statement with the other code, I get the incorrect values.


Thanks, Gabriel

[TyeM]

My apologies I misread your question. I thought you wanted the columns which are empty. Thats what the code I gave will result with, I didn't expect an error.

I just tested the following on a database I put up quickly and it worked.

Definately scrap the use of empty() it's not working at all for me. I don't know why.


<?php

mysql_connect("localhost", "root",'root') or die();
mysql_select_db('itemtest') or die();

$query = "SELECT * FROM itemmasterlist WHERE 1";

$result = mysql_query($query);

$numfields = mysql_num_fields($result);
$row = 0;

for($i=0; $i < $numfields; $i++){
if(mysql_result($result, $row, $i) != ''){
$name[$count] = mysql_field_name($result, $i);
$count++;
}

}

echo 'num of fields filled : '.$count.'<br />';
for($i=0; $i< count($name); $i++){
echo 'field : '. $name[$i]. ' is not empty<br />';
}

?>


The following is what I got as a result:

num of fields filled : 17
field : is not empty
field : ItemName is not empty
field : SellUnitDescription is not empty
field : SellingPrice is not empty
field : PriceCode is not empty
field : ItemDescription is not empty
field : FormDescription is not empty
field : LabelTeaUse is not empty
field : OtherName is not empty
field : BlendIngredients is not empty
field : PartUsed is not empty
field : InActiveItemName is not empty
field : InActiveItemDescriptionPartUsed is not empty
field : InactiveItemDescription is not empty
field : AccountName is not empty
field : ProductInactive is not empty
field : IndexItemType is not empty

The only strange thing is I got no field name for the first field, which should be ItemCode. i have to look into that. It does give me the correct number of fields that have data in the table.

Can you tell me what part of the code line 19 refers to?

[TyeM]

If you are placing code can you place it within tags. It will make it easier to read.

[RootChaos]

Thanks TyeM, I got this working a while ago, just forgot to post the comment.


Regards

RC