php/SQL-Help for Geofence-Project?

Discussion in 'OffTopic' started by The_Stig, June 11, 2015.

  1. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    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
     
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. FreakyJ
    • Team MediaPortal

    FreakyJ Development Group

    Joined:
    July 25, 2010
    Messages:
    4,021
    Likes Received:
    839
    Gender:
    Male
    Ratings:
    +1,424 / 1
    Home Country:
    Germany Germany
    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 ;)
     
    • Thank You! Thank You! x 1
  4. FreakyJ
    • Team MediaPortal

    FreakyJ Development Group

    Joined:
    July 25, 2010
    Messages:
    4,021
    Likes Received:
    839
    Gender:
    Male
    Ratings:
    +1,424 / 1
    Home Country:
    Germany Germany
    • Thank You! Thank You! x 1
  5. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    Thanks for your answer!
    Yes, its called Geofancy
    I know, maybe I formulated it misleading above.
    Already up and running.

    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
     
  6. FreakyJ
    • Team MediaPortal

    FreakyJ Development Group

    Joined:
    July 25, 2010
    Messages:
    4,021
    Likes Received:
    839
    Gender:
    Male
    Ratings:
    +1,424 / 1
    Home Country:
    Germany Germany
    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^^
     
    • Thank You! Thank You! x 1
  7. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    Done.
    What will be the content of the something.php? Do I have to connect to the database first?
    Understood.
    How? ;-)

    I currently only have the scripts to activate and deactivate. This is my activate.php (the deactivate.php is nearly identical):
    Code (Text):
    1.  
    2. <?php
    3. $httpformat="http"; //hier angeben ob http oder https
    4. $port="5000"; //port
    5. $loginname="administrator"; //Loginname an der Surveillance Station
    6. $passwd="Passwort"; //passwort an der Surveillance Station
    7. $ip="192.168.178.99"; //ip adresse der Surveillance station im LAN
    8. $cam_id="1"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
    9.  
    10. //Login an der Surveillance Station:
    11.  
    12. $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'");
    13. $obj = json_decode($json, true);
    14. $sid = $obj["data"]["sid"];
    15.  
    16. //Kamera aktivieren:
    17. $json = file_get_contents($httpformat."://".$ip.":".$port."/webapi/_______________________________________________________entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=".$cam_id."&_sid=".$sid);
    18.  
    19.  
    20. //Logout Surveillance Station:
    21. $out = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&version=3&method=Logout &session=SurveillanceStation");
    22. ?>
    23.  
     
  8. FreakyJ
    • Team MediaPortal

    FreakyJ Development Group

    Joined:
    July 25, 2010
    Messages:
    4,021
    Likes Received:
    839
    Gender:
    Male
    Ratings:
    +1,424 / 1
    Home Country:
    Germany Germany
    I hacked something together^^ It is totally untested and written in notebpad, so expect some errors ;)

    Code (Text):
    1. <?php
    2. $httpformat="http"; //hier angeben ob http oder https
    3. $port="5000"; //port
    4. $loginname="administrator"; //Loginname an der Surveillance Station
    5. $passwd="Passwort"; //passwort an der Surveillance Station
    6. $ip="192.168.178.99"; //ip adresse der Surveillance station im LAN
    7. $cam_id="1"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
    8. // db
    9. $db_user = "USER";
    10. $db_pw = "pw";
    11. $db_database = "database";
    12. $db_table = "table";
    13. $db_ip = 127.0.0.1;
    14.  
    15. // activate Surveillance
    16. $surveillance = true;
    17. // connect to db
    18. $connection = mysql_connect(db_ip, $db_user,$db_pw) or die ("No connection. User or password is wrong");
    19. // select table
    20. mysql_select_db($db_database) or die ("The Database ". $db_database ." doesn't exist");
    21.  
    22. // update table
    23. $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'])."'";
    24. $update = mysql_query($query);
    25.  
    26. if (!$update) {
    27.     die('Error: ' . mysql_error());
    28. }
    29.  
    30. // select info
    31.  
    32. $query = "SELECT available FROM ".$db_table;
    33. $result = mysql_query($query);
    34. while($row = mysql_fetch_object($result)) {
    35.    if ($row->available == true)
    36.     $surveillance = false;
    37. }
    38. //Login an der Surveillance Station:
    39. $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'");
    40. $obj = json_decode($json, true);
    41. $sid = $obj["data"]["sid"];
    42. if ($surveillance) {
    43.     //Kamera aktivieren:
    44.     $json = file_get_contents($httpformat."://".$ip.":".$port."/webapi/_______________________________________________________entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=".$cam_id."&_sid=".$sid);
    45. }else {
    46.     //Logout Surveillance Station:
    47.     $out = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&version=3&method=Logout &session=SurveillanceStation");
    48. }
    49. ?>
     
    • Thank You! Thank You! x 1
  9. FreakyJ
    • Team MediaPortal

    FreakyJ Development Group

    Joined:
    July 25, 2010
    Messages:
    4,021
    Likes Received:
    839
    Gender:
    Male
    Ratings:
    +1,424 / 1
    Home Country:
    Germany Germany
    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");
     
  10. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    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...
     
    Last edited: June 11, 2015
  11. FreakyJ
    • Team MediaPortal

    FreakyJ Development Group

    Joined:
    July 25, 2010
    Messages:
    4,021
    Likes Received:
    839
    Gender:
    Male
    Ratings:
    +1,424 / 1
    Home Country:
    Germany Germany
    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
     
Loading...

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!