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

[The_Stig]

Hey guys,

as this is the most active forum I know I am asking for assistance in a little project I am starting currently:

I bought a Synology 414j NAS lately and a surveillance cam. What I want to achieve is, that alerts of the camera are activated in case neiter my wife nor me are in our house and deactivated when we are at home. This should be done via geofency and our iPhones when leaving/entering the house. This is the plan

What I have done so far:
- Webserver activated on NAS
- phpmyadmin installed
- php-scripts for "activate" and "deactivate"
- Testscript with phpinfo is working fine

Where am I struggling:
- I have absolutely no clue how to set up the database to check, if both iPhones are away from the house.
- I later want to activate the alarm in the night time, even if one or both iPhones are at home.

Anyone with a pointer for me here?

Stiggy

 

[FreakyJ]

you need to be a bit more specific

So you have a app on your phone and it calls the php script depending on where you are?!

So phpmyadmin is no database, it is a management tool for the database. You need to have MySQL running, than you install phpmyadmin and set it up. After that you can log in php my admin and browse through all the databases and table son your mysql server.
There you can also view the data in the tables and therefore check the values.

Hope this is helpful

 

[FreakyJ]

Here si a demo how phpmyadmin looks like:
http://demo.phpmyadmin.net/master-c...ode_ci&token=21d8aa0b2590e6a94ad6f94aca279549

 

[The_Stig]

Thanks for your answer!

So you have a app on your phone and it calls the php script depending on where you are?!

Yes, its called Geofancy

So phpmyadmin is no database, it is a management tool for the database.

I know, maybe I formulated it misleading above.

You need to have MySQL running, than you install phpmyadmin and set it up. After that you can log in php my admin and browse through all the databases

Already up and running.

There you can also view the data in the tables and therefore check the values.

I know that.

What I do not know is what would be the correct way for my above described idea? I am trying to describe as detailed as I can (some reading here too in German language: http://www.synology-forum.de/showth...kennung-von-unterwegs-aktivieren-deaktivieren (especially the posts of createch)

- User1 away -> write to DB (triggered via Geofancy app in iPhone; but how to write this to db???)
- User2 away -> write to DB (triggered via Geofancy app in iPhone; but how to write this to db???)
- if User1 and User2 away -> activate Camera (triggered via ???; php script for activating already working)
- if User1 or User2 home -> deactivate Camera (triggered via ???; php script for deactivating already working)
- overrule above and activate Camera if time between 0 and 6 am

 

[FreakyJ]

Ahh, I just read the first page and it only describes teh communication with the Nas, but not how to track these things
http://www.php-einfach.de/einf_mysql.php
Here you can learn how to make a mysql connection with php.

Basically you just need a user table:
id, name, pw, available, time

from you phone, you call a simple URL: something.php?id=[user_id]&pw=[pw]&available[true/false]
The pw is not really secure, it is just a not known secret to make hacking a bit harder...

You use the first two vars to get the right user and the last one to set the status in the db.
After that you check if both users are not available and if this is true switch the cam on

If you like you can send me your php script via pn or post it here and I will take a look on it
Id did a lot with php as I was younger, so I should be able to help you^^

 

[The_Stig]

id, name, pw, available, time

Done.

from you phone, you call a simple URL: something.php?id=[user_id]&pw=[pw]&available[true/false]

What will be the content of the something.php? Do I have to connect to the database first?

You use the first two vars to get the right user and the last one to set the status in the db.

Understood.

After that you check if both users are not available and if this is true switch the cam on

How? ;-)

If you like you can send me your php script via pn or post it here and I will take a look on it

I currently only have the scripts to activate and deactivate. This is my activate.php (the deactivate.php is nearly identical):

<?php
$httpformat="http"; //hier angeben ob http oder https
$port="5000"; //port
$loginname="administrator"; //Loginname an der Surveillance Station
$passwd="Passwort"; //passwort an der Surveillance Station
$ip="192.168.178.99"; //ip adresse der Surveillance station im LAN
$cam_id="1"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren

//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"];

//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);


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

 

[FreakyJ]

I hacked something together^^ It is totally untested and written in notebpad, so expect some errors

<?php
$httpformat="http"; //hier angeben ob http oder https
$port="5000"; //port
$loginname="administrator"; //Loginname an der Surveillance Station
$passwd="Passwort"; //passwort an der Surveillance Station
$ip="192.168.178.99"; //ip adresse der Surveillance station im LAN
$cam_id="1"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
// db
$db_user = "USER";
$db_pw = "pw";
$db_database = "database";
$db_table = "table";
$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 {
    //Logout Surveillance Station:
    $out = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&version=3&method=Logout &session=SurveillanceStation");
}
?>

 

[FreakyJ]

Ups here is mistake:

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 {
//Logout Surveillance Station:
$out = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&version=3&method=Logout &session=SurveillanceStation");
}

It must be something like

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");

 

[The_Stig]

Danke dir!!!

Habe das gerade mal versucht gedanklich nachzuvollziehen. Wenn ich es richtig verstehe, macht dieses PHP das folgende:

- Verbindung zur DB
- setzen des Werts in der DB
- Verbindung zum NAS
- Aktivierung Kamera
- Ausloggen vom NAS

Korrekt? Wenn ja, bin ich schonmal einen Schritt weiter, aber wie bekomme ich es hin, dass es die Kamera nur aktiviert, wenn beide Nutzer nicht daheim sind?

EDIT: Oh sorry, switched to german. If anyone is interested in translation, I will edit it of course...

 

[FreakyJ]

Korrekt? Wenn ja, bin ich schonmal einen Schritt weiter, aber wie bekomme ich es hin, dass es die Kamera nur aktiviert, wenn beide Nutzer nicht daheim sind?

Du hast die kleine if übersehen

if ($surveillance) {
//Kamera aktivieren:

Und schau noch hier:
while($row = mysql_fetch_object($result)) {
if ($row->available == true)
$surveillance = false;
}

Sobald ein user in der Gegend des Hauses ist, ist der Wert "available" in der DB true. Folglich wird die Variable $surveillance auf false gesetzt und die überwachung in der if deaktiviert
Das ist die eine Zeile, die du noch einfügen musst