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;
}
function fof_get_nav_links($feed = NULL, $what = "new", $when = NULL, $start = NULL, $limit = NULL)
{
$prefs = fof_prefs();
$string = "";
if (!is_null($when) && $when != "") {
if ($when == "today") {
$whendate = fof_todays_date();
} else {
$whendate = $when;
}
$begin = strtotime($whendate);
$tomorrow = date("Y/m/d", $begin + 24 * 60 * 60);
$yesterday = date("Y/m/d", $begin - 24 * 60 * 60);
$string .= "<a href=\".?feed={$feed}&what={$what}&when={$yesterday}&how={$how}&howmany={$howmany}\">[« {$yesterday}]</a> ";
if ($when != "today") {
$string .= "<a href=\".?feed={$feed}&what={$what}&when=today&how={$how}&howmany={$howmany}\">[today]</a> ";
}
if ($when != "today") {
$string .= "<a href=\".?feed={$feed}&what={$what}&when={$tomorrow}&how={$how}&howmany={$howmany}\">[{$tomorrow} »]</a> ";
}
}
if (is_numeric($start)) {
if (!is_numeric($limit)) {
$limit = $prefs["howmany"];
}
$earlier = $start + $limit;
$later = $start - $limit;
$string .= "<a href=\".?feed={$feed}&what={$what}&when={$when}&how=paged&which={$earlier}&howmany={$limit}\">[« previous {$limit}]</a> ";
if ($later >= 0) {
$string .= "<a href=\".?feed={$feed}&what={$what}&when={$when}&how=paged&howmany={$limit}\">[current items]</a> ";
}
if ($later >= 0) {
$string .= "<a href=\".?feed={$feed}&what={$what}&when={$when}&how=paged&which={$later}&howmany={$limit}\">[next {$limit} »]</a> ";
}
}
return $string;
}
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_nav_links($feed = NULL, $what = 'new', $when = NULL, $start = NULL, $limit = NULL, $search = NULL, $itemcount = 9999)
{
$prefs = fof_prefs();
$navlinks = '';
$qv = array('feed' => $feed, 'what' => $what, 'when' => $when, 'search' => $search, 'howmany' => $limit);
if (!empty($when)) {
$begin = strtotime($when == 'today' ? fof_todays_date() : $when);
$tomorrow = date("Y/m/d", $begin + 24 * 60 * 60);
$yesterday = date("Y/m/d", $begin - 24 * 60 * 60);
$navlinks .= '<a href="' . fof_url('.', array_merge($qv, array('when' => $yesterday))) . '">[« ' . $yesterday . ']</a>';
if ($when != "today") {
$navlinks .= ' <a href="' . fof_url('.', array_merge($qv, array('when' => 'today'))) . '">[today]</a>';
$navlinks .= ' <a href="' . fof_url('.', array_merge($qv, array('when' => 'tomorrow'))) . '">[' . $tomorrow . '»]</a>';
}
}
if (is_numeric($start)) {
if (!is_numeric($limit)) {
$limit = isset($prefs['howmany']) ? $prefs['howmany'] : NULL;
$qv['howmany'] = $limit;
}
if ($itemcount <= $limit) {
return '';
}
$earlier = $start + $limit;
$later = $start - $limit;
$qv['how'] = 'paged';
if ($itemcount > $earlier) {
$navlinks .= ' <a href="' . fof_url('.', array_merge($qv, array('which' => $earlier))) . '">[« previous ' . $limit . ']</a>';
$navlinks .= ' <a href="' . fof_url('.', array_merge($qv, array('how' => 'unpaged'))) . '">[all-at-once]</a>';
}
if ($later >= 0) {
$navlinks .= ' <a href="' . fof_url('.', array_merge($qv, array('which' => $start))) . '">[current items]</a>';
$navlinks .= ' <a href="' . fof_url('.', array_merge($qv, array('which' => $later))) . '">[next ' . $limit . ' »]</a>';
}
}
return $navlinks;
}