[PHP-WEBMASTER] com web/master: Replaced the old SQL with a much more memory and time efficient single LEFT JOIN query and only fetching one row at a time.: fetch/user-notes.php

[Sherif Ramadan]

Commit:    1deb32e96917a5d9486971db4dd6ed528d10d7fc
Author:    Sherif Ramadan <google...@php.net>         Mon, 24 Dec 2012 21:31:56 
-0500
Parents:   810fea593b33c8e967925e1923d0df3201d3b7af
Branches:  master

Link:       
http://git.php.net/?p=web/master.git;a=commitdiff;h=1deb32e96917a5d9486971db4dd6ed528d10d7fc

Log:
Replaced the old SQL with a much more memory and time efficient single LEFT 
JOIN query and only fetching one row at a time.

Changed paths:
  M  fetch/user-notes.php


Diff:
diff --git a/fetch/user-notes.php b/fetch/user-notes.php
index b24ecd5..6e3adab 100644
--- a/fetch/user-notes.php
+++ b/fetch/user-notes.php
@@ -8,24 +8,25 @@ if (!isset($_REQUEST['token']) || md5($_REQUEST['token']) != 
"19a3ec370affe2d899
 try {
     $dbh = new PDO('mysql:host=localhost;dbname=phpmasterdb', 'nobody', '');
     $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);
 } catch (PDOException $e) {
     // Old error handling was to simply exit. Do we want to log anything 
here???
     exit;
 }
 
 try {
-    $query  = "SELECT DISTINCT id,note.sect,user,note,UNIX_TIMESTAMP(ts) AS 
ts,";
-    $query .= "IF(votes=0, 10, rating/votes) AS rate";
+    $query  = "SELECT DISTINCT 
note.id,note.sect,note.user,note.note,UNIX_TIMESTAMP(note.ts) AS ts,";
+    $query .= "SUM(votes.vote) AS up, (COUNT(votes.vote) - SUM(votes.vote)) AS 
down,";
+    $query .= "ROUND((SUM(votes.vote) / COUNT(votes.vote)) * 100) AS rate";
     $query .= " FROM note";
+    $query .= " LEFT JOIN (votes) ON (note.id = votes.note_id)";
     //Only select notes that have been approved
-    $query .= " WHERE status is NULL";
-    $query .= " ORDER BY sect,rate DESC,ts DESC";
+    $query .= " WHERE note.status is NULL";
+    $query .= " GROUP BY note.id";
+    $query .= " ORDER BY note.sect,ts DESC";
 
     $stmt = $dbh->prepare($query);
     $stmt->execute();
-    $resultset = $stmt->fetchAll(PDO::FETCH_ASSOC);
-    
-    $getvotes = $dbh->prepare("SELECT SUM(votes.vote) AS up, 
(COUNT(votes.vote) - SUM(votes.vote)) AS down FROM votes WHERE votes.note_id = 
?");
 } catch (PDOException $e) {
     // Old error handling was to simply exit. Do we want to log anything 
here???
     exit;
@@ -33,8 +34,9 @@ try {
 
 // Print out a row for all notes, obfuscating the
 // email addresses as needed
-foreach ($resultset as $row) {
+while($row = $stmt->fetch(PDO::FETCH_ASSOC) {
     $user = $row['user'];
+    $row['rate'] = empty($row['rate']) ? 0 : $row['rate'];
     if ($user != "php-gene...@lists.php.net" && $user != "u...@example.com") {
         if (preg_match("!(.+)@(.+)\.(.+)!", $user)) {
             $user = str_replace(array('@', '.'), array(' at ', ' dot '), 
$user);
@@ -42,17 +44,7 @@ foreach ($resultset as $row) {
     } else {
         $user = '';
     }
-    // Calculate the votes for each note here
-    try {
-        $getvotes->execute(array($row['id']));
-        $votes = $getvotes->fetch(PDO::FETCH_ASSOC);
-        if ($votes === false) {
-            $votes = array('up' => 0, 'down' => 0);
-        }
-    } catch(PDOException $e) {
-        $votes = array('up' => 0, 'down' => 0);
-    }
     // Output here
     echo "$row[id]|$row[sect]|$row[rate]|$row[ts]|$user|",
-         
base64_encode(gzcompress($row['note'],3)),"|$votes[up]|$votes[down]\n";
+         base64_encode(gzcompress($row['note'],3)),"|$row[up]|$row[down]\n";
 }


--
PHP Webmaster List Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php