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; }