Decrement int mysql

[GlenRogers]

I have 2 tables for a simple massage board, one for questions and one for answers.
The table forum_questions has a field called 'reply' with holds an int value for the number of answers the question has.

My problem is this:
I have a form to delete answers, you pick the question from a dropdown and the answers to that question are displayed with checkboxes for deletion. This works ok, but I'm stuck at how I then get it to decrement the 'reply' fiel in forum_question by the amount of answers that werwe removed.

This is my form

<form class='form2' action='forumadminpage.php' method='post' enctype='multipart/form-data' >

                  <b><span class='formheading'>Choose A Topic</span><br />To Remove Comments</b><br />
                   <select name="comments">
                      <?php echo $op;?>
                   </select>
                   <input type='submit' name='submit' value='Get Comments' />
                   <?php
                   include '../inc/connect.php';
                   if(isset($_POST['submit'])){
                   $comments= intval($_POST['comments']);
                   $data = mysqli_query($link, "SELECT * FROM forum_answer WHERE question_id IN ($comments) ")
                     or die(mysql_error());
                     while($info = mysqli_fetch_array( $data )) { 
                        echo "<p>";
                        echo "<input type='checkbox' name='remove[{$info['id']}]' value='Remove' />";
                        echo $info['id'];
                        echo ':   ';
                        echo $info['a_title'];
                        echo "</p>";
                     }
                     echo"<input type='submit' name='submit' value='Delete Comments' />";
                     }



                   ?>

                   </form>

And this is the php to remove answers

<?php
                   if(isset($_POST['remove'])){
                         $chk = (array) $_POST['remove'];
                         $p = implode(',',array_keys($chk)); 
                         if ($sql = mysqli_query($link, "DELETE FROM forum_answer WHERE id IN ($p )")){
                          header( 'Location: forumadminpage.php' ) ;
                         }
                         else{
                      echo '<script type="text/javascript"> alert("Comments have not been removed, try again or contact site developer") </script>';
                   }
                   }
                   ?>

Can anyone help here?
Thanks...

[Atli]

There really is no need to store the reply count in the questions table to begin with. That is a value you can easily calculate based on the data. If you did that, you wouldn't have to go out of your way to keep that value in sync with the real data, or risk it ever going out of sync. (No code is perfect and bug free, after all.)

A basic LEFT JOIN and a GROUP BY clause, with a COUNT on the joned table, could get you this value in the query that fetches the other question fields. Something like:

SELECT
    q.id,
    q.title,
    q.whatever_else,
    COUNT(r.id) AS reply_count
FROM forum_questions AS q
LEFT JOIN forum_replies AS r
    ON q.id = r.question_id
WHERE q.id = 42
GROUP BY q.id;

The artificial reply_count field in that result set would tell you how many replies match up with the question you are fetching.

I use a LEFT JOIN there instead of the normal cross join so questions without any replies won't be automatically excluded from the result set.

[GlenRogers]

So reply_count is the number of replies? How do I use that?

I tried this

echo reply_count;

But it doesnt work!

[Atli]

Yes, that's the idea.

That code makes no sense; it has no context. Show us how you are executing the query and reading the results.

[GlenRogers]

Ah nevermind, worked it out. Doh. Thanks for the help, much better than what I was trying to do!