X-Git-Url: https://dev.renevier.net/gitweb.cgi?p=syp.git;a=blobdiff_plain;f=inc%2Fdb%2Fmysql.php;h=043e86d7818b853cd84951f4ae1ec9352e8cd5cf;hp=e4c61d2c8e629a6fa2ccd6ddbbe639c07a1f2d1e;hb=3b38ca36fc18d34999073625a9c66dc2f05747a3;hpb=57511b4efd7402ef58de66ac1fe2c01ed1b1d7b5

diff --git a/inc/db/mysql.php b/inc/db/mysql.php
index e4c61d2..043e86d 100644
--- a/inc/db/mysql.php
+++ b/inc/db/mysql.php
@@ -28,9 +28,8 @@ class mysqlConnection implements anydbConnection {
     public function users_table_exists () {
         return $this->_tblexists ("users");
     }
-    public function create_users_table ($error_if_exists) {
+    public function create_users_table () {
         $query = sprintf ("CREATE TABLE " .
-                           ($error_if_exists ? " " : "IF NOT EXISTS ") .
                            "%susers (
                             name VARCHAR(255) NOT NULL, pwd CHAR(32),
                             PRIMARY KEY (name));", $this->dbprefix);
@@ -40,9 +39,8 @@ class mysqlConnection implements anydbConnection {
     public function items_table_exists () {
         return $this->_tblexists ("items");
     }
-    public function create_items_table ($error_if_exists) {
+    public function create_items_table () {
         $query = sprintf ("CREATE TABLE " .
-                           ($error_if_exists ? " " : "IF NOT EXISTS ") .
                             "%sitems (
                                 id MEDIUMINT NOT NULL AUTO_INCREMENT,
                                 location POINT,
@@ -109,20 +107,22 @@ class mysqlConnection implements anydbConnection {
               $query = sprintf ("INSERT INTO %sitems
                               (imgpath, title, description, location, date, user)
                                 VALUES ('%s', '%s', '%s', 
-                               GeomFromText('POINT(%s %s)'), NOW(), 'admin')", 
+                               GeomFromText('POINT(%s %s)'), NOW(), '%s')", 
                               $this->dbprefix,
                               mysql_real_escape_string ($feature->imgpath),
                               mysql_real_escape_string ($feature->title),
                               mysql_real_escape_string ($feature->description),
                               $feature->lon,
-                              $feature->lat
+                              $feature->lat,
+                              mysql_real_escape_string ($feature->user)
                     );
 
                 $this->_execute_query ($query);
                 $id = mysql_insert_id ();
                 return new feature ($id, $feature->lon, $feature->lat,
                                     $feature->imgpath, $feature->title,
-                                    $feature->description);
+                                    $feature->description, $feature->date,
+                                    $feature->user);
         }
     }
 
@@ -136,7 +136,8 @@ class mysqlConnection implements anydbConnection {
 
     public function getfeature ($id) {
         $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
-                           AS location FROM %sitems WHERE id = '%s';", 
+                           AS location, UNIX_TIMESTAMP(date) AS date, user
+                           FROM %sitems WHERE id = '%s';", 
                         $this->dbprefix, mysql_real_escape_string ($id));
         $row = mysql_fetch_assoc ($this->_execute_query ($query));
         if ($row === false) {
@@ -145,10 +146,18 @@ class mysqlConnection implements anydbConnection {
         return $this->_feature_frow_row ($row);
     }
 
-    public function listfeatures () {
+    public function listfeatures ($user) {
+        if ($user && ($user != "admin")) {
+            $from_user_query = sprintf (" WHERE user = '%s' ",
+                                        mysql_real_escape_string ($user));
+        } else {
+            $from_user_query = "";
+        }
+
         $query = sprintf ("SELECT id, imgpath, title, description, AsText(location)
-                            AS location FROM %sitems;",
-                          $this->dbprefix);
+                            AS location, UNIX_TIMESTAMP(date) AS date, user
+                            FROM %sitems %s;",
+                          $this->dbprefix, $from_user_query);
 
         $features = array ();
         $res = $this->_execute_query ($query);
@@ -161,6 +170,25 @@ class mysqlConnection implements anydbConnection {
         return $features;
     }
 
+    public function mostrecentfeatures ($num_features) {
+        $query = sprintf ("SELECT id, imgpath, title, description,
+                           AsText(location) AS location, UNIX_TIMESTAMP(date)
+                           AS date, user FROM %sitems ORDER BY date DESC",
+                           $this->dbprefix);
+        if ($num_features) {
+            $query .= sprintf (" LIMIT %d", $num_features);
+        }
+        $features = array ();
+        $res = $this->_execute_query ($query);
+        while ($row = mysql_fetch_assoc ($res)) {
+            $feature = $this->_feature_frow_row ($row);
+            if (isset ($feature)) {
+                $features[] = $feature;
+            }
+        }
+        return $features;
+    }
+
     public function imgpath_exists ($imgpath) {
         $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgpath LIKE '%s';",
                            $this->dbprefix, mysql_real_escape_string ($imgpath));
@@ -168,46 +196,6 @@ class mysqlConnection implements anydbConnection {
         return ($res [0] >= 1) ? true : false;
     }
 
-    public function mbr () {
-        $maxlon = -180;
-        $minlon = 180;
-        $maxlat = -88;
-        $minlat = 88;
-
-        if (!$this->link) {
-            return array ($minlon, $minlat, $maxlon, $maxlat);
-        }
-        try {
-            $features = $this->listfeatures ();
-        } catch (Exception $e) {
-            return array ($minlon, $minlat, $maxlon, $maxlat);
-        }
-
-        if (count ($features) == 0) {
-            return array ($minlon, $minlat, $maxlon, $maxlat);
-        } else if (count ($features) == 1) { 
-            // in case there's only one feature, we show an area of at least 
-            // 4 x 4 degrees
-            $feature = $features [0];
-
-            $minlon = max ($feature->lon - 2, -180);
-            $maxlon = min ($feature->lon + 2, 180);
-            $minlat = max ($feature->lat - 2, -90);
-            $maxlat = min ($feature->lat + 2, 90);
-
-            return array ($minlon, $minlat, $maxlon, $maxlat);
-        } else {
-            foreach ($features as $feature) {
-                $minlon = min ($feature->lon, $minlon);
-                $minlat = min ($feature->lat, $minlat);
-                $maxlon = max ($feature->lon, $maxlon);
-                $maxlat = max ($feature->lat, $maxlat);
-            }
-
-            return array ($minlon, $minlat, $maxlon, $maxlat);
-        }
-    }
-
     public function getdbname () {
         return "Mysql";
     }
@@ -228,7 +216,8 @@ class mysqlConnection implements anydbConnection {
         $lat = $matches [2];
         try {
             $feature = new feature ($row ["id"], $lon, $lat, $row ["imgpath"],
-                                    $row ["title"], $row ["description"]);
+                                    $row ["title"], $row ["description"],
+                                    $row ["date"], $row ["user"]);
         } catch (Exception $e) {
             return null;
         }