Page 1 of 2
forum trouble
Posted: Wed Apr 21, 2010 3:39 am
by simmsy
hi was just wondering why anyone knew why this lets me post a topic then 1 reply then keeps coming back as error this is the add_answer.php code:
Code: Select all
<?php
$host="localhost"; // Host name
$username="****"; // Mysql username
$password="****"; // Mysql password
$db_name="fightwat_users"; // Database name
$tbl_name="forum_answer"; // Table name
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
// 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);
$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_email=$_POST['a_email'];
$a_answer=$_POST['a_answer'];
$datetime=date("d/m/y H:i:s"); // create date and time
// Insert answer
$sql2="INSERT INTO $tbl_name(question_id, a_id, a_name, a_email, a_answer, a_datetime)VALUES('$id', '$Max_id', '$a_name', '$a_email', '$a_answer', '$datetime')";
$result2=mysql_query($sql2);
if($result2){
echo "Successful<BR>";
echo "<a href='view_topic.php?id=".$id."'>View your answer</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);
}
else {
echo "ERROR";
}
mysql_close();
?>
Re: forum trouble
Posted: Wed Apr 21, 2010 5:49 am
by social_experiment
I think the problem is with this sql query:
Code: Select all
$sql="SELECT MAX(a_id) AS Maxa_id FROM $tbl_name WHERE question_id='$id'";
You are telling your query to search the database for the highest value (MAX())....WHERE you want it to (WHERE question_id=$id) be. The query runs no problem but when it finds the value (according to the value of 'question_id') and it is NOT the maximum value of 'a_id' in the table it returns a value of NULL.
MAX(columnname) Returns the largest value in columnname
So you would use it (working from your example) as follows : SELECT MAX(a_id) AS Maxa_id FROM $tbl_name;
Re: forum trouble
Posted: Wed Apr 21, 2010 12:32 pm
by simmsy
Hi i took that little part out and it still says error and doesnt add the reply?
Re: forum trouble
Posted: Wed Apr 21, 2010 5:46 pm
by social_experiment
The problem could be with your queries '$sql2' and '$sql3'. Try the following code and paste the errors you receive (if any).
Code: Select all
<?php $result2=mysql_query($sql2);
//change it to
$result2 = mysql_query($sql2) or die(mysql_error()); ?>
do the same with
Code: Select all
<?php $result3=mysql_query($sql3);
//change it to
$result3 = mysql_query($sql3) or die(mysql_error()); ?>
Also, please paste your table structures as well as some sample data.
Re: forum trouble
Posted: Wed May 23, 2012 1:28 am
by sophie
we got this same problem because we got it on the same site so here is the code Im trying to debug.
Code: Select all
<?php
$con = mysql_connect('localhost','root','admin');
if($con){
mysql_select_db('test_user',$con);
$id=$_POST['id'];
$sql="SELECT MAX(a_id) AS Maxa_id FROM forum_question";
$result=mysql_query($sql);
if(!$result) {
echo "The following SQL FAILED" . $sql;
}
else {
$rows=mysql_fetch_array($result);
if($rows){
$Max_id = $rows['Maxa_id']+1;
}
else{
$Max_id =1;
}
}
$a_name=$_POST['a_name'];
$a_email=$_POST['a_email'];
$a_answer=$_POST['a_answer'];
date_default_timezone_set('UTC');
$datetime=date("d/m/y h:i:s");
$sql2="INSERT INTO forum_answer(question_id,a_id,a_name,a_email,a_answer,a_datetime)
VALUES('$id','$Max_id','$a_name','$a_email','$a_answer','$datetime')";
$result2=mysql_query($sql2) or die (mysql_error());
if($result2){
echo "Success<br>";
echo "<a href='view_topic.php?id=".$id."'> View Your answer </a>";
$sql3="UPDATE forum_question SET reply='$Max_id' WHERE id='$id'";
$result3=mysql_query($sql3) or die (mysql_error());
} else{
echo "Error";
}
mysql_close($con);
}
?>
i tried your suggestion here and here is the error.
The following SQL FAILEDSELECT MAX(a_id) AS Maxa_id FROM forum_question
Notice: Undefined variable: Max_id in C:\websites\reservation\add_answer.php on line 31
Incorrect integer value: '' for column 'question_id' at row 1
help us! newbie here. thank you
Re: forum trouble
Posted: Wed May 23, 2012 1:43 am
by social_experiment
To get the error that is returned by the sql query modify your code like the snippet below
Code: Select all
<?php
if(!$result) {
echo "The following SQL FAILED " . mysql_error();
}
?>
The notice and error that follows is as a result of the error in the query. Paste the received error message back here for further assistance
Re: forum trouble
Posted: Wed May 23, 2012 10:09 pm
by sophie
this is the error :
The following SQL FAILEDUnknown column 'a_id' in 'field list'
Notice: Undefined variable: Max_id in C:\websites\reservation\add_answer.php on line 31
Incorrect integer value: '' for column 'question_id' at row 1
a_id was in my database..question_id was in my database too

Re: forum trouble
Posted: Wed May 23, 2012 11:59 pm
by sophie
and btw i used mysql workbench so i do not code mysql manually

definitely i think its not a syntax error on my mysql.
Re: forum trouble
Posted: Thu May 24, 2012 12:27 am
by social_experiment
Code: Select all
<?php
$sql="SELECT MAX(a_id) AS Maxa_id FROM forum_question";
?>
Can you paste the structure for 'forum_question' table?
The syntax doesn't seem to be a problem, the error you are receiving is issued when a column isn't present in a table but the query still wants to access it.
Re: forum trouble
Posted: Thu May 24, 2012 2:35 am
by sophie
arg! darn! why I didn't see that? a_id is from 'forum_answer' and not in 'forum_question' thank you for that.
last error I think
Incorrect integer value: '' for column 'question_id' at row 1
question_id is from 'forum_answer' table
Re: forum trouble
Posted: Thu May 24, 2012 3:38 am
by social_experiment
What type is expected for 'question_id'? (string, integer)?
If you are still receiving this error it could be due to incorrect type being passed to the column
Re: forum trouble
Posted: Thu May 24, 2012 11:12 pm
by sophie
here is my database..
[img]
coulumn name |data type|
question_id | int(11) |Primary |not null |auto increment
a_id | int(11) | null
a_name
a_email
a_answer
a_datetime
[/img]
should i remove it as my primary key? question_id is my id number when i choose to reply some questions in the topic. i had another id anway

Re: forum trouble
Posted: Fri May 25, 2012 12:18 am
by social_experiment
sophie wrote:should i remove it as my primary key?
no this isn't necessary;
Code: Select all
<?php
$sql="SELECT MAX(a_id) AS Maxa_id FROM forum_question";
echo $sql;
?>
what is returned if you echo the above variable?
Re: forum trouble
Posted: Fri May 25, 2012 2:19 am
by sophie
nothing just the same error
SELECT MAX(a_id) AS Maxa_id FROM forum_answerIncorrect integer value: '' for column 'question_id' at row 1
Re: forum trouble
Posted: Fri May 25, 2012 2:48 am
by social_experiment
The value of $Max_id in this instance, what is display to the browser?
Code: Select all
<?php
if($rows){
$Max_id = $rows['Maxa_id']+1;
echo $Max_id;
}
?>