function fof_db_get_items($user_id = 1, $feed = NULL, $what = "unread", $when = NULL, $start = NULL, $limit = NULL, $order = "desc", $search = NULL)
{
global $FOF_SUBSCRIPTION_TABLE, $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_TAG_TABLE;
$prefs = fof_prefs();
$offset = $prefs['tzoffset'];
if (!is_null($when) && $when != "") {
if ($when == "today") {
$whendate = fof_todays_date();
} else {
$whendate = $when;
}
$whendate = explode("/", $whendate);
$begin = gmmktime(0, 0, 0, $whendate[1], $whendate[2], $whendate[0]) - $offset * 60 * 60;
$end = $begin + 24 * 60 * 60;
}
if (is_numeric($start)) {
if (!is_numeric($limit)) {
$limit = $prefs["howmany"];
}
$limit_clause = " limit {$start}, {$limit} ";
}
$args = array();
$select = "SELECT i.* , f.* ";
$from = "FROM {$FOF_FEED_TABLE} f, {$FOF_ITEM_TABLE} i, {$FOF_SUBSCRIPTION_TABLE} s ";
$where = sprintf("WHERE s.user_id = %d AND s.feed_id = f.feed_id AND f.feed_id = i.feed_id ", $user_id);
if (!is_null($feed) && $feed != "") {
$where .= sprintf("AND f.feed_id = %d ", $feed);
}
if (!is_null($when) && $when != "") {
$where .= sprintf("AND i.item_published > %d and i.item_published < %d ", $begin, $end);
}
if ($what != "all") {
$tags = split(" ", $what);
$in = implode(", ", array_fill(0, count($tags), "'%s'"));
$from .= ", {$FOF_TAG_TABLE} t, {$FOF_ITEM_TAG_TABLE} it ";
$where .= sprintf("AND it.user_id = %d ", $user_id);
$where .= "AND it.tag_id = t.tag_id AND ( t.tag_name IN ( {$in} ) ) AND i.item_id = it.item_id ";
$group = sprintf("GROUP BY i.item_id HAVING COUNT( i.item_id ) = %d ", count($tags));
$args = array_merge($args, $tags);
}
if (!is_null($search) && $search != "") {
$where .= "AND (i.item_title like '%%%s%%' or i.item_content like '%%%s%%' )";
$args[] = $search;
$args[] = $search;
}
$order_by = "order by i.item_published desc {$limit_clause} ";
$query = $select . $from . $where . $group . $order_by;
$result = fof_safe_query($query, $args);
if (mysql_num_rows($result) == 0) {
return array();
}
while ($row = mysql_fetch_assoc($result)) {
$array[] = $row;
}
$array = fof_multi_sort($array, 'item_published', $order != "asc");
$i = 0;
foreach ($array as $item) {
$ids[] = $item['item_id'];
$lookup[$item['item_id']] = $i;
$array[$i]['tags'] = array();
$i++;
}
$items = join($ids, ", ");
$result = fof_safe_query("select {$FOF_TAG_TABLE}.tag_name, {$FOF_ITEM_TAG_TABLE}.item_id from {$FOF_TAG_TABLE}, {$FOF_ITEM_TAG_TABLE} where {$FOF_TAG_TABLE}.tag_id = {$FOF_ITEM_TAG_TABLE}.tag_id and {$FOF_ITEM_TAG_TABLE}.item_id in (%s) and {$FOF_ITEM_TAG_TABLE}.user_id = %d", $items, $user_id);
while ($row = fof_db_get_row($result)) {
$item_id = $row['item_id'];
$tag = $row['tag_name'];
$array[$lookup[$item_id]]['tags'][] = $tag;
}
return $array;
}
$feed = fof_db_get_feed_by_id($feed);
$feeds[] = $feed;
} else {
if ($fof_user_id == 1) {
$result = fof_db_get_feeds();
} else {
$result = fof_db_get_subscriptions(fof_current_user());
}
while ($feed = fof_db_get_row($result)) {
if (time() - $feed["feed_cache_date"] < $admin_prefs["manualtimeout"] * 60) {
$title = $feed['feed_title'];
list($timestamp, ) = fof_nice_time_stamp($feed['feed_cache_date']);
print "{$title} was just updated {$timestamp}!<br>";
} else {
$feeds[] = $feed;
}
}
}
$feeds = fof_multi_sort($feeds, 'feed_cache_attempt_date', false);
print "<script>\nwindow.onload = ajaxupdate;\nfeedslist = [";
foreach ($feeds as $feed) {
$title = $feed['feed_title'];
$id = $feed['feed_id'];
$feedjson[] = "{'id': {$id}, 'title': '" . addslashes($title) . "'}";
}
print join($feedjson, ", ");
print "];\n</script>";
include "footer.php";
?>
function fof_db_get_items($user_id = 1, $feed = NULL, $what = 'unread', $when = NULL, $start = NULL, $limit = NULL, $order = 'desc', $search = NULL)
{
global $FOF_SUBSCRIPTION_TABLE, $FOF_FEED_TABLE, $FOF_ITEM_TABLE, $FOF_ITEM_TAG_TABLE, $FOF_TAG_TABLE;
global $fof_connection;
$all_items = array();
if ($order != 'asc' && $order != 'desc') {
$order = 'desc';
}
fof_trace();
$prefs = fof_prefs();
$select = "SELECT i.*, f.*, s.subscription_prefs";
$from = " FROM {$FOF_FEED_TABLE} f, {$FOF_ITEM_TABLE} i, {$FOF_SUBSCRIPTION_TABLE} s";
if ($what != 'all') {
$from .= ", {$FOF_TAG_TABLE} t, {$FOF_ITEM_TAG_TABLE} it";
}
$where = " WHERE s.user_id = " . $fof_connection->quote($user_id) . " AND s.feed_id = f.feed_id AND f.feed_id = i.feed_id";
if (!empty($feed)) {
$where .= " AND f.feed_id = " . $fof_connection->quote($feed);
}
if (!empty($when)) {
$tzoffset = isset($prefs['tzoffset']) ? $prefs['tzoffset'] : 0;
$whendate = explode('/', $when == 'today' ? fof_todays_date() : $when);
$when_begin = gmmktime(0, 0, 0, $whendate[1], $whendate[2], $whendate[0]) - $tzoffset * 60 * 60;
$when_end = $when_begin + 24 * 60 * 60;
$where .= " AND i.item_published > " . $fof_connection->quote($when_begin) . " AND i.item_published < " . $fof_connection->quote($when_end);
}
if ($what != 'all') {
$tags_q = array();
foreach (explode(' ', $what) as $tag) {
$tags_q[] = $fof_connection->quote($tag);
}
$where .= " AND it.user_id = s.user_id AND it.tag_id = t.tag_id AND i.item_id = it.item_id AND t.tag_name IN (" . (count($tags_q) ? implode(', ', $tags_q) : "''") . ")";
}
if ($what == 'all') {
$group = "";
} else {
$group = " GROUP BY i.item_id HAVING COUNT( i.item_id ) = " . count($tags_q);
}
if (!empty($search)) {
$search_q = $fof_connection->quote('%' . $search . '%');
$where .= " AND (i.item_title LIKE {$search_q} OR i.item_content LIKE {$search_q} )";
}
$order_by = " ORDER BY i.item_published " . strtoupper($order);
if (is_numeric($start)) {
$order_by .= " LIMIT " . $start . ", " . (is_numeric($limit) ? $limit : $prefs['howmany']);
}
$query = $select . $from . $where . $group . $order_by;
// fof_log(__FUNCTION__ . " first query: " . $query);
$statement = $fof_connection->prepare($query);
$result = $statement->execute();
$item_ids_q = array();
$lookup = array();
/* remember item_id->all_rows mapping, for populating tags */
$idx = 0;
while (($row = fof_db_get_row($statement)) !== FALSE) {
fof_trace("collecting item_id:" . $row['item_id'] . " idx:{$idx}");
fof_db_subscription_feed_fix($row);
/* feed prefs are included, so decode them */
$item_ids_q[] = $fof_connection->quote($row['item_id']);
$lookup[$row['item_id']] = $idx;
$all_items[$idx] = $row;
$all_items[$idx]['tags'] = array();
$idx += 1;
}
$all_items = fof_multi_sort($all_items, 'item_published', $order != "asc");
$query = "SELECT t.tag_name, it.item_id" . " FROM {$FOF_TAG_TABLE} t, {$FOF_ITEM_TAG_TABLE} it" . " WHERE t.tag_id = it.tag_id" . " AND it.item_id IN (" . (count($item_ids_q) ? implode(',', $item_ids_q) : "''") . ")" . " AND it.user_id = " . $fof_connection->quote($user_id);
// fof_log(__FUNCTION__ . " second query: " . $query);
fof_trace('item_ids_q:' . implode(',', $item_ids_q));
$statement = $fof_connection->prepare($query);
$result = $statement->execute();
while (($row = fof_db_get_row($statement)) !== FALSE) {
$idx = $lookup[$row['item_id']];
$all_items[$idx]['tags'][] = $row['tag_name'];
}
fof_trace("all_items:" . var_export($all_items, true));
return $all_items;
}
function fof_get_feeds($user_id, $order = 'feed_title', $direction = 'asc')
{
$feeds = array();
$result = fof_db_get_subscriptions($user_id);
$i = 0;
while ($row = fof_db_get_row($result)) {
$id = $row['feed_id'];
$age = $row['feed_cache_date'];
$feeds[$i]['feed_id'] = $id;
$feeds[$i]['feed_url'] = $row['feed_url'];
$feeds[$i]['feed_title'] = $row['feed_title'];
$feeds[$i]['feed_link'] = $row['feed_link'];
$feeds[$i]['feed_description'] = $row['feed_description'];
$feeds[$i]['feed_image'] = $row['feed_image'];
$feeds[$i]['prefs'] = unserialize($row['subscription_prefs']);
$feeds[$i]['feed_age'] = $age;
list($agestr, $agestrabbr) = fof_nice_time_stamp($age);
$feeds[$i]['agestr'] = $agestr;
$feeds[$i]['agestrabbr'] = $agestrabbr;
$i++;
}
$tags = fof_db_get_tag_id_map();
for ($i = 0; $i < count($feeds); $i++) {
$feeds[$i]['tags'] = array();
if (is_array($feeds[$i]['prefs']['tags'])) {
foreach ($feeds[$i]['prefs']['tags'] as $tag) {
$feeds[$i]['tags'][] = $tags[$tag];
}
}
}
$result = fof_db_get_item_count($user_id);
while ($row = fof_db_get_row($result)) {
for ($i = 0; $i < count($feeds); $i++) {
if ($feeds[$i]['feed_id'] == $row['id']) {
$feeds[$i]['feed_items'] = $row['count'];
$feeds[$i]['feed_read'] = $row['count'];
$feeds[$i]['feed_unread'] = 0;
}
}
}
$result = fof_db_get_unread_item_count($user_id);
while ($row = fof_db_get_row($result)) {
for ($i = 0; $i < count($feeds); $i++) {
if ($feeds[$i]['feed_id'] == $row['id']) {
$feeds[$i]['feed_unread'] = $row['count'];
}
}
}
foreach ($feeds as $feed) {
$feed['feed_starred'] = 0;
}
$result = fof_db_get_starred_item_count($user_id);
while ($row = fof_db_get_row($result)) {
for ($i = 0; $i < count($feeds); $i++) {
if ($feeds[$i]['feed_id'] == $row['id']) {
$feeds[$i]['feed_starred'] = $row['count'];
}
}
}
$result = fof_db_get_latest_item_age($user_id);
while ($row = fof_db_get_row($result)) {
for ($i = 0; $i < count($feeds); $i++) {
if ($feeds[$i]['feed_id'] == $row['id']) {
$feeds[$i]['max_date'] = $row['max_date'];
list($agestr, $agestrabbr) = fof_nice_time_stamp($row['max_date']);
$feeds[$i]['lateststr'] = $agestr;
$feeds[$i]['lateststrabbr'] = $agestrabbr;
}
}
}
$feeds = fof_multi_sort($feeds, $order, $direction != "asc");
return $feeds;
}
function fof_get_items($feed = NULL, $what = "new", $when = NULL, $start = NULL, $limit = NULL, $order = "desc", $tags = NULL, $search)
{
if (!is_null($when) && $when != "") {
if ($when == "today") {
$whendate = date("Y/m/d", time() - FOF_TIME_OFFSET * 60 * 60);
} else {
$whendate = $when;
}
$begin = strtotime($whendate);
$begin = $begin + FOF_TIME_OFFSET * 60 * 60;
$end = $begin + 24 * 60 * 60;
$tomorrow = date("Y/m/d", $begin + 24 * 60 * 60);
$yesterday = date("Y/m/d", $begin - 24 * 60 * 60);
}
if (is_numeric($start)) {
if (!is_numeric($limit)) {
$limit = FOF_HOWMANY;
}
$limit_clause = " limit {$start}, {$limit} ";
}
$query = "select feeds.tags as feed_tags, items.read as item_read, feeds.image as feed_image, feeds.title as feed_title, feeds.link as feed_link, feeds.description as feed_description, items.id as item_id, items.link as item_link, items.title as item_title, UNIX_TIMESTAMP(items.timestamp) as timestamp, items.content as item_content, items.dcdate as dcdate, items.dccreator as dccreator, items.dcsubject as dcsubject, items.publish as item_publish, items.star as item_star from feeds, items where items.feed_id=feeds.id";
if (!is_null($feed) && $feed != "") {
$query .= " and feeds.id = {$feed}";
}
if (!is_null($when) && $when != "") {
$query .= " and UNIX_TIMESTAMP(items.timestamp) > {$begin} and UNIX_TIMESTAMP(items.timestamp) < {$end}";
}
if (!is_null($tags) && $tags != "" && $tags != "All tags") {
if ($tags == "No tags") {
$query .= " and feeds.tags IS NULL";
# or tags LIKE ''";
} else {
$query .= " and feeds.tags LIKE \"%" . $tags . "%\"";
}
}
if ($what == "published") {
$query .= " and items.publish=1";
} else {
if ($what == "public") {
$query .= " and feeds.private=0";
} else {
if ($what == "starred") {
$query .= " and items.star=1";
} else {
if ($what == "search") {
$query .= " and items.title LIKE \"%" . $search . "%\"";
} else {
if ($what != "all") {
$query .= " and items.read is null";
}
}
}
}
}
$query .= " order by timestamp desc {$limit_clause}";
$result = fof_do_query($query);
while ($row = mysql_fetch_array($result)) {
$array[] = $row;
}
$array = fof_multi_sort($array, 'timestamp', $order != "asc");
return $array;
}
function fof_get_feeds($user_id, $order = 'feed_title', $direction = 'asc')
{
$feeds = array();
$tagmap = fof_db_get_tag_id_map();
$result = fof_db_get_subscriptions($user_id);
$i = 0;
$feeds_index = array();
while (($row = fof_db_get_row($result)) !== false) {
/* remember where we are */
$feeds_index[$row['feed_id']] = $i;
/* fix user prefs */
fof_db_subscription_feed_fix($row);
/* initialize some values.. these will be populated later */
$row['feed_items'] = 0;
$row['feed_read'] = 0;
$row['feed_unread'] = 0;
$row['feed_starred'] = 0;
$row['feed_tagged'] = 0;
$row['max_date'] = 0;
$row['lateststr'] = '';
$row['lateststrabbr'] = '';
/* we can set these now, though */
$row['feed_age'] = $row['feed_cache_date'];
list($row['agestr'], $row['agestrabbr']) = fof_nice_time_stamp($row['feed_cache_date']);
$row['tags'] = array();
foreach ($row['subscription_prefs']['tags'] as $tagid) {
$row['tags'][] = $tagmap[$tagid];
}
$feeds[$i] = $row;
$i++;
}
/* tally up all items */
$result = fof_db_get_item_count($user_id);
while (($row = fof_db_get_row($result)) !== false) {
$i = $feeds_index[$row['feed_id']];
$feeds[$i]['feed_items'] += $row['count'];
$feeds[$i]['feed_read'] += $row['count'];
}
/* tally up unread items */
$result = fof_db_get_item_count($user_id, 'unread');
while (($row = fof_db_get_row($result)) !== false) {
$i = $feeds_index[$row['feed_id']];
$feeds[$i]['feed_unread'] += $row['count'];
$feeds[$i]['feed_read'] -= $row['count'];
}
/* tally up starred items */
$result = fof_db_get_item_count($user_id, 'starred');
while (($row = fof_db_get_row($result)) !== false) {
$i = $feeds_index[$row['feed_id']];
$feeds[$i]['feed_starred'] += $row['count'];
}
/* tally up tags which aren't system-tags */
$result = fof_db_get_item_count($user_id, 'tagged');
while (($row = fof_db_get_row($result)) !== false) {
$i = $feeds_index[$row['feed_id']];
$feeds[$i]['feed_tagged'] += $row['count'];
}
/* find most recent item for each feed */
$result = fof_db_get_latest_item_age($user_id);
while (($row = fof_db_get_row($result)) !== false) {
$i = $feeds_index[$row['feed_id']];
$feeds[$i]['max_date'] = $row['max_date'];
list($feeds[$i]['lateststr'], $feeds[$i]['lateststrabbr']) = fof_nice_time_stamp($row['max_date']);
}
return fof_multi_sort($feeds, $order, $direction != 'asc');
}
function fof_get_items($feed = NULL, $what = "new", $when = NULL, $start = NULL, $limit = NULL, $order = "desc")
{
global $FOF_FEED_TABLE, $FOF_ITEM_TABLE;
if (!is_null($when) && $when != "") {
if ($when == "today") {
$whendate = date("Y/m/d", time() - FOF_TIME_OFFSET * 60 * 60);
} else {
$whendate = $when;
}
$begin = strtotime($whendate);
$begin = $begin + FOF_TIME_OFFSET * 60 * 60;
$end = $begin + 24 * 60 * 60;
$tomorrow = date("Y/m/d", $begin + 24 * 60 * 60);
$yesterday = date("Y/m/d", $begin - 24 * 60 * 60);
}
if (is_numeric($start)) {
if (!is_numeric($limit)) {
$limit = FOF_HOWMANY;
}
$limit_clause = " limit {$start}, {$limit} ";
}
$query = "select {$FOF_ITEM_TABLE}.read as item_read, {$FOF_FEED_TABLE}.title as feed_title, {$FOF_FEED_TABLE}.link as feed_link, {$FOF_FEED_TABLE}.description as feed_description, {$FOF_ITEM_TABLE}.id as item_id, {$FOF_ITEM_TABLE}.link as item_link, {$FOF_ITEM_TABLE}.title as item_title, UNIX_TIMESTAMP({$FOF_ITEM_TABLE}.timestamp) as timestamp, {$FOF_ITEM_TABLE}.content as item_content, {$FOF_ITEM_TABLE}.dcdate as dcdate, {$FOF_ITEM_TABLE}.dccreator as dccreator, {$FOF_ITEM_TABLE}.dcsubject as dcsubject from {$FOF_FEED_TABLE}, {$FOF_ITEM_TABLE} where {$FOF_ITEM_TABLE}.feed_id={$FOF_FEED_TABLE}.id";
if (!is_null($feed) && $feed != "") {
$query .= " and {$FOF_FEED_TABLE}.id = {$feed}";
}
if (!is_null($when) && $when != "") {
$query .= " and UNIX_TIMESTAMP({$FOF_ITEM_TABLE}.timestamp) > {$begin} and UNIX_TIMESTAMP({$FOF_ITEM_TABLE}.timestamp) < {$end}";
}
if ($what != "all") {
$query .= " and {$FOF_ITEM_TABLE}.read is null";
}
$query .= " order by timestamp desc {$limit_clause}";
$result = fof_do_query($query);
while ($row = mysql_fetch_array($result)) {
$array[] = $row;
}
$array = fof_multi_sort($array, 'timestamp', $order != "asc");
return $array;
}
