1

I've this PHP code on my Ubuntu 15.10 virtual machine

<?php    
  $lon = 4.221542;
  $lat = 32.21115;

  $db_data_sessions = new SQLite3('./DataBase/MyDB.sqlite');

  # Loading SpatiaLite as an extension ...
  $db_data_sessions->loadExtension('mod_spatialite.so');

  $q="SELECT comune FROM comuni32632 WHERE 
      WITHIN(Transform(GeomFromText('POINT(".$lon." 
      ".$lat.")',4326),32632),comuni32632.Geometry)";

      echo "Query = " .$q;
      echo "\n\n";

      try {
        $stmt = $db_data_sessions->prepare($q);
        $results = $stmt->execute();

        if ($results->fetchColumn() > 0)
         {
           echo "OK";
           echo "\n\n";

           $theName = '';
           while($row=$results->fetchArray(SQLITE3_ASSOC))
             {
              $theName = $row['comune'];
             }
           //return $theName;
           echo "Result = " .$theName;
           echo "\n\n"; 
         }
         else {
            //return "NO DATA";
            echo "Result = NO DATA";
            echo "\n\n";
         }
     }
     catch(PDOException $e) {
        print "Something went wrong or Connection to database failed! ".$e->getMessage();
        //return "ERROR";
        echo "Result = ERROR";
        echo "\n\n";   
   }
   $db_data_sessions = null;    
?>

and when I try to execute I can see only the first echo output, so my current query text and not more. In the browser console I can see "Error 500"

When I try to execute from command line

php test.php

the result is

PHP Warning:  SQLite3::loadExtension(): SQLite Extension are disabled in /var/www/html/test.php on line 10
Query = SELECT comune FROM comuni32632 WHERE WITHIN(Transform(GeomFromText('POINT(4.221542 32.21115)',4326),32632),comuni32632.Geometry)

PHP Warning:  SQLite3::prepare(): Unable to prepare statement: 1, no such function: GeomFromText in /var/www/html/test.php on line 19
PHP Fatal error:  Call to a member function execute() on boolean in /var/www/html/test.php on line 20

Note that if I try to execute the query in Sqlite3 loading the Spatialite extension all works fine

Note that if I try to execute the PHP test code available here http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/php.html all works fine so I think that PHP and Spatialite are working fine together

What is going wrong in my code?

  • Feels like sqlite does not allow to load extensions. – user30184 Mar 25 '17 at 10:56
0

I've solved changing my code in this way ....

<?php

  $lon = 11.611378;
  $lat = 45.730675;

  $db_data_sessions = new SQLite3('./DataBase/OpenProntoSoccorso.sqlite');

  # Loading SpatiaLite as an extension ...
  $db_data_sessions->loadExtension('mod_spatialite.so');

  $q="SELECT comune FROM comuni32632 WHERE WITHIN(Transform(GeomFromText('POINT(".$lon." ".$lat.")',4326),32632),comuni32632.Geometry)";

  echo "Query = " .$q;
  echo "\n\n";

  try {
     $stmt = $db_data_sessions->prepare($q);
     $results = $stmt->execute();

     $count = 0;
     while ($row = $results->fetchArray(SQLITE3_ASSOC)) {
       $data .= $row['comune'];
       $count = $count + 1;
     }
     echo "Result = OK - Comune = ".$data." Count = ".$count;
     echo "\n\n";
  }
  catch(PDOException $e) {
        print "Something went wrong or Connection to database failed! ".$e->getMessage();
        //return "ERROR";
        echo "Result = ERROR";
        echo "\n\n";

  }
  $db_data_sessions = null;

?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.