Acinic and Real-time databases - My final solution?

[gabrielcrowe]

Acinic and Real-time databases - My final solution?

This is (hopefully) the building blocks for a smarter system, allowing Actinic to host some useful data online.

This technique aims to:
- Be simple to install (paste 1 block of code)
- Not slow down Actinic
- Be transparent and real-time
- Store product data online.
~ name
~ price
~ image
~ url
~ stock


Skill level required:
Advanced

Prerequisites:
- Actinic V9
- A MySQL Database that can accept remote connections
- A text editor

The Beef:
First of all, we'll need the MySQL ODBC Connector, from the nice people over at mysql.
http://dev.mysql.com/downloads/connector/odbc/5.1.html

After install, you'll need to configure the ODBC connector, so that Actinic can talk to your online MySQL Database. This is not covered here, but I suggest you start by heading off to your control panel, then Admin tools, then Data sources (ODBC). Set up a new data source there, using the new mysql driver, that you installed. Remember the DSN name we used. You'll need it later.

Right, so our Mysql ODBC link is set up and you clicked the test button right? and it worked? right? ok, lets continue.

Here is the required code:

<actinic:block if="IsPreviewMode%20%3d%3d%20true" >
<actinic:block php="true" selectable="false" >

//################################
//# product variables
//################################
$actref=("<actinic:variable name="ProductReference" encoding="perl" selectable="false" />");
$actname=("<actinic:variable name="ProductName"  encoding="perl" selectable="false" />");
$actprice=("<actinic:variable name="ProductPriceRaw" encoding="perl" selectable="false" />");
$actimage=("<actinic:variable name="ProductImageFileName" encoding="perl" selectable="false" />");
$actstock=("<actinic:variable name="StockLevel" />");


//################################
//# Some settings
//################################
$dsnname="actiniconline";
$localDSN="ActinicCatalog9";
$tablename="odbcproduct";



//################################
//# LOCAL ODBC Fetch page name, and parent section...
//################################
//get the parent section filename...
		$connect = odbc_connect($localDSN,"","");
		
		$query = "SELECT [nParentSectionID] FROM [product] WHERE [Product Reference]='".$actref."'";
		$result = odbc_exec($connect, $query);
		
		while(odbc_fetch_row($result)){
			$parentid = odbc_result($result, 1);
		}
		
		$query = "SELECT [sPageName] FROM [Catalog Section] WHERE [nSectionID]=".$parentid."";
		$result = odbc_exec($connect, $query);

		while(odbc_fetch_row($result)){
			$actlink = odbc_result($result, 1);  
		}
		odbc_close($connect);

//################################
//# REMOTE ODBC connection
//################################
$conn=odbc_connect($dsnname,"","");
if (!$conn)
  {exit("Connection Failed: " . $conn);}


//################################
//# create our table, if not exists.
//################################
$createsql = "CREATE TABLE IF NOT EXISTS `$tablename` (
`nref` TEXT NOT NULL ,
`sname` TEXT NOT NULL ,
`nprice` TEXT NOT NULL ,
`simage` TEXT NOT NULL ,
`slink` TEXT NOT NULL ,
`nstock` TEXT NOT NULL ,
`nparentcat` INT NOT NULL
) ENGINE = MYISAM 
";
$result = odbc_exec($conn, $createsql);
  

	
//################################
//# ODBC select
//################################

$sql="SELECT nref FROM $tablename WHERE nref = '$actref'";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit('Error in SQL');}

$recordexists=false;
while (odbc_fetch_row($rs))
  {
  $recordexists=true;
		$nref=odbc_result($rs,'nref');
  }

  if ($recordexists==true) {
	//update this record
	$updatequery = "UPDATE $tablename SET sname='$actname',nprice='$actprice',simage='$actimage',slink='$actlink',nstock='$actstock',nparentcat='$parentid' WHERE nref='$actref'";
	$result = odbc_exec($conn, $updatequery);	
  } else {
	//add a new record
	$insertquery = "INSERT INTO $tablename VALUES('$actref','$actname','$actprice','$actimage','$actlink','$actstock','$parentid')";
	$result = odbc_exec($conn, $insertquery);

  }


//################################
//# Close the connection
//################################
odbc_close_all();


</actinic:block>
</actinic:block>

In the code above, find this part:

$dsnname="actiniconline";

change 'actiniconline' to the name we chose for our system DSN earlier, when we set up the MySQL ODBC Connector.

Go and find your product template, and paste this at the end of it. I pasted mine after everything in 'Standard Layout Using CSS'. after all the blocks and everything. Really as long as this script gets executed only once, in the context of the product, so that product data can be passed into it, then you should be fine.

Now, press apply.

Go and check your online database. You should see a record, your product, and its data should be there. Go back to actinic, change the price, click apply, return to your online database and... thats right, it should have changed.

I dont need to tell anyone the sort of cool searches and things that can be built with this data alone. How about a real time stock checker tool for your customers? or an ajaz search like the ones provided by shoptacular and things like ?

How does it work then?
Actinic has a built in ability to connect to ODBC databases. Installing the MySQL connector allows us to send data through this bridge, directly to a MySQL database online. When we look at a product in the preview, Actinic executes some PHP, that collates some information about the product, then sends it through the connector and subsequently, online. If the product exists, its updated. If it does not, we create it.

I need help:
I need people to test this and let me know how they got on. Particularly people like ferg, who I know stull struggles with this sort of thing. This project is ADVANCED, so if you're stuck, i'll try to help you, but things like setting up the ODBC connector are not part of the problems faced by this type of solution. There are loads of coding errors and other daft things, so i'm throwing it out there, to get some help. There are loads of logic problems with this type of solution, i'd appreciate some feedback.

Obligatory warning:
BACK EVERYTHING UP!! IM NOT RESPONSIBLE FOR YOUR DATA LOSS, SHOULD ANY OCCUR.