Page 1 of 1

Count rows of a table with a particular value, return number

Posted: Fri Feb 05, 2010 3:56 pm
by shatterjack
I'm trying to count the rows of a table where a key has a particular number.

It's a custom wordpress theme and I'm having to insert and update a lot of values in the database manually.

For the details, there are two tables I am dealing with:

wp_posts - this contains the post ID and the comment_count
wp_comments - this contains the post_comment_ID which is the ID of the post that a comment is associated with


I want to run through wp_posts and increase by 1 the value of comment_count for each row where post ID(from wp_posts)=post_comment_ID(from wp_posts). Basically just count up the number of rows where a=b and put that number in c.

This is the line of code I've been fiddling with (line 36):

Code: Select all

mysql_query ("UPDATE wp_posts SET comment_count=comment_count+1 WHERE wp_posts.ID=wp_comments.comment_post_ID");
I've moved it around to different places in my index.php to see if it makes a difference but so far no luck. I screen grabbed the two tables http://www.flickr.com/photos/griever/43 ... 2/sizes/o/

and here is my index.php if it helps:

Code: Select all

<?php get_header ();?>
 
<div id="box">
 
<?php
  
$qry =  "SELECT u.id, user_nicename, post_date, post_content, post_title, post_type, post_excerpt, comment_content, comment_author, comment_approved, comment_count, comment_post_ID, comment_ID, wp_posts.ID as post_id
         FROM wp_posts
         INNER JOIN wp_users AS u ON post_author = u.id
         LEFT JOIN wp_comments ON wp_posts.ID = comment_post_ID
         WHERE post_type='post'
         ORDER BY user_nicename ASC, post_date DESC";
 
$res = mysql_query($qry);
$author = array();
 
if ($res) {
     while ($row = mysql_fetch_assoc($res)) {
          $author[$row['user_nicename']][] = $row;       
     }
        
     foreach($author as $k => $v) {
         $orderbydate[$v[0]['post_date']][$k] = $v;
     }
 
     krsort($orderbydate);
     foreach ($orderbydate as $k => $v) {
         ksort($orderbydate[$k], SORT_STRING);
     }
}
 
 
 
$divIds = array('one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine');
$i = 0;
mysql_query ("UPDATE wp_posts SET comment_count=comment_count+1 WHERE wp_posts.ID=wp_comments.comment_post_ID");
 
 
foreach ($orderbydate as $date => $authors) {
     foreach ($authors as $author=> $posts) {   
          echo '<div id="'.$divIds[$i++].'">';
          
 
 
          if($author=="tony") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/2009/12/tonyname.jpg">';
          }
          elseif($author=="hippie") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/2009/12/hippiename.jpg">';
          }
          elseif($author=="eriku") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/2009/12/erikuname.jpg">';
          }
          elseif($author=="ben") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/2009/12/benname.jpg">';
          }
          elseif($author=="hingyi") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/2009/12/hingyiname.jpg">';
          }
          elseif($author=="chris") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/chrisname.jpg">';
          }
          elseif($author=="gabe") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/gabename.jpg">';
          }
          elseif($author=="bender") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/bendername.jpg">';
          }
          elseif($author=="jake") {
               echo '<img src="http://www.hippievstony.com/wp-content/uploads/jakename.jpg">';
          }
          
 
 
 
          $lastPost = null;          
          foreach($posts as $post) {
             
               if($lastPost != $post['post_id']) {                         
                    echo '<h2>' . $post['post_title'] . '</h2><small>' . $post['post_date'] . '</small>';
                    echo '<p>' . $post['post_content'] . '</p>';
                    echo '<p>' . $post['post_excerpt'] . '</p>';                                    
                    
                    $j=0;
               }             
               $lastPost = $post['post_id'];
 
               echo '<p>' . $post['comment_content'] . '</p>';
               echo '<p><small>-' . $post['comment_author'] . '</small></p>';
               
               
               
              
               if($j==0) {
                    
                    $post_id = $post['post_id'];
                    echo '<form method="POST" action="'.$_SERVER['PHP_SELF'].'"/>';
                    echo 'Comment <input type="text" name="commenty"/>'; 
 
                    if ($user_ID) {
                         $_POST[author]=$user_login;
                         $post['comment_approved']=1;
                         echo '<input type="text" name="author" style="display:none" value="$user_login"/>';
                         echo "  ready to comment as $user_login";
                    }
                  
                    else {
                         echo 'Name <input type="text" name="author"/>';                       
                         $post['comment_approved']=0;
                         }
                    
                    echo '<p><input name="submit_comment" type="submit" id="submit" value="fly like the eagle" tabindex="5" /></p>';
                    echo '<p><input type="hidden" name="number_of_the_post" value="'.$post_id.'" /></p>';
 
                    
                    
                    if (empty($_POST['submit_comment']) || !(($post_id = (int) $_POST['submit_comment']) > 0)) {
                         # bad comment_ID;
                    }
                    
                    if (isset($_POST['submit_comment'])) {
                    $_POST[commenty]=mysql_real_escape_string($_POST[commenty]);
                    $_POST[author]=mysql_real_escape_string($_POST[author]);
                    $_POST[number_of_the_post]=mysql_real_escape_string($_POST[number_of_the_post]);  
   
                    mysql_query ("INSERT INTO wp_comments(comment_content, comment_author, comment_post_ID) VALUES ('$_POST[commenty]', '$_POST[author]', '$_POST[number_of_the_post]')");
                    
                    }
                    
                    echo $post[comment_count];
                    $_POST['submit_comment']=NULL;
                    echo '</form>';
               }
               
               $j++;
          }
          
          echo '</div>';          
     }
}
 
get_sidebar ();
get_footer();
?>