QUESTION: storing events into a mySQL database

Post by **I-1** » Fri Feb 02, 2007 3:52 pm

Hi people,

I am a little stuck with my project @ the moment.
I got a website working with a CM11(USB) with AM12 & LM12 and I am using HomeDeamon as software.

This is my next step:
I want to store the events into a MySQL Database.
So whenever the light (LM12) is turned on/off or the power (AM12) I want to store that into a database together with a date/time stamp and the devicetype.

I already have the database ready and named it "DOMOTICA"
There is 1 table in it which I called "device_status" with the following colomns:

- status
- date_time_on_off
- device_type
- location
- X10_address
- date_time_last_change

I think there is a way to store the event of doing this under a mouseclick ... but this not seems the right way ...

can someone help me out of this?

Thanks,
I-1

Post by **Digit** » Sat Feb 03, 2007 2:18 am

Hi I-1,

I don't really understand what your problem is.

You have a website running, you're using HomeDaemon, you've set up a MySQL database, and now you want to store the events. So far, i understand.

But now this mouseclick comes around. Do you mean that you don't know how to update your MySQL table when you click a button on your website?
Or is it something else? Please post some more info, and i think you'll get some more response to this topic.

Something completely different:
think very carefully about how you design your tables.
If you do not, you keep on re-designing when more and more devices, devicetypes, interfaces etc. etc. keep getting added while you expand your domotica project ...
For instance, i wouldn't put a location in a device_status table.

Regards,
Robert.

Post by **I-1** » Sat Feb 03, 2007 11:13 am

hi Robert,

Thanks for your reply.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">But now this mouseclick comes around. Do you mean that you don't know how to update your MySQL table when you click a button on your website?
Or is it something else? Please post some more info, and i think you'll get some more response to this topic.<hr height="1" noshade id="quote"></blockquote id="quote">

It's not that wise to put a RECORD UPDATE method under a button here ... so that if the button is clicked there will be a record with the exact date/time/device/localtion/ of the device that has changed from ON to the OFF state.

I want to do it with a script ... so if the device is OFF and the "status" is changed from OFF to ON then I want to have it ro create a new record in my database.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">think very carefully about how you design your tables.
If you do not, you keep on re-designing when more and more devices, devicetypes, interfaces etc. etc. keep getting added while you expand your domotica project ...
For instance, i wouldn't put a location in a device_status table.<hr height="1" noshade id="quote"></blockquote id="quote">

I agree with you here ... but I allready have the "location" column right?
Or do you mean a complete different table with the status field in there and the tables connected trough a joint.

Thanks for the advise...

I discovered that HD is using CGI based scriping ... and I am trying to find a way to use PHP together with CGI and MySQL.
google has nog helped much yet ...

Post by **Digit** » Tue Feb 06, 2007 12:08 am

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by I-1
 
I agree with you here ... but I allready have the "location" column right?
Or do you mean a complete different table with the status field in there and the tables connected trough a joint.
<hr height="1" noshade id="quote"></blockquote id="quote">
I meant the latter; for instance, i have a device status grid on my website that gathers its information from 3 tables that are joined to get the desired information.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">
I discovered that HD is using CGI based scriping ... and I am trying to find a way to use PHP together with CGI and MySQL.
google has nog helped much yet ...
<hr height="1" noshade id="quote"></blockquote id="quote">

Did you know you can run php in cgi mode?
http://www.php.net should probably give you enough information on that.

Bear in mind that i don't know HD, so don't really know how useful this tip is...[:)]

Regards,
Robert.