I am just learning mySQL and PHP. Needless to say, I am a noob and this is I’m sure a very rudimentary question. I would do a little more research, but I don’t really even know how to ask the question (I told you I was new :))
So, my first database driven site that I am trying to build is a sleep logger. The functionality is very simple: onclick of the “asleep” button LOCALTIMESTAMP is submitted to mySQL in a table that has ID, DATETIME, and USERID as it’s columns. The same functionality is triggered for the “awake” function and is stored in a different table.
The challenge: I would like to JOIN the asleep record with the awake record as one “sleep session” to be displayed as a list on the website. I can do a basic join on ID of these 2 tables, but the obv isn’t good design and doesn’t support multiple users. I am sure at some point the IDs of asleep and awake would not match properly. I thought about using DATETIME, USER and ID, but could still run into problems if the user had several sleep sessions in a single day.
So, I told you, pretty basic, but I am stuck. A solution or a nudge in the right direction would be much appreciated!
I guess your problem is a wrong database design.
Instead of using two tables, I think it’s better to use just one, sleepsessions, with the following columns:
id (autoincremental)
userid
startofsleep
endofsleep
Of course, since one can’t wake up before going to sleep, and one can’t go to sleep a second time if one didn’t wake up in the mean time, your application will have to make sure that a user has to click the sleep button first, then the awake button, then the sleep button, etc.
When the user clicks the sleep button, you add a row to the table with userid and startofsleep (timestamp). The id will be added automatically (because you’ve made the column autoincremental). Let’s say that the endofsleep column will have value NULL.
When the user clicks the awake button, you’ll update the row with userid = this users userid and endofsleep = NULL, and put the current timestamp in endofsleep.
Thanks Guido! Seems like the most logical solution.
You mentioned that I should only allow the user to click the awake button after the asleep button. Is there an easy way to program a button in PHP that toggles from asleep to awake based on the criteria you mentioned?
Well, there’s some logic in it, isn’t there? Since you want to log sleep time, one has to go to sleep first, and then wake up
Of course, I don’t know the context of your website. If it’s just an exercise, you can define your own rules, and keep it relatively simple. If it’s a real world project, then things get more complicated because people are involved. They might forget to click when they go to sleep, or when they wake up.
But if we stick to the sleep-awake-sleep-awake-sleep etc routine, then you can decide which button to show the user simply by querying the database for the user’s userid and the value NULL in the endofsleep column.
If there is such a row, it means the user is currently asleep, and you show the wakeup button.
If there isn’t, the user is awake and you show the gotosleep button.
