* A mysql table called asalmas, with he Take5 Acounting structure. * tested with php 5.3.18 and Sage 50 accounts 2013. */ require_once 'config.inc'; require_once 'funcs.inc'; // common functions // configuration $srcTable = 'SALES_LEDGER'; $srcKeyname = 'ACCOUNT_REF'; $destTable = 'asalmas'; $destKeyName = 'ACCOUNT'; // Connect to Sage and grab the source table data $fields = array(); $results = array(); $debug = 0; sage_getTable($srcTable, $debug, $fields, $results); //print_r($fields); // to show the field structure //print_r($results); // to print out the data per record //print_r($results[0]); // print first row // 1. Connect to he destination $myconn = mysql_connect($mysql['host'], $mysql['user'], $mysql['pass']); if (!$myconn) { die("Error connecting to the MySQL database: " . $mysql_error()); } if (!mysql_select_db($mysql['dbname'], $myconn)) { die("Error selecting the database: " . $mysql_error()); } // 2. Loop through the data and update the target for ($i = 0; $i <= sizeof($results) - 1; $i++) { if ($results[$i]['RECORD_DELETED'] > 0) { // ignore these
$tables = array('sales_ledger' => 'ACCOUNT_REF', 'purchase_ledger' => 'ACCOUNT_REF', 'nominal_ledger' => 'ACCOUNT_REF', 'tax_code' => 'TAX_CODE_ID', 'CURRENCY' => 'NUMBER', 'COUNTRY_CODE' => 'CODE'); //print_r($tables); // Step 2: loop though each table if (!empty($tables)) { foreach ($tables as $currentTable => $srcKeyname) { echo date("H:i:s") . " Table {$currentTable} "; fwrite($logf, date("H:i:s") . " Table {$currentTable} "); if ($debug > 0) { echo " with key {$srcKeyname} "; } $destKeyName = $srcKeyname; $destTable = $currentTable; $fields = array(); $results = array(); // Step 2a: Connect to Sage and grab the source table data sage_getTable($currentTable, $debug, $fields, $results); //print_r($fields); // to show the field structure //print_r($results); // to print out the data per record //print_r($results[0]); // print first row // Step 2b: SQL to create the table, if it does not exist $sql = "CREATE TABLE IF NOT EXISTS " . $currentTable . " (\n"; //$sql.= " " . $mysql['idfield'] . " BIGINT(20) NOT NULL auto_increment, "; foreach ($fields as $key => $value) { $sql .= " " . mysql_real_escape_string($key) . " "; if ($value == "VARCHAR") { $sql .= "VARCHAR(255)"; } elseif ($value == "INTEGER") { $sql .= "BIGINT(20)"; } elseif ($value == "DATE") { $sql .= "date"; } else {