function listposts(){
global $db;
$locality=GETSTR('locality');
if(!$locality){echo json_encode(array("success"=>"false"));}
$query="select * from artworks where locality='$locality' order by likes desc";
$rs=sql_query($query, $db);
if(!$rs){echo json_encode(array("success:false"));die();}
$data=array();
while($myrow=sql_fetch_array($rs)){
array_push($data, array(
"artworkid"=>$myrow['artworkid']+0,
"createdate"=>$myrow['createdate']+0,
"userid"=>$myrow['userid'],
"price"=>$myrow['price']+0,
"likes"=>$myrow['likes']+0,
"geolat"=>$myrow['geolat']+0,
"geolong"=>$myrow['geolong']+0,
"image"=>$myrow['image'],
"onsale"=>$myrow['onsale']+0,
"title"=>$myrow['title'],
"locality"=>$myrow['locality']
));
}
$stats=array("itemcount"=>sql_affected_rows($db,$rs));
$response=array("data"=>$data, "stats"=>$stats);
echo json_encode($response);
}
/**
* Removes a ban from the banlist (correct iprange is needed as argument)
* Returns 1 on success, 0 on error
*/
function removeBan($blogid, $iprange)
{
global $manager;
$blogid = intval($blogid);
$manager->notify('PreDeleteBan', array('blogid' => $blogid, 'range' => $iprange));
$query = 'DELETE FROM ' . sql_table('ban') . " WHERE blogid={$blogid} and iprange='" . sql_real_escape_string($iprange) . "'";
sql_query($query);
$result = sql_affected_rows() > 0;
$manager->notify('PostDeleteBan', array('blogid' => $blogid, 'range' => $iprange));
return $result;
}
function mysql_session_write($SessionID, $val)
{
# dbg("writing session info for $SessionID");
$SessionTableName = $GLOBALS["SessionTableName"];
$SessionID = addslashes($SessionID);
$val = addslashes($val);
$SessionExists = sql_fetch_row_query("select count(*) from {$SessionTableName} where sessionid = '{$SessionID}'");
if ($SessionExists[0] == 0) {
$retval = sql_query(sprintf('insert into %s (sessionid,lastactive,data) values("%s",UNIX_TIMESTAMP(NOW()),"%s")', $SessionTableName, $SessionID, $val));
} else {
$retval = sql_query(sprintf('update %s SET data = "%s", lastactive = UNIX_TIMESTAMP(NOW()) where sessionid = "%s"', $SessionTableName, $val, $SessionID));
if (sql_affected_rows() < 0) {
sendError("unable to update session data for session {$SessionID}");
}
}
return $retval;
}
sql_query("delete from resource_keyword where resource not in (select ref from resource)");
echo sql_affected_rows() . " orphaned resource-keyword relationships deleted.<br/><br/>";
sql_query("delete from keyword where ref not in (select keyword from resource_keyword) and ref not in (select keyword from keyword_related) and ref not in (select related from keyword_related) and ref not in (select keyword from collection_keyword)");
echo sql_affected_rows() . " unused keywords deleted.<br/><br/>";
sql_query("delete from resource_alt_files where resource not in (select ref from resource)");
echo sql_affected_rows() . " orphaned alternative files deleted.<br/><br/>";
sql_query("delete from resource_custom_access where resource not in (select ref from resource) or (user not in (select ref from user) and usergroup not in (select ref from usergroup))");
echo sql_affected_rows() . " orphaned resource custom access rows deleted.<br/><br/>";
sql_query("delete from resource_dimensions where resource not in (select ref from resource)");
echo sql_affected_rows() . " orphaned resource dimension rows deleted.<br/><br/>";
sql_query("delete from resource_log where resource<>0 and resource not in (select ref from resource)");
echo sql_affected_rows() . " orphaned resource log rows deleted.<br/><br/>";
sql_query("delete from resource_related where resource not in (select ref from resource) or related not in (select ref from resource)");
echo sql_affected_rows() . " orphaned resource related rows deleted.<br/><br/>";
sql_query("delete from resource_type_field where resource_type<>999 and resource_type<>0 and resource_type not in (select ref from resource_type)");
echo sql_affected_rows() . " orphaned fields deleted.<br/><br/>";
sql_query("delete from user_collection where user not in (select ref from user) or collection not in (select ref from collection)");
echo sql_affected_rows() . " orphaned user-collection relationships deleted.<br/><br/>";
sql_query("delete from resource_data where resource not in (select ref from resource) or resource_type_field not in (select ref from resource_type_field)");
echo sql_affected_rows() . " orphaned resource data rows deleted.<br/><br/>";
# Clean out and resource data that is set for fields not applicable to a given resource type.
$r = get_resource_types();
for ($n = 0; $n < count($r); $n++) {
$rt = $r[$n]["ref"];
$fields = sql_array("select ref value from resource_type_field where resource_type=0 or resource_type=999 or resource_type='" . $rt . "'");
if (count($fields) > 0) {
sql_query("delete from resource_data where resource in (select ref from resource where resource_type='{$rt}') and resource_type_field not in (" . join(",", $fields) . ")");
echo sql_affected_rows() . " orphaned resource data rows deleted for resource type {$rt}.<br/><br/>";
}
}
hook("dbprune");
sql_query("DELETE FROM resource_keyword WHERE resource NOT IN (SELECT ref FROM resource)");
echo number_format(sql_affected_rows()) . " orphaned resource-keyword relationships deleted." . $newline;
sql_query("DELETE FROM keyword WHERE ref NOT IN (SELECT keyword FROM resource_keyword) AND ref NOT IN (SELECT keyword FROM keyword_related) AND ref NOT IN (SELECT related FROM keyword_related) AND ref NOT IN (SELECT keyword FROM collection_keyword)");
echo number_format(sql_affected_rows()) . " unused keywords deleted." . $newline;
sql_query("DELETE FROM resource_alt_files WHERE resource NOT IN (SELECT ref FROM resource)");
echo number_format(sql_affected_rows()) . " orphaned alternative files deleted." . $newline;
sql_query("DELETE FROM resource_custom_access WHERE resource NOT IN (SELECT ref FROM resource) OR (user NOT IN (SELECT ref FROM user) AND usergroup NOT IN (SELECT ref FROM usergroup))");
echo number_format(sql_affected_rows()) . " orphaned resource custom access rows deleted." . $newline;
sql_query("DELETE FROM resource_dimensions WHERE resource NOT IN (SELECT ref FROM resource)");
echo number_format(sql_affected_rows()) . " orphaned resource dimension rows deleted." . $newline;
sql_query("DELETE FROM resource_log WHERE resource<>0 AND resource NOT IN (SELECT ref FROM resource)");
echo number_format(sql_affected_rows()) . " orphaned resource log rows deleted." . $newline;
sql_query("DELETE FROM resource_related WHERE resource NOT IN (SELECT ref FROM resource) OR related NOT IN (SELECT ref FROM resource)");
echo number_format(sql_affected_rows()) . " orphaned resource related rows deleted." . $newline;
sql_query("DELETE FROM resource_type_field WHERE resource_type<>999 AND resource_type<>0 AND resource_type NOT IN (SELECT ref FROM resource_type)");
echo number_format(sql_affected_rows()) . " orphaned fields deleted." . $newline;
sql_query("DELETE FROM user_collection WHERE user NOT IN (SELECT ref FROM user) OR collection NOT IN (SELECT ref FROM collection)");
echo number_format(sql_affected_rows()) . " orphaned user-collection relationships deleted." . $newline;
sql_query("DELETE FROM resource_data WHERE resource NOT IN (SELECT ref FROM resource) OR resource_type_field NOT IN (SELECT ref FROM resource_type_field)");
echo number_format(sql_affected_rows()) . " orphaned resource data rows deleted." . $newline;
# Clean out and resource data that is set for fields not applicable to a given resource type.
$r = get_resource_types();
for ($n = 0; $n < count($r); $n++) {
$rt = $r[$n]["ref"];
$fields = sql_array("SELECT ref value FROM resource_type_field WHERE resource_type=0 OR resource_type=999 OR resource_type='" . $rt . "'");
if (count($fields) > 0) {
sql_query("DELETE FROM resource_data WHERE resource in (SELECT ref FROM resource WHERE resource_type='{$rt}') AND resource_type_field NOT IN (" . join(",", $fields) . ")");
echo number_format(sql_affected_rows()) . " orphaned resource data rows deleted for resource type {$rt}." . $newline;
}
}
hook("dbprune");
* For license information see doc/license.txt
*
* Unicode Reminder メモ
***************************************************************************/
require './lib2/web.inc.php';
$tpl->name = 'mytop5';
$tpl->menuitem = MNU_MYPROFILE_RECOMMENDATIONS;
$login->verify();
if ($login->userid == 0) {
$tpl->redirect('login.php?target=mytop5.php');
}
$action = isset($_REQUEST['action']) ? mb_strtolower($_REQUEST['action']) : '';
if ($action == 'delete') {
$cache_id = isset($_REQUEST['cacheid']) ? $_REQUEST['cacheid'] + 0 : 0;
sql("DELETE FROM `cache_rating` WHERE `cache_id`='&1' AND `user_id`='&2'", $cache_id, $login->userid);
if (sql_affected_rows() == 0) {
// will happen if
// 1. we delete a recommendation
// 2. we view another recommended cache
// 3. we return to mytop5 via browser "back" button
// -> ignore
} else {
$rs = sql("SELECT `caches`.`wp_oc` AS `wp`, `caches`.`name` AS `cachename` FROM `caches` WHERE `caches`.`cache_id`='&1'", $cache_id);
$deletedItem = sql_fetch_assoc($rs);
$tpl->assign('deleted', true);
$tpl->assign('deletedItem', $deletedItem);
sql_free_result($rs);
}
}
$rs = sql("SELECT `cache_rating`.`cache_id` AS `cacheid`, `cache_rating`.`rating_date`, `caches`.`wp_oc` AS `wp`, `caches`.`name` AS `cachename`, `caches`.`type` AS `type`, `caches`.`status` AS `status`, `ca`.`attrib_id` IS NOT NULL AS `oconly`, `stat_caches`.`toprating` AS `countrating`\n\t FROM `cache_rating`\n\t\t\t\t INNER JOIN `caches` ON `cache_rating`.`cache_id`=`caches`.`cache_id`\n\t\t\tLEFT JOIN `stat_caches` ON `stat_caches`.`cache_id`=`cache_rating`.`cache_id`\n\t LEFT JOIN `caches_attributes` `ca` ON `ca`.`cache_id`=`caches`.`cache_id` AND `ca`.`attrib_id`=6\n\t WHERE `cache_rating`.`user_id`='&1'\n\t ORDER BY `rating_date` DESC", $login->userid);
$tpl->assign_rs('ratings', $rs);
function HookAnnotateDatabase_pruneDbprune()
{
sql_query("delete from resource_keyword where annotation_ref > 0 and annotation_ref not in (select note_id from annotate_notes)");
echo sql_affected_rows() . " orphaned annotation resource-keyword relationships deleted.<br/><br/>";
}
function saveField($field)
{
if ($this->bLoaded == false || $this->bExist == false || $this->bAddNew == true) {
return false;
}
if ($this->fields[$field]['changed'] == false) {
return true;
}
if ($this->fields[$field]['value'] === null) {
$sSet = '`' . sql_escape($field) . '`=NULL';
} else {
$sSet = '`' . sql_escape($field) . '`=\'' . sql_escape($this->pFormatValueSql($this->fields[$field]['type'], $this->fields[$field]['value'])) . '\'';
}
$sql = 'UPDATE `' . sql_escape($this->sTable) . '` SET ' . $sSet;
$sql .= ' WHERE ';
$sql .= $this->pBuildPK();
sql($sql);
if (sql_affected_rows() == 0) {
return false;
}
$this->fields[$field]['changed'] = false;
return true;
}
static function visitCounter($nVisitUserId, $sRemoteAddr, $nCacheId)
{
global $opt, $_SERVER;
// delete cache_visits older 1 day 60*60*24 = 86400
sql("DELETE FROM `cache_visits` WHERE `cache_id`='&1' AND `user_id_ip`!='0' AND NOW()-`last_modified`>86400", $nCacheId);
if ($nVisitUserId == 0) {
$se = explode(';', $opt['logic']['search_engines']);
$ua = isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : "";
foreach ($se as $s) {
if (strpos($ua, $s) !== FALSE) {
return;
}
}
// do not count search engine views
$sIdentifier = $sRemoteAddr;
} else {
$sIdentifier = $nVisitUserId;
}
// note the visit of this user
sql("INSERT INTO `cache_visits` (`cache_id`, `user_id_ip`, `count`) VALUES (&1, '&2', 1)\n\t\t\t\tON DUPLICATE KEY UPDATE `count`=`count`+1", $nCacheId, $sIdentifier);
// if the previous statement does an INSERT, it was the first visit for this user today
if (sql_affected_rows() == 1) {
if ($nVisitUserId != sql_value("SELECT `user_id` FROM `caches` WHERE `cache_id`='&1'", 0, $nCacheId)) {
// increment the counter for this cache
sql("INSERT INTO `cache_visits` (`cache_id`, `user_id_ip`, `count`) VALUES (&1, '0', 1)\n\t\t\t\t\t\tON DUPLICATE KEY UPDATE `count`=`count`+1", $nCacheId);
}
}
}
public function resendLogNotifications()
{
# delete notification protocol
sql("DELETE FROM `watches_notified`\n WHERE\n `object_type` = '&1'\n AND " . $this->getDateCondition('watches_notified', 'date_created'), OBJECT_CACHELOG);
# re-notify owners
sql("UPDATE `cache_logs`\n SET `owner_notified` = 0\n WHERE " . $this->getDateCondition('cache_logs', 'date_created'));
$owner_notifications = sql_affected_rows();
# Re-notify direct watchers. See also trigger cacheLogsAfterInsert.
sql("INSERT IGNORE INTO `watches_logqueue` (`log_id`, `user_id`)\n SELECT `cache_logs`.`id` `log_id`, `cache_watches`.`user_id`\n FROM `cache_logs`\n JOIN `caches` ON `caches`.`cache_id` = `cache_logs`.`cache_id`\n JOIN `cache_status` ON `cache_status`.`id` = `caches`.`status`\n JOIN `cache_watches` ON `cache_watches`.`cache_id` = `caches`.`cache_id`\n WHERE\n " . $this->getDateCondition('cache_logs', 'date_created') . "\n AND `cache_status`.`allow_user_view` = 1");
$watcher_notifications = sql_affected_rows();
# Re-notify list watchers. See also trigger cacheLogsAfterInsert.
sql("INSERT IGNORE INTO `watches_logqueue` (`log_id`, `user_id`)\n SELECT `cache_logs`.`id` `log_id`, `cache_list_watches`.`user_id`\n FROM `cache_logs`\n JOIN `caches` ON `caches`.`cache_id` = `cache_logs`.`cache_id`\n JOIN `cache_status` ON `cache_status`.`id` = `caches`.`status`\n JOIN `cache_list_items` ON `cache_list_items`.`cache_id` = `cache_logs`.`cache_id`\n JOIN `cache_list_watches` ON `cache_list_watches`.`cache_list_id` = `cache_list_items`.`cache_list_id`\n WHERE\n " . $this->getDateCondition('cache_logs', 'date_created') . "\n AND `cache_status`.`allow_user_view` = 1");
$watcher_notifications += sql_affected_rows();
self::message(0, $owner_notifications . ' owners will be notified');
self::message(0, $watcher_notifications . ' watchers will be notified');
}
function bb2_db_affected_rows($result)
{
return sql_affected_rows();
}
static function visitCounter($nVisitUserId, $sRemoteAddr, $nCacheId)
{
// delete cache_visits older 1 day 60*60*24 = 86400
sql("DELETE FROM `cache_visits` WHERE `cache_id`='&1' AND `user_id_ip`!='0' AND NOW()-`last_modified`>86400", $nCacheId);
if ($nVisitUserId == 0) {
$sIdentifier = $sRemoteAddr;
} else {
$sIdentifier = $nVisitUserId;
}
// note the visit of this user
sql("INSERT INTO `cache_visits` (`cache_id`, `user_id_ip`, `count`) VALUES (&1, '&2', 1)\r\n\t\t\t\tON DUPLICATE KEY UPDATE `count`=`count`+1", $nCacheId, $sIdentifier);
// if the previous statement does an INSERT, it was the first visit for this user
if (sql_affected_rows() == 1) {
if ($nVisitUserId != sql_value("SELECT `user_id` FROM `caches` WHERE `cache_id`='&1'", 0, $nCacheId)) {
// increment the counter for this cache
sql("INSERT INTO `cache_visits` (`cache_id`, `user_id_ip`, `count`) VALUES (&1, '0', 1)\r\n\t\t\t\t\t\tON DUPLICATE KEY UPDATE `count`=`count`+1", $nCacheId);
}
}
}
function update_records($table, $conditions, $newdata)
{
$newdata = array_escape($newdata);
$column_quote_func = db_params('column_quote_func');
array_walk($newdata, $column_quote_func);
if (count($newdata) > 0) {
$query = "UPDATE " . $table . " SET " . implode(", ", $newdata) . sql_where($conditions);
$result = sql_query_dbg($query);
return sql_affected_rows($result);
}
return FALSE;
}
