Count number of occurences of a value in different columns in *one* row/record

Question

I might be doing this the wrong way when I set up the tables? I'm using mysql & php. Loads of googleing only shows how to count how many times a value appears in several rows, I want check for a value in many different columns but in the same record. (I think this: count number of columns that have data for each row just might be about the same thing, maybe? but I don't get it.)

I have a table with goals that I hope to work on and achieve every day. So for every day I wish to mark: "success" or "fail". And insert is working great. How ever I'm looking for a way to calculate the number of success& number of fails of "today", to show the right kind of smiley which will be encouraging or sad depending on number of fails and number of successes.

For example:

ID  date          drinkMoreWater    goToBedEarlier    callADearFriend
1   2012 jan 15   fail              fail              fail
1   2012 jan 16   success           _(still empty)    success

So if today is jan 15 the smiley will be very very sad. If today is jan 16 the smiley will be really really hppy with stars in it's eye's (atleast until I fail goal 2 ;) )

Answer 1

Your tables should actually be structured differently to make your life easier:

Goals:
ID     Goal
1      drinkMoreWater
2      goToBedEarly
3      callADearFriend

Status:
ID     Status
1      Success
2      Fail

Tracking:
ID     Date      Goal_ID     Status_ID
1      1/1/2012  1           1
1      1/1/2012  3           2
1      1/2/2012  2           1
1      1/2/2012  4           1

Now you could easily add goals and status (e.g. 'Working on it') and your table structure does not have to change to accommodate your changes, your queries become a lot simpler as well.