MySQL integration

mcotton · May 2008

I am working on remote logging using php and mysql. I am interested in re-inventing the wheel because we have multiple clients to monitor and this also allows for easy remote monitoring. Custom reports could be generated by customer ID, range of dates, sub-system, or any combination.

I would like to open this up to the community and get feedback.

This URL is subject to change so please PM if it isn't working.

I don't have a master in front of my to test with but it should be trivial to use IP_CLIENT_OPEN

To add a record into the database use this URL:
http://mcottondesign.com/Fuller/AMX_insert.php?f1=Testing&f2=Security&f3=howdy

replace value associated with f1 with the customer ID
replace value associated with f2 with the sub-system generating the event
replace value associated with f3 with the event
If you are adding spaces you'll need to have make it a valid URL by using %20

You can see the current database at:
http://mcottondesign.com/Fuller/AMX_syslog.php

For more information google sql injection.

vining · May 2008

Here's a link where you can download a complete packaged system for PHP, MySQL & Apache to run on your PC. Once the server is running you can inteface with it fairly easily using POST & GET commands to the server pages running the PHP script.

http://xampp.en.softonic.com/

mcotton · May 2008

My goal is to help others with the server side of my example.

I have modified it so that on a successful insert into the database the script with respond with 'OK' before jumping to the database view. This is very simple but I hope to help those not familiar with HTTP.

I am still making changes to it and will post the php code and mysql schema in this thread.

Here is a code example:

dvCheckIP  		= 		0:3:0      (* IP Socket *)
...
IP_CLIENT_OPEN(dvCheckIP.Port,'mcottondesign.com',80,IP_TCP)
...
DEFINE_FUNCTION updateMySQL(char f1[50], char f2[50], char f3[50])
{

	stack_var sinteger result
	result = IP_CLIENT_OPEN(dvMySQL.PORT,'mcottondesign.com',80,IP_TCP)
	wait_until(IPReadyToSend == 1)
	{
		SEND_STRING dvMySQL,
		"'GET /Fuller/AMX_insert.php?f1=',f1,'&f2=',f2,'&f3=',f3,' HTTP/1.0',13,10,
		'Host: mcottondesign.com',13,10,13,10"
		IPReadyToSend = 0
		IP_CLIENT_CLOSE(dvMySQL.PORT)
	}
	
}

Then call this function with the information you want logged:

