title: Counting number of positive and negative votes:
body: <p>I have the following tables:</p>
  
  <pre><code>post (id, title, content) etc
  author (id, username) etc
  author_vote (post_id, author_id, value)
  </code></pre>
  
  <p>Value is a tiny_int that can either be 1 or -1.</p>
  
  <p>I want to count the number of positive and negative votes for each post:</p>
  
  <pre><code>$posts = sql_select($link, "SELECT post.*, author.username 
                              FROM post, author 
                              AND author.id = post.author_id");
  </code></pre>
  
  <p>Why does the following code not work?</p>
  
  <pre><code>array_walk($posts, function(&amp;$post, $link){
     $post['positive'] = sql_select($link, "SELECT SUM(value) FROM author_vote WHERE post_id = $post['id']
                                              AND value  = 1");
  
     $post['negative'] = abs(sql_select($link, "SELECT SUM(value) FROM author_vote WHERE post_id = $post['id']
                                              AND value  = -1"));
  });
  </code></pre>
  
  <p>I've also tried the following, this causes all the votes to be the same for each post:</p>
  
  <pre><code>foreach ($posts as &amp;$post)
  {
     $id = $post['id'];
     $post['positive'] = (int)sql_select($link, "SELECT SUM(value) FROM author_vote WHERE post_id = $id
                                             AND value  = 1");
     $post['negative'] = (int)abs(sql_select($link, "SELECT SUM(value) FROM author_vote WHERE post_id = $id
                                                 AND value  = -1"));
  }
  </code></pre>
  
  <p>Also is there any way to do this without having to query the database multiple times for each post? How would something that is constantly changing [like this] be (mem)cached?</p>