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");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();
?>