Page 1 of 1

calculate percentage of fields with data

Posted: Sun May 03, 2009 8:31 pm
by rosslad
Hi there,

I have said before that I am not really used to asking for help as I like to figure things out for myself but again here I am with another problem that has me :banghead:

I am trying to figure out how to calculate all the fields in a certain mysql table that has data from the overall fields.

so User Table has say 20 fields and the user with the id of 1 only has 10 of them with data in, I need to write a function that can easily count all fields and then count only the fields with data and display this as a percentage.

Can this even be done? I have tried to the best of my knowledge but come up empty 8O
If anyone can show me the way I would be 3 days worth of grateful to you :wink:

Ross

Re: calculate percentage of fields with data

Posted: Mon May 04, 2009 1:34 am
by McInfo
By your definition, if a field is not a "field with data" do you mean it is NULL, an empty string, or either?

Edit: This post was recovered from search engine cache.

Re: calculate percentage of fields with data

Posted: Mon May 04, 2009 2:51 am
by McInfo
I think I misread your question. I wrote a script to retrieve a count of all of the cells in the table that are not null. After rereading your post, it looks like you just want to find the number of cells in a single row.

For the total number of fields returned in a result, use mysql_num_fields().

For the number of filled-in fields, loop through the result and use empty() to check the values.

Edit: This post was recovered from search engine cache.

Re: calculate percentage of fields with data

Posted: Mon May 04, 2009 10:50 am
by rosslad
Ok sorry for not explaining it properly...

Thanks for the tips, so I now have:

Code: Select all

 
$total_cnt = mysql_query("SELECT * FROM user_table WHERE id = '$userid'");
 
$cnts = mysql_num_fields($total_cnt);
 
        foreach ($cnts as $totals) {
 
        if (empty($totals)) {
$fields = 0;
} else {
$fields = 1;
}
}
$total_counts = $fields * 100 / $totals;
 
but its not showing any result, probably my code is flawed.
Any help with the above would be awesome thanks.

Ross

Re: calculate percentage of fields with data

Posted: Mon May 04, 2009 11:10 am
by McInfo
$cnts is a number (or false), not an array. You need to loop through the result set returned by mysql_query() to find the number of empty fields for the row. The total number of fields is returned by mysql_num_fields().

Even better, read this:
PHP Manual: mysql_fetch_lengths().

Edit: This post was recovered from search engine cache.