Getting reply count to increase...

[Seaholme]

Hey,

I'm working on editing some code for a simple forum, however I'm having difficulty with the script to add a reply to a topic. All of it works fine except for the reply ID -- every reply is listed as no. 1, whereas it's meant to add on another digit for every subsequent reply so the first reply is no. 1, the second reply is no. 2, the third reply is no. 3 and so on. I've enclosed the main body of the php (there's connecting to the database beforehand, and that's pretty much it) , and was wondering if anybody could help me work out why it's not adding on another number for every new reply? :[

 

// Get value of id that sent from hidden field 
$id=$_POST['id'];

// Find highest answer number. 
$sql="SELECT MAX('a_id') AS Maxa_id FROM $tbl_name WHERE question_id='$id'";
$result=mysql_query($sql) or die(mysql_error());
$rows=mysql_fetch_array($result);

// add + 1 to highest answer number and keep it in variable name "$Max_id". if there no answer yet set it = 1 
if ($rows) {
$Max_id = $rows['Maxa_id']+1;
}
else {
$Max_id = 1;
}

// get values that sent from form 
$a_name=$_POST['a_name'];
$a_answer=$_POST['a_answer']; 

$datetime=date("d/m/y H:i"); // create date and time

// Insert answer 
$sql2="INSERT INTO $tbl_name(question_id, a_id, a_name, a_answer, a_datetime)VALUES('$id', '$Max_id', '$a_name', '$a_answer', '$datetime')";
$result2=mysql_query($sql2);

if($result2){
echo "Your reply has been posted.<BR>";
echo "<a href='view_topic.php?id=".$id."'>View Topic</a><br><a href=comms.php>Return to Comms</a>";

// If added new answer, add value +1 in reply column 
$tbl_name2="forum_question";
$sql3="UPDATE $tbl_name2 SET reply='$Max_id' WHERE id='$id'";
$result3=mysql_query($sql3);

}

 

Thank-you!

[Seaholme]

Just poking this topic back onto page 1 O:

[Pikachu2000]

My guess would be a problem in this block if ($rows) { that would cause that statement never to evaluate as true, therefore setting the field to 1 as instructed by the else{}. Add some echos to see whether the if or the else part of the conditional is executing.

 

 

if ($rows) {

$Max_id = $rows['Maxa_id']+1;

echo "<br />IF executes<br />:

}

else {

$Max_id = 1;

echo "<br />ELSE executes<br />";

}

[Seaholme]

Heya,

 

I did what you said and rather confusingly, it came out with the IF executing, rather than the else! o.O So it's not just defaulting to 1 via the ELSE clause.

[Seaholme]

Okay, so I tried your tactic of echoing things to see whereabouts the problem might be and I did

 

if ($rows) {
$Max_id = $rows['Maxa_id']+1;
echo $rows['Maxa_id'];
}
else {
$Max_id = 1;
}

 

and the echo was "a_id". Pretty sure it should have echoed a number there (number 1, I think)! I'm still trying to figure this out, but any thoughts are welcome

 

[Pikachu2000]

Then the variable has to have a value of zero coming from the database, somehow. Echo the variable both before and after the addition operation to see if I'm on the right track. Also might be a good idea to echo the query string to make sure it holds the values you'd expect it to hold.

 

[/code]

if ($rows) {

$Max_id = $rows['Maxa_id']+1;

echo "Query: " . $sql . "<br />";

echo "<br />Before: {$rows['Maxa_id']}<br />:

echo "<br />After: {$Max_id}<br />";

}

[/code]

[Pikachu2000]

Then the variable has to have a value of zero coming from the database, somehow. Echo the variable both before and after the addition operation to see if I'm on the right track. Also might be a good idea to echo the query string to make sure it holds the values you'd expect it to hold.

 

[/code]

if ($rows) {

$Max_id = $rows['Maxa_id']+1;

echo "Query: " . $sql . "<br />";

echo "<br />Before: " ; print_r($rows['Maxa_id']);  echo "<br />";

echo "<br />After: {$Max_id}<br />";

}

[/code]

[Seaholme]

Hey -- firstly, thanks for all the help! Secondly, this is the output I get from doing what you suggested...

 

Query: SELECT MAX('a_id') AS Maxa_id FROM forum_answer WHERE question_id='34'

 

Before: a_id

 

After: 1

 

Should that query read something different? I have the table set up so that a_id has a default value of 0, if that helps at all. I can't work out if that might be relevant or not, but at this stage I'm starting to think everything might be relevant, this has been giving me a headache for ages now!

[Pikachu2000]

Ah, now I see it. You need to change the single-quotes to backticks in the MAX('a_id'). It's just returning the literal value that is inside the single quotes within the parentheses. Also, if the value of $id is to always be an integer, leave the single quotes off of it in the "WHERE question_id='$id'" clause.

$sql = "SELECT MAX(`a_id`) as Maxa_id FROM $tbl_name WHERE question_id=" . $id;

[Pikachu2000]

Hey -- firstly, thanks for all the help! Secondly, this is the output I get from doing what you suggested...

 

Query: SELECT MAX('a_id') AS Maxa_id FROM forum_answer WHERE question_id='34'

 

Before: a_id

 

After: 1

 

Should that query read something different? I have the table set up so that a_id has a default value of 0, if that helps at all. I can't work out if that might be relevant or not, but at this stage I'm starting to think everything might be relevant, this has been giving me a headache for ages now!

 

The query, with the modifications I put in my last post, should be just fine for the moment. To prevent SQL injection and XSS attacks, you will want to sanitize the user-input form values that come from the $_POST array before using them in a query, though. You do that by casting integers as integers, running strings through mysql_real_escape_string(), and stripping out potentially harmful tags.

 

The database table, in this case should probably not allow a NULL value for that field. Judging by your description,  I would think all posts should have a reply id even if it's a zero to represent the first post of a thread.

[Seaholme]

Fixed! I changed it to backticks and it worked!

 

Thanks a billion, you've been so helpful!  :D

[Pikachu2000]

Excellent! Glad I could help.