php/SQL-Help for Geofence-Project? (1 Viewer)

[The_Stig]

Yeah! Will try at the weekend. Thanks again

 

[The_Stig]

Sooooo...tried a bit around. Did get a bit further, but not working.

What I have working so far is a simple php activating and deactivating it. Had to change the script I posted here, because this did not work (don't know why). This one works (and yes, I know, the if else isn't nice, but I don't know how to remove it ;-)

<?php
//Authentication
$json = file_get_contents('http://IP:PORT/webapi/auth.cgi?api=SYNO.API.Auth&method=Login&version=3&account=user&passwd=PASSWORD&session=SurveillanceStation&format=sid');
$obj = json_decode($json, true);
$sid = $obj["data"]["sid"];

//GetList
$json = file_get_contents('http://IP:PORT/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=List&version=3&_sid='.$sid);
$obj = json_decode($json, true);
$cam_activated = $obj["data"]["cameras"][0]["enabled"];

if($cam_activated == false){
    //Deactivate cam
    $json = file_get_contents('http://IP:PORT/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=2&_sid='.$sid);
    echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/on_icon.png\" style=\"width:auto;height:auto;\" /></div>";
}
else
{
    //Deactivate cam
    $json = file_get_contents('http://IP:PORT/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=2&_sid='.$sid);
    echo "<div class=\"centered\"><img src=\"https://externeAdresse:Port/off_icon.png\" /></div>";
}
?>

What I've done in php is this:

I guess some of the types are wrong?

And I have this now as php script:

<?php
$httpformat="http"; //hier angeben ob http oder https
$port="5000"; //port
$loginname="administrator"; //Loginname an der Surveillance Station
$passwd="Password"; //passwort an der Surveillance Station
$ip="IP"; //ip adresse der Surveillance station im LAN
$cam_id="2"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
// db
$db_name = "root";
$db_pw = "Password";
$db_database = "Surveillance";
$db_table = "user";
$db_ip = "127.0.0.1";

// activate Surveillance
$surveillance = true;
// connect to db
$connection = mysql_connect(db_ip, $db_user,$db_pw) or die ("No connection. User or password is wrong");
// select table
mysql_select_db($db_database) or die ("The Database ". $db_database ." doesn't exist");

// update table
$query = "UPDATE ".$db_table." Set available = ".mysql_real_escape_string($_GET['available']).", time = NOW() WHERE id = ".mysql_real_escape_string($_GET['id'])." AND pw='".mysql_real_escape_string($_GET['pw'])."'";
$update = mysql_query($query);

if (!$update) {
    die('Error: ' . mysql_error());
}

// select info

$query = "SELECT available FROM ".$db_table;
$result = mysql_query($query);
while($row = mysql_fetch_object($result)) {
   if ($row->available == true)
    $surveillance = false;
}
//Login an der Surveillance Station:
$json = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&method=Login&version=3& account=".$loginname."&passwd=".$passwd."&session= SurveillanceStation&format=sid'");
$obj = json_decode($json, true);
$sid = $obj["data"]["sid"];
if ($surveillance) {
//Kamera aktivieren:
$json = file_get_contents($httpformat."://".$ip.":".$port."/webapi/_______________________________________________________entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=".$cam_id."&_sid=".$sid);
}else {
//Kamera deaktivieren:
}

//Logout Surveillance Station:
$out = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&version=3&method=Logout &session=SurveillanceStation");

?>

Getting from this php: "Warning: mysql_connect(): php_network_getaddresses: gethostbyname failed. errno=0 in /volume1/web/alarm.php on line 18 Warning: mysql_connect(): php_network_getaddresses: gethostbyname failed. errno=0 in /volume1/web/alarm.php on line 18 No connection. User or password is wrong"

But root has access to this db. Checked this. Can you lead me further?

 

[FreakyJ]

Haha easy error xD

$connection = mysql_connect($db_ip, $db_user,$db_pw) or die ("No connection. User or password is wrong");

A $ was missing^^

 

[The_Stig]

Haha easy error xD

$connection = mysql_connect($db_ip, $db_user,$db_pw) or die ("No connection. User or password is wrong");

A $ was missing^^

Tried that already, but this leads to:

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in /volume1/web/alarm.php on line 23 Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' time = NOW() WHERE id = [1] AND pw='[Password]'' at line 1

My table is is "password", not "pw" but changing it from pw to password does not work either.

 

[FreakyJ]

your biggest problem is that you don't read the error messages
Warning: mysql_real_escape_string() expects parameter 1 to be string
Says it all^^ How do you call the script?
I guess without "xyz.php?available=true" ?!
You could just add an if checking for this case:

if(!isset($_GET["available"])) {
exit("Var 'available' not set!");
}

 

[The_Stig]

I did use the parameters, but unfortunately with [ and ]...works now with writing in the db. But weirdly don't deactivate the camera. Maybe I got to use the above posted string? Do you see major differences between the two scripts?

 

[FreakyJ]

put some echo "xyz"; in the code and try to trace it down and use var_dump() to see whats in the vars^^
In you db you set available as int, but it should be boolean. Don't know if it makes a difference but could be hint

 

[The_Stig]

In you db you set available as int, but it should be boolean. Don't know if it makes a difference but could be hint

Currently I think its not a DB-Problem, but an API-Problem. Even when I use only the snipplet to activate / deactivate the cam, it doesn't work.

This is my current full php where activating/deactivating is not working:

<?php
$httpformat="http"; //hier angeben ob http oder https
$port="5000"; //port
$loginname="administrator"; //Loginname an der Surveillance Station
$passwd="Password"; //passwort an der Surveillance Station
$ip="192.168.178.99"; //ip adresse der Surveillance station im LAN
$cam_id="2"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
// db
$db_name = "root";
$db_pw = "Password";
$db_database = "Surveillance";
$db_table = "user";
$db_ip = "127.0.0.1";

// activate Surveillance
$surveillance = true;
// connect to db
$connection = mysql_connect($db_ip, $db_name,$db_pw) or die ("No connection. User or password is wrong");
// select table
mysql_select_db($db_database) or die ("The Database ". $db_database ." doesn't exist");

// update table
$query = "UPDATE ".$db_table." Set available = ".mysql_real_escape_string($_GET['available']).", time = NOW() WHERE id = ".mysql_real_escape_string($_GET['id'])." AND password='".mysql_real_escape_string($_GET['pw'])."'";
$update = mysql_query($query);

if (!$update) {
    die('Error: ' . mysql_error());
}

// select info

$query = "SELECT available FROM ".$db_table;
$result = mysql_query($query);
while($row = mysql_fetch_object($result)) {
   if ($row->available == true)
    $surveillance = false;
}
//Login an der Surveillance Station:
$json = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&method=Login&version=3& account=".$loginname."&passwd=".$passwd."&session= SurveillanceStation&format=sid'");
$obj = json_decode($json, true);
$sid = $obj["data"]["sid"];
if ($surveillance) {
//Kamera aktivieren:
$json = file_get_contents($httpformat."://".$ip.":".$port."/webapi/_______________________________________________________entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=".$cam_id."&_sid=".$sid);
}else {
//Kamera deaktivieren:
}

//Logout Surveillance Station:
$out = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&version=3&method=Logout &session=SurveillanceStation");

?>

And this is a script, where activating/deactivating is working:

<?php
//Authentication
$json = file_get_contents('http://192.168.178.99:5000/webapi/auth.cgi?api=SYNO.API.Auth&method=Login&version=3&account=administrator&passwd=Password&session=SurveillanceStation&format=sid');
$obj = json_decode($json, true);
$sid = $obj["data"]["sid"];

//GetList
$json = file_get_contents('http://192.168.178.99:5000/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=List&version=3&_sid='.$sid);
$obj = json_decode($json, true);
$cam_activated = $obj["data"]["cameras"][0]["enabled"];

if($cam_activated == false){
    //Activate cam
    $json = file_get_contents('http://192.168.178.99:5000/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=2&_sid='.$sid);
    echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/on_icon.png\" style=\"width:auto;height:auto;\" /></div>";
} 
else
{
    //Activate cam
    $json = file_get_contents('http://192.168.178.99:5000/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=2&_sid='.$sid);
    echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/off_icon.png\" /></div>";
}
?>

put some echo "xyz"; in the code and try to trace it down and use var_dump() to see whats in the vars^^

Don't know how to do this. Do you see a possibility to combine the first db-script with the second working activating script?

 

[FreakyJ]

Try this, again untested at all

<?php
$httpformat="http"; //hier angeben ob http oder https
$port="5000"; //port
$loginname="administrator"; //Loginname an der Surveillance Station
$passwd="Password"; //passwort an der Surveillance Station
$ip="192.168.178.99"; //ip adresse der Surveillance station im LAN
$cam_id="2"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
// db
$db_name = "root";
$db_pw = "Password";
$db_database = "Surveillance";
$db_table = "user";
$db_ip = "127.0.0.1";
// activate Surveillance
$surveillance = true;
// connect to db
$connection = mysql_connect($db_ip, $db_name,$db_pw) or die ("No connection. User or password is wrong");
// select table
mysql_select_db($db_database) or die ("The Database ". $db_database ." doesn't exist");
// update table
$query = "UPDATE ".$db_table." Set available = ".mysql_real_escape_string($_GET['available']).", time = NOW() WHERE id = ".mysql_real_escape_string($_GET['id'])." AND password='".mysql_real_escape_string($_GET['pw'])."'";
$update = mysql_query($query);
if (!$update) {
    die('Error: ' . mysql_error());
}
// select info
$query = "SELECT available FROM ".$db_table;
$result = mysql_query($query);
while($row = mysql_fetch_object($result)) {
   if ($row->available == true)
    $surveillance = false;
}
//Authentication
$json = file_get_contents('http://192.168.178.99:5000/webapi/auth.cgi?api=SYNO.API.Auth&method=Login&version=3&account=administrator&passwd=Password&session=SurveillanceStation&format=sid');
$obj = json_decode($json, true);
$sid = $obj["data"]["sid"];

//GetList
$json = file_get_contents('http://192.168.178.99:5000/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=List&version=3&_sid='.$sid);
$obj = json_decode($json, true);
$cam_activated = $obj["data"]["cameras"][0]["enabled"];

if ($surveillance && !$cam_activated) {
  //Activate cam
  $json = file_get_contents('http://192.168.178.99:5000/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=2&_sid='.$sid);
  echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/on_icon.png\" style=\"width:auto;height:auto;\" /></div>";
}else if(!$surveillance && $cam_activated) {
  //Deactivate cam
  $json = file_get_contents('http://192.168.178.99:5000/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=2&_sid='.$sid);
  echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/off_icon.png\" /></div>";
}
?>

 

[The_Stig]

It works!!!! You're the man!!! Thanks a lot @FreakyJ. There was just a tiny error in the script where //Deactivate cam was enabling too. But now it works like a charm. Thanks, thanks, thanks.

And - as you know - I never get enough: do you have an idea how to always enable the cam between 0 an 6 am (no matter if users are available)?