com web/master: Added voting information to search results and created new views for highest/lowest voted notes.: manage/user-notes.php

Commit: 209cc6044f1b981da0886a22ea84e4eea91e5bef Author: Sherif Ramadan <googleguy@php.net> Thu, 13 Dec 2012 12:58:33 -0500 Parents: bd65392a3f284193a49ef61d0db22c8fd0b6d7cb Branches: master Link: http://git.php.net/?p=web/master.git;a=commitdiff;h=209cc6044f1b981da0886a22ea84e4eea91e5bef Log: Added voting information to search results and created new views for highest/lowest voted notes. Changed paths: M manage/user-notes.php Diff: diff --git a/manage/user-notes.php b/manage/user-notes.php index 64035ce..09c0164 100644 --- a/manage/user-notes.php +++ b/manage/user-notes.php @@ -64,11 +64,14 @@ if (!$action) { if (isset($_REQUEST['keyword']) || isset($_REQUEST["view"])) { if(isset($_REQUEST['keyword'])) { - $sql = 'SELECT *,UNIX_TIMESTAMP(ts) AS ts FROM note WHERE '; + $sql = 'SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, note.*, UNIX_TIMESTAMP(note.ts) AS ts '. + 'FROM note '. + 'JOIN(votes) ON (note.id = votes.note_id) '. + 'WHERE '; if (is_numeric($_REQUEST['keyword'])) { - $sql .= 'id = ' . (int) $_REQUEST['keyword']; + $sql .= 'note.id = ' . (int) $_REQUEST['keyword']; } else { - $sql .= 'note LIKE "%' . real_clean($_REQUEST['keyword']) . '%" LiMIT 20'; + $sql .= 'note.note LIKE "%' . real_clean($_REQUEST['keyword']) . '%" LiMIT 20'; } } else { $page = isset($_REQUEST["page"]) ? intval($_REQUEST["page"]) : 0; @@ -90,13 +93,27 @@ if (!$action) { "FROM note ". "JOIN(votes) ON (note.id = votes.note_id) ". "GROUP BY note.id ORDER BY LENGTH(note.note) ASC LIMIT $limit, 10"; + /* Top rated notes */ + } else if ($type == 3) { + $sql = "SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, ". + "(SUM(votes.vote) - (COUNT(votes.vote) - SUM(votes.vote))) AS rating, note.*, UNIX_TIMESTAMP(note.ts) AS ts ". + "FROM note ". + "JOIN(votes) ON (note.id = votes.note_id) ". + "GROUP BY note.id ORDER BY rating ASC LIMIT $limit, 10"; + /* Bottom rated notes */ + } else if ($type == 4) { + $sql = "SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, ". + "(SUM(votes.vote) - (COUNT(votes.vote) - SUM(votes.vote))) AS rating, note.*, UNIX_TIMESTAMP(note.ts) AS ts ". + "FROM note ". + "JOIN(votes) ON (note.id = votes.note_id) ". + "GROUP BY note.id ORDER BY rating DESC LIMIT $limit, 10"; /* Last notes */ } else { $sql = "SELECT SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS down, note.*, UNIX_TIMESTAMP(note.ts) AS ts ". "FROM note ". "JOIN(votes) ON (note.id = votes.note_id) ". "GROUP BY note.id ORDER BY note.id DESC LIMIT $limit, 10"; - } + } } if ($result = db_query($sql)) { @@ -163,6 +180,8 @@ if (!$action) { <p><a href="<?= PHP_SELF ?>?view=notes&type=0">View last 10 notes</a></p> <p><a href="<?= PHP_SELF ?>?view=notes&type=1">View first 10 notes</a></p> <p><a href="<?= PHP_SELF ?>?view=notes&type=2">View minor 10 notes</a></p> +<p><a href="<?= PHP_SELF ?>?view=notes&type=3">View top 10 rated notes</a></p> +<p><a href="<?= PHP_SELF ?>?view=notes&type=4">View bottom 10 rated notes</a></p> <?php foot(); exit;