i just wrote a class that allows executing RouterOS command like SQL command (select, insert, update, delete).
i wrote this class in the PHP programming language and is still dependend to the class written by Denis Basta (denis.basta@gmail.com).
the idea is using menu names instead of table names. The menu structure is separated by (-) character which is stored in the “*.ini” file.
for example ip-pool will access pool menu as table name or interface-wireless will access wireless menu as table name. More detail fork me on github (https://github.com/tauhidcp/PHPMikAPISQL)
CONNECT
<?php
require('../PHPMikAPISQL.php');
$config['host'] = "192.168.56.101";
$config['user'] = "admin";
$config['pass'] = "";
$config['port'] = "8728";
$MikSQL = new PHPMikAPISQL($config);
?>
SELECT
Retrieves data from the router. to print the router’s interface list you can use “select * from interface” or print only specific item “select .id, name from interface”. this command can be combined with the search clause where or/and sorting using “order by .id asc” or “order by .id desc”. use limit keyword at the last to limit output “select * from interface limit 2”. also, you can search with pattern using like keyword “select * from log where message like ‘via api’ order by .id desc limit 3”
<?php
# Example Select
require('connect.php');
# Select all
$print = $MikSQL->ExecuteSQL("select .id,time,message from log where message like 'via api' order by .id desc limit 5");
#$print = $MikSQL->ExecuteSQL("select .id,name,type,mac-address from interface where name like 'eth'");
#$print = $MikSQL->ExecuteSQL("select .id,name,type,mac-address from interface where name like 'isp' and type='ether' order by .id desc limit 2");
#$print = $MikSQL->ExecuteSQL("select * from interface order by .id desc");
if ($print['status']!="FALSE"){
//print_r($print['data']);
// or
// print specific log item
foreach ($print['data'] as $row){
echo $row['.id']."<br>";
echo $row['time']."<br>";
echo $row['message']."<br>";
echo "-----------------------<br>";
// dst...
}
// or
// print specific interface item
/*foreach ($print['data'] as $row){
echo $row['.id']."<br>";
echo $row['name']."<br>";
echo $row['mac-address']."<br>";
echo "-----------------------<br>";
// dst...
} */
} else {
echo $print['message'];
}
?>
INSERT
Add config item to the router. to add user to the router you can use “insert into user (name, group) values (‘budi’, ‘full’)”. just change the table’s field with menu’s attribute.
<?php
# Example Insert
require('connect.php');
# Insert New User
$user = "budi";
$group = "full"; # full/read/write
$exec = $MikSQL->ExecuteSQL("insert into user (name,group) values ('".$user."','".$group."')");
# Insert New IP Address
#$address = "192.168.88.1/24";
#$network = "192.168.88.0";
#$interface = "ether4";
#$exec = $MikSQL->ExecuteSQL("insert into ip-address (address,network,interface) values ('".$address."','".$network."','".$interface."')");
if ($exec['status']=="TRUE"){
echo $exec['message'];
} else {
echo $exec['message'];
}
?>
UPDATE
Update the router config item. to edit user from the router you can use “update user set group = ‘read’ where .id = ‘*2’”. similar to insert command, the table’s field is menu’s attribute.
<?php
# Example Update
require('connect.php');
# Update User
$group = "read"; # full/read/write
$id = "*4";
$exec = $MikSQL->ExecuteSQL("update user set group='".$group."' where .id='".$id."'");
# Update IP Address
#$address = "172.188.77.1/23";
#$network = "172.188.77.0";
#$interface = "ether4";
#$id = "*4";
#$exec = $MikSQL->ExecuteSQL("update ip-address set address='".$address."',network='".$network."',interface='".$interface."' where .id='".$id."'");
if ($exec['status']=="TRUE"){
echo $exec['message'];
} else {
echo $exec['message'];
}
?>
DELETE
Remove config item from the router. to remove user from the router you can use “delete from user where .id = ‘*2’”.
<?php
# Example Delete
require('connect.php');
# Delete User
$id = "*4";
$exec = $MikSQL->ExecuteSQL("delete from user where .id='".$id."'");
#Delete IP Address
#$exec = $MikSQL->ExecuteSQL("delete from ip-address where .id='".$id."'");
if ($exec['status']=="TRUE"){
echo $exec['message'];
} else {
echo $exec['message'];
}
?>