"Status" table optimization

[xander85]

Hi All,

 

I'm fairly new to MySQL and just want to make sure I wrote some good code for an important part of my project before it is released to dozens of users and I don't want it to be a performance bottleneck. Here is what I'm trying to do:

 

-Alert users on login of data that has not been submitted (application requires daily entries for each user)

 

I'm trying to figure out what the best way to write this:

 

1) Is it possible to run a query that tells me which columns in a table are empty? (i.e. entries for, say, Mon, Wed, Thur, Fri are empty)

2) Should I create a table for each user that tracks this on a weekly or bi-weekly basis?

3) Or should I just run a query on the table that needs to be updated each time the user logs in?

 

I'm trying to figure out the best way to alert a user when data needs to be entered, but I don't want run excess queries. Right now the only way I know how to check if columns are empty in X rows is to run a query on each row and use mysql_num_rows? (So this could be potentially 6 queries on login for each user)

 

Thanks!

[ignace]

1) Is it possible to run a query that tells me which columns in a table are empty? (i.e. entries for, say, Mon, Wed, Thur, Fri are empty)

 

Yes take a look at the CASE-statement for MySQL

 

2) Should I create a table for each user that tracks this on a weekly or bi-weekly basis?

 

No.

 

3) Or should I just run a query on the table that needs to be updated each time the user logs in?

 

Yes.

[xander85]

I wasn't aware of the CASE statement and it looks to be a perfect solution.

 

Thanks!

[xander85]

OK quick follow up statement:

 

I tried this query:

 SELECT CASE dow when "1" then "Entered" END from daily where storeid = '7' and wedate = '2010-04-20' 

and it returns:

 

CASE dow when "1" then "Entered" END

NULL

NULL

NULL

NULL

NULL

Entered

NULL

 

However, if I run the following:

SELECT *
FROM `daily`
WHERE `storeid` =7
AND `wedate` = '2010-04-20'
AND `dow` = '1'

 

It only returns 1 row.

 

Why does my case statement return 7 rows? The column "dow" is an enum with values 0, 1, 2, 3, 4, 5, 6, 7. Do I need to add something else to the CASE statement so force it to only check where "dow" = 1?

 

Thanks!