I've looked everywhere on how to do this and it seems like an awful long approach to output 1 number.
This is the layout of my database:
UserId Info1 Info2 Info3 Info4 Info5 Info6 Info7
-------------------------------------------------------------------------------
user1 | xyz | | abc | xyz | | abc |
user2 | | abc | | def | ghi | wxy | jkl
What i want to do is return a number for example:
User1 has completed 4 Tasks
User2 has completed 5 Tasks
There are 95 columns in this row so as you can understand it would be a real pain to count each one individually. However i only want to count the ones that are not empty and return a number.
<?
$sql="SELECT * FROM builder WHERE userid = '".$userid."' IS NOT NULL";
$query=mysql_query($sql);
$num=mysql_num_fields($query);
echo $num;?>
This outputs how many rows there are in total regardless of whether they are empty or not so the number is always the same which isn't what I want.
Any suggestions?
You can count the values in MySQL using this construct:
select ((info1 is not null) +
(info2 is not null) +
(info3 is not null) +
(info4 is not null) +
(info5 is not null) +
(info6 is not null) +
(info7 is not null)
) as NumNotNull
MySQL treats boolean expressions as integers, in a numeric context. TRUE is "1", so you can just add them up to get the total that match.
I apologize for the partial answer here (i.e. I haven't written out the proposed stored procedure) but perhaps this will be a start for you. My approach will take some time to write but will be particularly useful if new tasks are sometimes added as new columns in your builder table.
The approach is as follows: create a stored procedure that pivots your data. You then query off the result set generated by that procedure so that you query off a data set that looks like the following:
userID task Complete
------ ---- --------
user1 Info1 True
user1 Info2 False
user1 Info3 True
user1 Info4 True
user1 Info5 False
user1 Info6 True
user1 Info7 False
user2 Info1 False
user2 Info2 True
user2 Info3 False
user2 Info4 True
user2 Info5 True
user2 Info6 True
user2 Info7 True
I don't believe MySQL has a pivot command but you can imitate one by writing a stored procedure that dynamically generates an SQL statement as it reads off the built in INFORMATION_SCHEMA COLUMNS Table.
The dynamic SQL would look something like:
SELECT userID, 'Info1' AS Task, info1 IS NOT NULL FROM builder
UNION
SELECT userID, 'Info2' AS Task, info2 IS NOT NULL FROM builder
UNION
...
You'd have to play with this a while but I think it will give you a result that could be more useful. BTW, if it's possible to redesign the builder table, a design that looks like what this stored procedure returns might be easier to work with.