function journo_collectArticles(&$journo, $limit = 10, $offset = 0)
{
// union to merge results from "articles" and "journo_other_articles"
// into one query... NOTE: union doesn't use column names, so
// the order is important.
$sql = <<<EOT
(
SELECT a.id,a.title,a.description,a.pubdate,a.permalink, o.prettyname as srcorgname, a.srcorg,a.total_bloglinks,a.total_comments
FROM article a
INNER JOIN journo_attr attr ON a.id=attr.article_id
INNER JOIN organisation o ON o.id=a.srcorg
WHERE a.status='a' AND attr.journo_id=?
UNION ALL
SELECT NULL as id, title, NULL as description, pubdate, url as permalink, publication as srcorgname, NULL as srcorg, 0 as total_bloglinks, 0 as total_comments
FROM journo_other_articles
WHERE status='a' AND journo_id=?
)
ORDER BY pubdate DESC
LIMIT ?
OFFSET ?
EOT;
$arts = db_getAll($sql, $journo['id'], $journo['id'], $limit, $offset);
// now do a pass over to pretty up the results
foreach ($arts as &$a) {
// add pretty pubdate etc...
article_augment($a);
if (!is_null($a['id'])) {
$a['buzz'] = BuzzFragment($a);
} else {
$a['buzz'] = '';
}
// no publication given? use the hostname from the url
if (!$a['srcorgname']) {
$bits = crack_url($a['permalink']);
$a['srcorgname'] = $bits['host'];
}
}
unset($a);
return $arts;
}
function article_collect($article_id, $sim_orderby = 'score', $sim_showall = 'no')
{
$art = db_getRow('SELECT * FROM article WHERE id=?', $article_id);
if (is_null($art)) {
return null;
}
$art['article_id'] = $art['id'];
$art['id36'] = article_id_to_id36($art['id']);
$art['blog_links'] = db_getAll("SELECT * FROM article_bloglink WHERE article_id=? ORDER BY linkcreated DESC", $article_id);
// journos
$sql = <<<EOT
SELECT j.prettyname, j.ref
FROM ( journo j INNER JOIN journo_attr attr ON j.id=attr.journo_id )
WHERE attr.article_id=? AND j.status='a';
EOT;
$art['journos'] = db_getAll($sql, $article_id);
$art['byline'] = article_markup_byline($art['byline'], $art['journos']);
$orginfo = db_getRow("SELECT * FROM organisation WHERE id=?", $art['srcorg']);
$art['srcorgname'] = $orginfo['prettyname'];
$art['sop_name'] = $orginfo['sop_name'];
$art['sop_url'] = $orginfo['sop_url'];
$art['srcorg_url'] = $orginfo['home_url'];
$permalink = $art['permalink'];
$d = new datetime($art['pubdate']);
$art['pretty_pubdate'] = pretty_date(strtotime($art['pubdate']));
$art['iso_pubdate'] = $d->format('c');
$art['buzz'] = BuzzFragment($art);
/* similar articles */
if ($sim_orderby == 'date') {
$ord = 'a.pubdate DESC, s.score DESC';
} else {
// 'score'
$ord = 's.score DESC, a.pubdate DESC';
}
$sql = <<<EOT
SELECT a.id,a.title, a.srcorg,a.byline,a.permalink,a.pubdate
FROM article a INNER JOIN article_similar s ON s.other_id=a.id
WHERE s.article_id=? and a.status='a'
ORDER BY {$ord}
EOT;
/* only the first 10 by default */
if ($sim_showall != 'yes') {
$sql .= " LIMIT 10";
}
$sim_arts = db_getAll($sql, $article_id);
foreach ($sim_arts as &$s) {
article_augment($s);
}
unset($s);
$art['sim_orderby'] = $sim_orderby;
$art['sim_showall'] = $sim_showall;
$art['sim_arts'] = $sim_arts;
$tags = db_getAll('SELECT tag, freq FROM article_tag WHERE article_id=? ORDER BY freq DESC', $article_id);
$sorted_tags = array();
foreach ($tags as $t) {
$sorted_tags[$t['tag']] = intval($t['freq']);
}
ksort($sorted_tags);
$art['tags'] = $sorted_tags;
$art['comment_links'] = article_collect_commentlinks($article_id);
return $art;
}
