I have an application that runs on an ASP.NET platform with MySQL as the database back end. I also use JavaScript to do much of the functionality. This system records data from a web cam as a series of images and stores them into the database as images, after being processed into video clips (.FLV files). To get the system to run you need to login as a user (eg. user1). For this account lets say you have 3 web cams - cam1, cam2, and cam3. To start a session you choose which cam will be the active cam. Let's choose cam1.
On a particular day I start cam1, and record, then stop, then start a new record session and then some time later stop. Every time you start a record session a DateTime stamp is stored in the database for that user account and the cam used. If you open cam2 and/or cam3 you can do the same.
I have a JavaScript calendar control on the page and when I choose a particular day in the calendar I can pull out all the videos recorded for that chosen day and list them for the active cam. if I choose a different day I can see the videos for that day, and so on.
As I look at the page I see the calendar control and the list of videos for that chosen day, and when I look closer at the calendar control I see that ONE day is highlighted to give me the list of videos for THAT day.
What I need is a query for my MySQL database to tell me all the dates that have videos for the active camera and I need to popuate those dates in the calendar control so that at a glance I can choose any of those highlighted dates in the calendar control and I can see the list of videos for that chosen date. Obviously if there were no recordings for that day then the day would not be highlighted.
Here is the table, fields and field types I am working with:
IMAGE table
-----------------
IMAGE_ID - int (autonumber)
IMAGE_RECEIVED - DateTime
CAMERA_ID - INT
IMAGE - BLOB
CUSTOMER_ID_FK - INT
Example sessions as shown in the databae via a dump:
--------------------------
----------
----------
----------
----------
----------
----------
----
IMAGE_ID CAMERA_ID CUSTOMER_ID_FK IMAGE IMAGE_RECEIVED
('1', '6997', '1', '(Binary)', '10/24/2008 6:57:35 PM'),
('2', '6997', '1', '(Binary)', '10/27/2008 11:41:49 AM'),
('3', '6997', '1', '(Binary)', '10/27/2008 11:41:58 AM'),
('4', '6997', '1', '(Binary)', '10/27/2008 11:42:10 AM'),
('5', '6997', '1', '(Binary)', '10/27/2008 12:29:21 PM'),
('6', '6997', '1', '(Binary)', '10/27/2008 1:06:03 PM'),
('7', '6997', '1', '(Binary)', '10/27/2008 2:21:13 PM'),
('8', '6997', '1', '(Binary)', '10/27/2008 2:56:28 PM'),
('9', '6997', '1', '(Binary)', '10/27/2008 4:30:44 PM'),
('10', '6997', '1', '(Binary)', '10/27/2008 4:36:06 PM'),
('11', '6997', '1', '(Binary)', '10/27/2008 4:36:21 PM'),
('12', '7024', '113', '(Binary)', '10/27/2008 6:00:19 PM'),
('13', '7024', '113', '(Binary)', '10/27/2008 6:00:21 PM'),
('14', '7024', '113', '(Binary)', '10/27/2008 6:01:51 PM'),
('15', '7024', '113', '(Binary)', '10/27/2008 6:20:45 PM'),
('16', '7024', '113', '(Binary)', '10/27/2008 6:20:59 PM'),
('17', '6997', '1', '(Binary)', '10/28/2008 2:46:55 PM'),
('18', '6997', '1', '(Binary)', '10/28/2008 3:02:07 PM'),
('19', '6997', '1', '(Binary)', '10/28/2008 3:02:23 PM'),
('20', '6997', '1', '(Binary)', '10/28/2008 4:25:01 PM'),
('21', '6997', '1', '(Binary)', '10/28/2008 4:42:34 PM'),
('22', '6997', '1', '(Binary)', '10/28/2008 4:42:44 PM'),
('23', '6997', '1', '(Binary)', '10/28/2008 4:43:05 PM'),
('24', '7050', '1', '(Binary)', '10/30/2008 5:47:04 PM'),
('25', '7050', '1', '(Binary)', '10/30/2008 5:49:20 PM'),
('26', '7050', '1', '(Binary)', '10/30/2008 5:49:21 PM'),
('27', '7050', '1', '(Binary)', '10/30/2008 5:49:32 PM'),
('28', '7050', '1', '(Binary)', '10/30/2008 5:49:47 PM'),
('29', '7050', '1', '(Binary)', '10/30/2008 5:50:48 PM'),
('30', '7092', '122', '(Binary)', '11/4/2008 10:14:12 PM'),
('31', '7032', '113', '(Binary)', '11/5/2008 12:23:22 PM'),
('32', '7032', '113', '(Binary)', '11/5/2008 12:23:29 PM'),
('33', '7026', '118', '(Binary)', '11/5/2008 2:58:15 PM'),
('34', '7032', '113', '(Binary)', '11/5/2008 3:29:14 PM'),
('35', '7032', '113', '(Binary)', '11/5/2008 3:29:16 PM'),
('36', '7032', '113', '(Binary)', '11/5/2008 3:29:18 PM'),
('37', '7050', '1', '(Binary)', '11/10/2008 3:13:28 PM'),
('38', '7050', '1', '(Binary)', '11/10/2008 3:13:45 PM'),
('39', '7050', '1', '(Binary)', '11/10/2008 3:34:23 PM'),
('40', '7050', '1', '(Binary)', '11/10/2008 3:34:25 PM'),
('41', '7050', '1', '(Binary)', '11/10/2008 5:10:55 PM'),
('42', '7032', '113', '(Binary)', '11/10/2008 5:41:06 PM'),
('43', '7032', '113', '(Binary)', '11/10/2008 5:41:12 PM'),
('44', '7032', '113', '(Binary)', '11/10/2008 5:41:29 PM'),
('45', '7032', '113', '(Binary)', '11/10/2008 5:45:31 PM'),
('46', '7032', '113', '(Binary)', '11/10/2008 5:48:01 PM'),
('68', '7050', '1', '(Binary)', '11/11/2008 1:25:01 PM'),
('69', '7050', '1', '(Binary)', '11/11/2008 1:48:30 PM'),
('70', '7050', '1', '(Binary)', '11/11/2008 1:54:34 PM'),
('71', '7050', '1', '(Binary)', '11/11/2008 1:55:48 PM'),
('72', '7050', '1', '(Binary)', '11/11/2008 1:55:55 PM'),
('73', '7050', '1', '(Binary)', '11/11/2008 1:55:59 PM'),
('74', '7050', '1', '(Binary)', '11/11/2008 1:56:45 PM'),
('75', '7050', '1', '(Binary)', '11/11/2008 1:58:52 PM')
Currently I CAN choose a day and view the image clips for that day, if there are images for that day. If I want to see if any other days have data, I have to click each day and hope I can see data for that date. I just want to be able to have my query do the work for me and let me know which days have images when I have an active CAMERA and have that information populated in my calendar control as highlighted dates so that I can choose a date that HAS data instead of having to search for a day that has data.
