dsn); // Reset the index table $db->query('DELETE FROM insurge_index'); $db->query('INSERT INTO insurge_index (bnum) SELECT locum_bib_items.bnum FROM locum_bib_items'); // Do the ratings. // Get the average rating for all entries $dbq = $db->query("SELECT AVG(rating) FROM insurge_ratings"); $avg_rating_total = $dbq->fetchOne(); // Get totals and averages $dbq = $db->query("SELECT DISTINCT(bnum) AS bnum, COUNT(*) AS vote_count, AVG(rating) AS avg_rating FROM insurge_ratings GROUP BY bnum"); $item_tot_avg_arr = $dbq->fetchAll(MDB2_FETCHMODE_ASSOC); $vote_count = 0; // First time through... foreach ($item_tot_avg_arr as $item_tot_avg) { $vote_count = $vote_count + $item_tot_avg['vote_count']; } if (count($item_tot_avg_arr)) { $vote_count_avg = $vote_count / count($item_tot_avg_arr); } else { $vote_count_avg = 0; } // Second time through... Create the baysian rank value // FYI, eq is: ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / (avg_num_votes + this_num_votes) if ($vote_count_avg) { $ratings_rank = array(); foreach ($item_tot_avg_arr as $item_tot_avg) { $ratings_rank[$item_tot_avg['bnum']] = (($vote_count_avg * $avg_rating_total) + ($item_tot_avg['vote_count'] * $item_tot_avg['avg_rating'])) / ($vote_count_avg + $item_tot_avg['vote_count']); } unset($item_tot_avg_arr); // Free up the memory foreach ($ratings_rank as $bnum => $rating) { $rating_prepped = round($rating, 6) * 1000000; $db->exec("UPDATE insurge_index SET rating_idx = '$rating_prepped' WHERE bnum = '$bnum'"); } } /* TODO: jblyberg: tag and review indexing can be done in single sql statements i think UPDATE insurge_index, (SELECT bnum, group_concat(rev_title,' ',rev_body) as review FROM insurge_reviews GROUP BY bnum) as temprev SET insurge_index.review_idx = temprev.review WHERE insurge_index.bnum = temprev.bnum UPDATE insurge_index, (SELECT bnum, group_concat(tag SEPARATOR ' ') as tag FROM insurge_tags GROUP BY bnum) as tagtemp SET insurge_index.tag_idx = tagtemp.tag WHERE insurge_index.bnum = tagtemp.bnum */ // Now for the tags. $dbq = $db->query("SELECT bnum, tag FROM insurge_tags"); $tag_arr = $dbq->fetchAll(MDB2_FETCHMODE_ASSOC); $tags = array(); foreach ($tag_arr as $tag_vals) { $tags[$tag_vals['bnum']] .= ' ' . $tag_vals['tag']; } unset($tag_arr); // Free up the memory foreach ($tags as $bnum => $tag) { $tag = $db->quote(trim($tag), 'text'); $db->exec("UPDATE insurge_index SET tag_idx = $tag WHERE bnum = '$bnum'"); } // And finally, the reviews. $dbq = $db->query("SELECT bnum, rev_title, rev_body FROM insurge_reviews"); $rev_arr = $dbq->fetchAll(MDB2_FETCHMODE_ASSOC); $rev = array(); foreach ($rev_arr as $rev_vals) { $rev[$rev_vals['bnum']] .= ' ' . $rev_vals['rev_title'] . ' ' . $rev_vals['rev_body']; } unset ($rev_arr); // Free up the memory foreach ($rev as $bnum => $rev_text) { $rev_text = $db->quote(trim($rev_text), 'text'); $db->exec("UPDATE insurge_index SET review_idx = $rev_text WHERE bnum = '$bnum'"); } } }