php/SQL-Help for Geofence-Project? | Page 2

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
    Yeah! Will try at the weekend. Thanks again


     
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    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 ;-)

    Code (Text):
    1.  
    2. <?php
    3. //Authentication
    4. $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');
    5. $obj = json_decode($json, true);
    6. $sid = $obj["data"]["sid"];
    7.  
    8. //GetList
    9. $json = file_get_contents('http://IP:PORT/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=List&version=3&_sid='.$sid);
    10. $obj = json_decode($json, true);
    11. $cam_activated = $obj["data"]["cameras"][0]["enabled"];
    12.  
    13. if($cam_activated == false){
    14.     //Deactivate cam
    15.     $json = file_get_contents('http://IP:PORT/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=2&_sid='.$sid);
    16.     echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/on_icon.png\" style=\"width:auto;height:auto;\" /></div>";
    17. }
    18. else
    19. {
    20.     //Deactivate cam
    21.     $json = file_get_contents('http://IP:PORT/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=2&_sid='.$sid);
    22.     echo "<div class=\"centered\"><img src=\"https://externeAdresse:Port/off_icon.png\" /></div>";
    23. }
    24. ?>
    25.  
    What I've done in php is this:
    upload_2015-6-14_16-51-1.png

    I guess some of the types are wrong?

    And I have this now as php script:
    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="Password"; //passwort an der Surveillance Station
    7. $ip="IP"; //ip adresse der Surveillance station im LAN
    8. $cam_id="2"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
    9. // db
    10. $db_name = "root";
    11. $db_pw = "Password";
    12. $db_database = "Surveillance";
    13. $db_table = "user";
    14. $db_ip = "127.0.0.1";
    15.  
    16. // activate Surveillance
    17. $surveillance = true;
    18. // connect to db
    19. $connection = mysql_connect(db_ip, $db_user,$db_pw) or die ("No connection. User or password is wrong");
    20. // select table
    21. mysql_select_db($db_database) or die ("The Database ". $db_database ." doesn't exist");
    22.  
    23. // update table
    24. $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'])."'";
    25. $update = mysql_query($query);
    26.  
    27. if (!$update) {
    28.     die('Error: ' . mysql_error());
    29. }
    30.  
    31. // select info
    32.  
    33. $query = "SELECT available FROM ".$db_table;
    34. $result = mysql_query($query);
    35. while($row = mysql_fetch_object($result)) {
    36.    if ($row->available == true)
    37.     $surveillance = false;
    38. }
    39. //Login an der Surveillance Station:
    40. $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'");
    41. $obj = json_decode($json, true);
    42. $sid = $obj["data"]["sid"];
    43. if ($surveillance) {
    44. //Kamera aktivieren:
    45. $json = file_get_contents($httpformat."://".$ip.":".$port."/webapi/_______________________________________________________entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=".$cam_id."&_sid=".$sid);
    46. }else {
    47. //Kamera deaktivieren:
    48. }
    49.  
    50. //Logout Surveillance Station:
    51. $out = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&version=3&method=Logout &session=SurveillanceStation");
    52.  
    53. ?>
    54.  
    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?
     
  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
    Haha easy error xD
    A $ was missing^^
     
  5. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    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.
     
  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
    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!");
    }
     
  7. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    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?
     
  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
    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 :)
     
    • Thank You! Thank You! x 1
  9. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    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:
    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="Password"; //passwort an der Surveillance Station
    7. $ip="192.168.178.99"; //ip adresse der Surveillance station im LAN
    8. $cam_id="2"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
    9. // db
    10. $db_name = "root";
    11. $db_pw = "Password";
    12. $db_database = "Surveillance";
    13. $db_table = "user";
    14. $db_ip = "127.0.0.1";
    15.  
    16. // activate Surveillance
    17. $surveillance = true;
    18. // connect to db
    19. $connection = mysql_connect($db_ip, $db_name,$db_pw) or die ("No connection. User or password is wrong");
    20. // select table
    21. mysql_select_db($db_database) or die ("The Database ". $db_database ." doesn't exist");
    22.  
    23. // update table
    24. $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'])."'";
    25. $update = mysql_query($query);
    26.  
    27. if (!$update) {
    28.     die('Error: ' . mysql_error());
    29. }
    30.  
    31. // select info
    32.  
    33. $query = "SELECT available FROM ".$db_table;
    34. $result = mysql_query($query);
    35. while($row = mysql_fetch_object($result)) {
    36.    if ($row->available == true)
    37.     $surveillance = false;
    38. }
    39. //Login an der Surveillance Station:
    40. $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'");
    41. $obj = json_decode($json, true);
    42. $sid = $obj["data"]["sid"];
    43. if ($surveillance) {
    44. //Kamera aktivieren:
    45. $json = file_get_contents($httpformat."://".$ip.":".$port."/webapi/_______________________________________________________entry.cgi?api=SYNO.SurveillanceStation.Camera&method=Enable&version=3&cameraIds=".$cam_id."&_sid=".$sid);
    46. }else {
    47. //Kamera deaktivieren:
    48. }
    49.  
    50. //Logout Surveillance Station:
    51. $out = file_get_contents($httpformat."://".$ip.":".$port."/webapi/auth.cgi?api=SYNO.API.Auth&version=3&method=Logout &session=SurveillanceStation");
    52.  
    53. ?>
    And this is a script, where activating/deactivating is working:

    Code (Text):
    1.  
    2. <?php
    3. //Authentication
    4. $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');
    5. $obj = json_decode($json, true);
    6. $sid = $obj["data"]["sid"];
    7.  
    8. //GetList
    9. $json = file_get_contents('http://192.168.178.99:5000/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=List&version=3&_sid='.$sid);
    10. $obj = json_decode($json, true);
    11. $cam_activated = $obj["data"]["cameras"][0]["enabled"];
    12.  
    13. if($cam_activated == false){
    14.     //Activate cam
    15.     $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);
    16.     echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/on_icon.png\" style=\"width:auto;height:auto;\" /></div>";
    17. }
    18. else
    19. {
    20.     //Activate cam
    21.     $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);
    22.     echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/off_icon.png\" /></div>";
    23. }
    24. ?>
    25.  
    Don't know how to do this. Do you see a possibility to combine the first db-script with the second working activating script?
     
  10. 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
    Try this, again untested at all

    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="Password"; //passwort an der Surveillance Station
    6. $ip="192.168.178.99"; //ip adresse der Surveillance station im LAN
    7. $cam_id="2"; // Kamera ID mit script "hole Kamera ID" abfragen, später automatisieren
    8. // db
    9. $db_name = "root";
    10. $db_pw = "Password";
    11. $db_database = "Surveillance";
    12. $db_table = "user";
    13. $db_ip = "127.0.0.1";
    14. // activate Surveillance
    15. $surveillance = true;
    16. // connect to db
    17. $connection = mysql_connect($db_ip, $db_name,$db_pw) or die ("No connection. User or password is wrong");
    18. // select table
    19. mysql_select_db($db_database) or die ("The Database ". $db_database ." doesn't exist");
    20. // update table
    21. $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'])."'";
    22. $update = mysql_query($query);
    23. if (!$update) {
    24.     die('Error: ' . mysql_error());
    25. }
    26. // select info
    27. $query = "SELECT available FROM ".$db_table;
    28. $result = mysql_query($query);
    29. while($row = mysql_fetch_object($result)) {
    30.    if ($row->available == true)
    31.     $surveillance = false;
    32. }
    33. //Authentication
    34. $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');
    35. $obj = json_decode($json, true);
    36. $sid = $obj["data"]["sid"];
    37.  
    38. //GetList
    39. $json = file_get_contents('http://192.168.178.99:5000/webapi/entry.cgi?api=SYNO.SurveillanceStation.Camera&method=List&version=3&_sid='.$sid);
    40. $obj = json_decode($json, true);
    41. $cam_activated = $obj["data"]["cameras"][0]["enabled"];
    42.  
    43. if ($surveillance && !$cam_activated) {
    44.   //Activate cam
    45.   $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);
    46.   echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/on_icon.png\" style=\"width:auto;height:auto;\" /></div>";
    47. }else if(!$surveillance && $cam_activated) {
    48.   //Deactivate cam
    49.   $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);
    50.   echo "<div class=\"centered\"><img src=\"http://192.168.178.99:5000/off_icon.png\" /></div>";
    51. }
    52. ?>
     
    • Thank You! Thank You! x 1
  11. The_Stig
    • Team MediaPortal

    The_Stig Retired Team Member

    Joined:
    April 5, 2005
    Messages:
    2,176
    Likes Received:
    288
    Ratings:
    +426 / 5
    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)?
     
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!