Some update:
I have change the SQL to the following, can add more than one interface from a couple of RB’s
-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Dec 12, 2015 at 06:52 PM
-- Server version: 5.6.21
-- PHP Version: 5.5.19
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `tikstat`
--
-- --------------------------------------------------------
--
-- Table structure for table `devices`
--
CREATE TABLE IF NOT EXISTS `devices` (
`id` mediumint(9) NOT NULL,
`sn` text COLLATE utf8_unicode_ci,
`comment` text COLLATE utf8_unicode_ci,
`last_tx` int(11) DEFAULT NULL,
`last_rx` int(11) DEFAULT NULL,
`last_check` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `traffic`
--
CREATE TABLE IF NOT EXISTS `traffic` (
`id` mediumint(9) NOT NULL,
`device_id` int(10) unsigned NOT NULL,
`datetime` datetime NOT NULL,
`tx` int(10) unsigned NOT NULL,
`rx` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `devices`
--
ALTER TABLE `devices`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `traffic`
--
ALTER TABLE `traffic`
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `traffic` (`device_id`,`datetime`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `devices`
--
ALTER TABLE `devices`
MODIFY `id` mediumint(9) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;
--
-- AUTO_INCREMENT for table `traffic`
--
ALTER TABLE `traffic`
MODIFY `id` mediumint(9) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
I have made some changes to collector.php it’s working now with mysql, I’m working on it if anybody can help it would be appreciated.
<?php
include 'config.php';
include 'opendb.php';
if (isset($_GET[sn])
and isset($_GET[tx]) and is_numeric($_GET[tx])
and isset($_GET[rx]) and is_numeric($_GET[rx])) {
$device_serial = substr($_GET[sn], 0, 12);
} else {
echo '<table border="2" align="center"><tr><td>';
echo '<align="center">This PHP scripts will only run from the Mikrotik Routerboard Tikstat Script.<br />';
echo '</td></tr></table>';
exit;
}
// Delete empty records start
mysql_query("delete from devices where sn = '0'");
// Delete empty records end
$res = mysql_query("select id ,last_tx, last_rx from devices where sn='".$_GET[sn]."'");
$row = mysql_fetch_row($res);
if ($row[0]==0)
$sql = "insert into devices (sn,last_tx,last_rx,last_check) values ('".$_GET[sn]."','".$_GET[tx]."','".$_GET[rx]."',CURRENT_TIMESTAMP)";
else
$sql = "update devices set "."sn='".$device_serial."'".",last_tx='".$_GET[tx]."'".",last_rx='".$_GET[rx]."'".",last_check = CURRENT_TIMESTAMP"." where sn='".$device_serial."'";
mysql_query($sql);
mysql_close();
echo ' "OK";';
?>
Here is the include config.php
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'tikstat';
?>
Here is the include opendb.php
<?php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);
?>
Tiksat scrip update:
:local sysnumber [/system routerboard get value-name=serial-number];
:local txbyte [/interface get [find name="ether8_ADSL"] tx-byte];
:local rxbyte [/interface get [find name="ether8_ADSL"] rx-byte];
# convert to MB
# :set txbyte (($txbyte / 1048576))
# :set rxbyte (($rxbyte / 1048576))
:set txbyte (($txbyte / 1000))
:set rxbyte (($rxbyte / 1000))
:log warning "$sysnumber ...";
:log warning "$txbyte ...";
:log warning "$rxbyte ...";
/tool fetch url=("http://192.168.0.105:81/tikstat-master/collector.php\?sn=$sysnumber&tx=$txbyte&rx=$rxbyte") mode=http keep-result=no;
:log warning "Tikstat inset to DB has run...";
hope to get it working 100% 
