Retrieving different chunks of data

[Francoise]

I've been trying to learn some MySQL and some PHP for a while, and have recently even made a little progress. My goal is to create a very specific, fairly simple program that I and some friends can use on my website.

I have a feel for how to design the database; and I have a pretty fair sense of how to insert data into the database. Where I still feel very unsure is in how I will retrieve the data in the various ways that I would like to.

Basically there will be only two kinds of data: numerical ratings and times (of day/week/month). You might want, for example, to record your pulse once an hour for eight hours each day.

In terms of data retrieval, I know I'm going to want to be able to get "chunks" of data back: pulse readings for every Monday in May, for example; pulse readings for mornings (defined as 9 AM-12 AM) for the week of April 1-7; and even maybe a graph for a given week or month.

I don't have much idea how to phrase a data retrieval question that would return those kinds of results. In terms of database design, a lot of it is taken care of by using a timestamp column. Would that be adequate for days of the week, though?

Please, can anyone tell me where to look to learn more about these kinds of data retrieval queries? If I can just find the right materials, I'm ready to dive into them and work hard. I have the Larry Ullman book on PHP and the one on Dynamic Websites, but they both seem a bit weak on the varieties of data retrieval.

[bradgrafelman]

Francoise wrote:

Would that be adequate for days of the week, though?

Yes, because most DBMS's have a set of date and time functions that allow you to transform their native datetime/timestamp datatype into whatever you need it to be.

For example, MySQL's Date and Time Functions contain a function called DAYOFWEEK() as well as one called MONTH() which, when the two are combined, would allow you to select the data you gave as an example.

[Francoise]

Brad, thank you very much for our reply. That's terrific news about the DAYOFWEEK() function. Any thoughts about how I can inform myself about how to do some of the other queries I described?

[bradgrafelman]

Which other ones? It's the same basic principle.. e.g. for the time slots, you'd use HOUR() to see if the timestamp was greater than or equal to the start time and less than or equal to the end time.

[Francoise]

Could it really be that simple?? I could construct all those other queries, just using TIMESTAMP, MONTH and DAYOFWEEK? For example, I could retrieve all the readings for Spring (Mar, Apr, May)? And I could look at all the readings above, say, 70 for the month of November? Or for the week starting December 1 and ending December 7? That would be awesome!