I'm currently trying to improve on my knowledge of MySQL and PHP, and have run into a snag that I need a little help with.
I am trying to create a website that displays a table of information, namely numbers of "hours" someone has been active, every day.
I think I've confused myself and made the process way more complicated than it actually is, as I've created a table for "Dates" and another table for "User Information"
A demo of what I'm trying to do is here (it's currently not liked to SQL or anything as I'm trying to get the databases sorted first )
As you can see, I'm trying to display the number of hours below each date in a simple calendar-like table. (To be improved on later ) - Again, the problem I'm having is how to store the data so retrieving it is simple for the sql code.
You can make the dates table simple but making it have just 2 columns. The first the UserID thus making it have a common field with the users table for JOINS during data extraction.
You can the store data by comma separating every days data. For example store like this: 1/3/2010-3,1/4/2010-3,1/5/2010-3 blah, blah.
During data extraction you can then use the explode() function to separate days first on the comma , then use it again to separate each days from its hours using the hyphen.
If you use a week-hours format like week 4-3 hours, you can implement 4-3, blah, blah
__________________
For professional Hosting and Web design.....
CREATE TABLE userActivity (
userid INT FOREIGN KEY users(userid), /* refers to your users table */
startTime DATETIME,
endTime DATETIME
);
Then you could get the time spend by a given user on each day of a given month thus:
Code:
SELECT DAY(startTime) AS theDay, SUM( TIMESTAMPDIFF( MINUTE, startTime, endTime ) ) AS minutesPerDay
FROM userActivity
WHERE YEAR(startTime) = 2012 AND MONTH(startTime) = 10
GROUP BY DAY(startTime)
ORDER BY theDay
Your PHP code would read that and convert the minutesPerDay value into hours and minutes, for example.
You *could* have a table for dates, and then LEFT JOIN from the dates table to this one, but there's no real need for that.
Your PHP code can easily detect if there is a DAY missing during the month (when the user had no activity) and dump out a ZERO in that day's slot in the calendar.
NOTE: You don't have to do this for whole months. You could easily do, say, the last 4 weeks instead.
Code:
SELECT MONTH(startTime) AS theMonth, DAY(startTime) AS theDay,
SUM( TIMESTAMPDIFF( MINUTE, startTime, endTime ) ) AS minutesPerDay
FROM userActivity
WHERE startTime > DATE_SUB( CURDATE(), INTERVAL 27 DAY )
GROUP BY MONTH(startTime), DAY(startTime)
ORDER BY YEAR(startTime), theMonth, theDay
That query gets you data for last 28 days, including today, just as an example.
NOTE: If a user is active starting on one day and the activity slops over midnight and into the next day, the above code will record that activity as all being in the day when the activity started. We *could* write a query that would split that activity between the two days, if it's important to you, but it's much more complex. A better answer would be to have your PHP code that does the recording of the activity notice that this happened and then insert *TWO* records into the table, one per day.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Old Pendant if I may ask, your DB structure and the one I suggested, which one is faster and more efficient? I think you are a DBA, if so you have done a lot of query profiling. My approach only needs one query while yours will take several especially if the user is a regular. In this case we take tht it involves several rows.
And why is my idea so terrible?
__________________
For professional Hosting and Web design.....
If the table is properly indexed, it will only take a bit longer for mine to execute.
But what you are missing is that NOW your code has to use PHP (or other language) to parse through that delimited string and make sense out of it. Do you really think that PHP code is faster than MySQL code?
But the real reason your solution is bad is because it doesn't provide for *ANY* of the many many queries that you can perform on a properly structured DB.
Examples:
-- Find me all users who were active any time between 9PM and midnight on October 3rd.
-- Find me all users who were active more than 20 hours during the week starting October 7th.
-- Find me all users who were active on 3 consecutive days or more during the month of September
And many more. Your solution would mean you have no choice but to pull *ALL* the records from the DB over to PHP and start parsing the strings and collecting data and and and... Whereas with the properly NORMALIZED database all those queries are, if not trivial, quite easy (and fast) using SQL. (In fact, only the 3rd of those would make me scratch my head for more than 15 seconds.)
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Anyway, it won't take more than two google searches on "normalizaton" to show you that my opinion is shared by virtually *ALL* authors on database design.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
I don't doubt that, I only needed a reminder. What I was really forgetting is the fact that MySQL code is always faster than PHP code due to the fact that MySQL executes using C++.
__________________
For professional Hosting and Web design.....
To Pendant; The activity is entered via a form for say; 2 hours on the 21st.
The activity will never end unless the user disappears, and if that happens I would just write a sql query and button form to move the user to an archive table.
Thank you all for your help so far, it's been great
Let me explain fully again and with a bit more detail xD
Members of the group have to make 4 hours of activity a week to keep active status in the group.
If a member does 2 hours (in a day, doesn't matter what time, and we don't need to know what time), I enter it into the form and it updates in the database to say "2 hours on the DATE of MONTH"
Then the php on the website calls all that information and just displays it in a simple calendar like format as per the link I gave in the original post. (per user)
Also; The "Activity" is based on whether they play a certain game for ## hours each day/week, not web activity xD If i could somehow link it to their XFire or Steam, it would be much easier hehe xD
Thanks for your help so far though, I'm learning a lot
Before you post, read our: 
)
) - Again, the problem I'm having is how to store the data so retrieving it is simple for the sql code.