data_event[dvTp]
	{
		ONLINE:
		{
			updateMySQL('Customer,'TouchScreen','Touchscreen&#37;20is%20Online')
		}
		
		OFFLINE:
		{
			updateMySQL('Customer','TouchScreen','Touchscreen%20is%20Offline')
		}
	}

mcotton · May 2008

The SQL query to create the table looks like this:

CREATE TABLE `Casita` (
  `Index` int(11) NOT NULL auto_increment,
  `custID` varchar(16) NOT NULL default '',
  `Date` date NOT NULL default '0000-00-00',
  `Time` timestamp(14) NOT NULL,
  `SubSystem` varchar(255) NOT NULL default '',
  `Event` varchar(255) NOT NULL default '',
  KEY `Index` (`Index`)
) TYPE=MyISAM AUTO_INCREMENT=154 ;

And the PHP looks similar to this:

<?php
	// Connecting, selecting database
	$link = mysql_connect('HOST URL', USER', 'PASSWORD') or die('Could not connect: ' . mysql_error());
	mysql_select_db('DATABASE') or die('Could not select database');

$query = "INSERT INTO `Casita` (`custID`, `Date`, `Time`, `SubSystem`, `Event`) VALUES ('$f1', CURDATE(), NOW(), '$f2', '$f3');";


print $query;
	
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

print "OK";

// Closing connection
mysql_close($link);
?>

I'm very surprised I haven't had more people playing with this. I must be on a totally different wavelength than everyone else.

Binu · May 2008

We used to connect with MS sql database. And .net front end.
For reservations, bookings fault monitoring, LON and BACKnet Conterol via OPC server, Integration with AWaya and cisco iptelephoney etc.

but these kindof projects will take a month or more to complete

DEFINE_FUNCTION CHAR[1000] USER_DATA_UPDATE () //(CHAR USERNAME[25], CHAR USERPHONE[15],CHAR USERMOBILE[15],CHAR USEREMAIL[40],CHAR USERPOBOX[10],CHAR SMSALART[3],CHAR EMAILALEART[3],CHAR FAULTREPORTING[3], CHAR DAILYBULLETIN[3],CHAR MESSAGING [3])
{
LOCAL_VAR CHAR UPDATESTR [1000]
UPDATESTR = "'http://amenities/users.asp?sql= update users set user_name=',''',
USER_INFO.USER_NAME,'%27',',%20','USER_PHONE=','%27',USER_INFO.USER_PHONE,'%27',
',%20','USER_MOBILE=','%27',USER_INFO.USER_MOBILE,'%27',',%20','USER_EMAIL=','%27',
USER_INFO.USER_EMAIL,'%27',',%20','USER_PO_BOX=','%27',USER_INFO.USER_POBOX,'%27',
',%20','USER_SMS_ALERTS=','%27',USER_INFO.USER_SMS_ALART,'%27',',%20','USER_EMAIL_ALERTS=','%27',
USER_INFO.USER_EMAIL_ALART,'%27',',%20','USER_FAULT_REPORTS=','%27',
USER_INFO.USER_FAULT_REPORT,'%27',',%20','USER_DAILY_BULLETING=','%27',
USER_INFO.USER_DAILY_BULLITIN,'%27',',%20','USER_MESSAGING=','%27',USER_INFO.USER_MASSAGING,
'%27','%20','where%20Apartment_Number=%271102%27'"
UPDATESTR = STRING_REPLACE(UPDATESTR,' ','%20')
UPDATESTR = "'GET ',UPDATESTR,' HTTP/1.0', 13, 10, 'Connection: Close', 13, 10, 13, 10"
RETURN UPDATESTR
SEND_STRING 0,UPDATESTR
}

DATA_EVENT[AMENITIES_SERVER]
{
ONERROR:
{
CLEAR_BUFFER AMENITIES_BUFFER
SEND_STRING 0, DB_GET_IP_ERROR(DATA.NUMBER)
//IP_CLIENT_CLOSE(AMENITIES_SERVER.PORT)
}
ONLINE:
{
AMENITIES_BUFFER = ''
SWITCH(PKY)
{
CASE 1:
{
IF(AME_TRACK = 6)
{
SEND_STRING AMENITIES_SERVER, CREATE_MENU_STR ()
SEND_STRING 0, "CREATE_MENU_STR (), 'this is your str'"
CALL 'CLEAR_ALL_ARRAYAS'
}
ELSE
{
SEND_STRING AMENITIES_SERVER, "URL_SENT(URL_LIST[AME_TRACK])"
}

}
CASE 2:
{
SEND_STRING AMENITIES_SERVER, ERROR_REPORTING('0',MY_IP_ADDRESS.IPADDRESS,ERROR_CODE)
SEND_STRING 0, "URL_SENT(URL_LIST[AME_TRACK])"
}
CASE 3:
{
SEND_STRING AMENITIES_SERVER, EVENT_BOOKING(THIS_ROOM_NUMBER,ORDER_FINAL[PANEL_ID].TITLE,ORDER_FINAL[PANEL_ID].EVENTDTIME,ORDER_FINAL[PANEL_ID].PRICE,ORDER_FINAL[PANEL_ID].NO_OF_TICKETS,DATE_PROCESS())
SEND_STRING 0, ERROR_REPORTING('0',MY_IP_ADDRESS.IPADDRESS,ERROR_CODE)
}
CASE 4:
{
SEND_STRING AMENITIES_SERVER, USER_DATA_UPDATE () //to update user information to database
send_string 0,USER_DATA_UPDATE ()
}
CASE 5:
{
SEND_STRING AMENITIES_SERVER,CONCIERGE_BOOKING ()
}
CASE 6:
{
SEND_STRING AMENITIES_SERVER,rESTAURANTbOOKING ()
SEND_STRING 0,rESTAURANTbOOKING ()
}
CASE 7:
{
SEND_STRING AMENITIES_SERVER,CREATE_MENU_STR()
SEND_STRING 0,CREATE_MENU_STR ()
}
}
}
OFFLINE:
{
IF(PKY <= 1 && !PROCESS)
{
PROCESS = 1
AMENITIEY_OFFLINE_PROCESS ()
WAIT 5
{
PROCESS = 0
}
}
}
}

yuri · May 2008

mcotton wrote: »

The SQL query to create the table looks like this:

CREATE TABLE `Casita` (
  `Index` int(11) NOT NULL auto_increment,
  `custID` varchar(16) NOT NULL default '',
  `Date` date NOT NULL default '0000-00-00',
  `Time` timestamp(14) NOT NULL,
  `SubSystem` varchar(255) NOT NULL default '',
  `Event` varchar(255) NOT NULL default '',
  KEY `Index` (`Index`)
) TYPE=MyISAM AUTO_INCREMENT=154 ;

And the PHP looks similar to this:

<?php
	// Connecting, selecting database
	$link = mysql_connect('HOST URL', USER', 'PASSWORD') or die('Could not connect: ' . mysql_error());
	mysql_select_db('DATABASE') or die('Could not select database');

$query = "INSERT INTO `Casita` (`custID`, `Date`, `Time`, `SubSystem`, `Event`) VALUES ('$f1', CURDATE(), NOW(), '$f2', '$f3');";


print $query;
	
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

print "OK";

// Closing connection
mysql_close($link);
?>

I'm very surprised I haven't had more people playing with this. I must be on a totally different wavelength than everyone else.

There are lots of people playing with this, there is also a product out there that does this. (really!) It's called RMS

mcotton · May 2008

yuri wrote: »

There are lots of people playing with this, there is also a product out there that does this. (really!) It's called RMS

RMS is a fine product but didn't look like the right solution for what I was trying to do. I'm already paying for hosting with comes with Apache, PHP and MySQL so there is no incremental cost. The code was easy to write (I've made it free to the public) so that is also free.

I would prefer not to do port forwarding through a customers router to retrieve log/diagnostic information. So this could fix that.

I'm still fairly new to AMX so please let me know if I'm being naive

jweather · May 2008

mcotton wrote: »

I would prefer not to do port forwarding through a customers router to retrieve log/diagnostic information. So this could fix that.

I'm still fairly new to AMX so please let me know if I'm being naive

NetLinx masters connect outbound to the RMS server, so no port forwarding is necessary unless you want to run a WebControl or something. If you set up your RMS actions correctly, though, then you shouldn't need touchpanel to operate the room remotely.

Feel free to try it out -- you can download and install RMS without a license and experiment with up to 5 rooms. You do need a Server 2003 box to install it on, but it's happy in a virtual environment if you have one set up in Virtual PC or VMWare.

yuri · May 2008

mcotton wrote: »

RMS is a fine product but didn't look like the right solution for what I was trying to do. I'm already paying for hosting with comes with Apache, PHP and MySQL so there is no incremental cost. The code was easy to write (I've made it free to the public) so that is also free.

I would prefer not to do port forwarding through a customers router to retrieve log/diagnostic information. So this could fix that.

I'm still fairly new to AMX so please let me know if I'm being naive

you're not being naive. RMS is a nice product, with rich functionality, but, since you are developing this for your self (right?) RMS would be over the top. Except if you can charge your customer for the cost of RMS.

MySQL integration

Comments