X-Git-Url: https://dev.renevier.net/gitweb.cgi?p=syp.git;a=blobdiff_plain;f=inc%2Fdb%2Fmysql.php;h=8e3fcf85394d618b97c7d1bbd4e68e8ee7abf859;hp=1e20c34e526fde9ff2a18134c35bfec519bb75a0;hb=e4a4e5149d55cf01a86f26392c52fdca803654ce;hpb=7282fabcfef34ef95b8c6bd414f34d77037451e1 diff --git a/inc/db/mysql.php b/inc/db/mysql.php index 1e20c34..8e3fcf8 100644 --- a/inc/db/mysql.php +++ b/inc/db/mysql.php @@ -2,7 +2,7 @@ /* Copyright (c) 2009 Arnaud Renevier, Inc, published under the modified BSD license. */ -require ("./inc/db/anydb.php"); +require_once ("./inc/db/anydb.php"); class mysqlConnection implements anydbConnection { var $link = null; @@ -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,16 +39,17 @@ 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 ( - imgurl VARCHAR(255), + id MEDIUMINT NOT NULL AUTO_INCREMENT, + location POINT, title VARCHAR(127), description TEXT, - location POINT, + imgpath VARCHAR(255), date DATETIME, - PRIMARY KEY (imgurl) + user VARCHAR(255), + PRIMARY KEY (id) );", $this->dbprefix); $this->_execute_query ($query); } @@ -84,66 +84,69 @@ class mysqlConnection implements anydbConnection { } public function save_feature ($feature) { - $query = sprintf ("SELECT imgurl FROM %sitems WHERE imgurl = '%s'", - $this->dbprefix, - mysql_real_escape_string ($feature->imgurl)); - $this->_execute_query ($query); - if (mysql_affected_rows ($this->link) == 0) { - if ($feature->imgurl_exists ()) { + try { + $id = $feature->id; + } catch (Exception $e) {} + if (isset ($id)) { + $query = sprintf ("UPDATE %sitems SET + imgpath='%s', + title='%s', + description='%s', + location=GeomFromText('POINT(%s %s)') + WHERE id = '%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, + $id); + $this->_execute_query ($query); + return $feature; + } else { $query = sprintf ("INSERT INTO %sitems - (imgurl, title, description, location, date) - VALUES ('%s', '%s', '%s', - GeomFromText('POINT(%s %s)'), NOW())", + (imgpath, title, description, location, date, user) + VALUES ('%s', '%s', '%s', + GeomFromText('POINT(%s %s)'), NOW(), 'admin')", $this->dbprefix, - mysql_real_escape_string ($feature->imgurl), + mysql_real_escape_string ($feature->imgpath), mysql_real_escape_string ($feature->title), mysql_real_escape_string ($feature->description), $feature->lon, $feature->lat ); + $this->_execute_query ($query); - return true; - } else { - return false; - } - } else { - $query = sprintf ("UPDATE %sitems SET title='%s', description='%s', - location=GeomFromText('POINT(%s %s)') - where imgurl = '%s'", - $this->dbprefix, - mysql_real_escape_string ($feature->title), - mysql_real_escape_string ($feature->description), - $feature->lon, - $feature->lat, - mysql_real_escape_string ($feature->imgurl) - ); - $this->_execute_query ($query); - return true; + $id = mysql_insert_id (); + return new feature ($id, $feature->lon, $feature->lat, + $feature->imgpath, $feature->title, + $feature->description, $feature->date); } } public function delete_feature ($feature) { - $query = sprintf ("DELETE from %sitems WHERE imgurl like '%s'", + $query = sprintf ("DELETE from %sitems WHERE id = '%s'", $this->dbprefix, - mysql_real_escape_string ($feature->imgurl)); + mysql_real_escape_string ($feature->id)); $this->_execute_query ($query); return true; } - public function getfeature ($imgurl) { - $query = sprintf ("SELECT imgurl, title, description, AsText(location) - AS location FROM %sitems WHERE imgurl like '%s';", - $this->dbprefix, mysql_real_escape_string ($imgurl)); + public function getfeature ($id) { + $query = sprintf ("SELECT id, imgpath, title, description, AsText(location) + AS location, UNIX_TIMESTAMP(date) AS date FROM %sitems + WHERE id = '%s';", + $this->dbprefix, mysql_real_escape_string ($id)); $row = mysql_fetch_assoc ($this->_execute_query ($query)); - if (!isset ($row)) { + if ($row === false) { return null; } return $this->_feature_frow_row ($row); } public function listfeatures () { - $query = sprintf ("SELECT imgurl, title, description, AsText(location) - AS location FROM %sitems;", + $query = sprintf ("SELECT id, imgpath, title, description, AsText(location) + AS location, UNIX_TIMESTAMP(date) AS date FROM %sitems;", $this->dbprefix); $features = array (); @@ -157,9 +160,28 @@ class mysqlConnection implements anydbConnection { return $features; } - public function imgurl_exists ($imgurl) { - $query = sprintf ("SELECT COUNT(*) FROM %sitems WHERE imgurl LIKE '%s';", - $this->dbprefix, mysql_real_escape_string ($imgurl)); + public function mostrecentfeatures ($num_features) { + $query = sprintf ("SELECT id, imgpath, title, description, + AsText(location) AS location, UNIX_TIMESTAMP(date) + AS date 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)); $res = mysql_fetch_array ($this->_execute_query ($query), MYSQL_NUM); return ($res [0] >= 1) ? true : false; } @@ -223,8 +245,9 @@ class mysqlConnection implements anydbConnection { $lon = $matches [1]; $lat = $matches [2]; try { - $feature = new feature ($row ["imgurl"], $row ["title"], $row - ["description"], $lon, $lat); + $feature = new feature ($row ["id"], $lon, $lat, $row ["imgpath"], + $row ["title"], $row ["description"], + $row ["date"]); } catch (Exception $e) { return null; }