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 :banghead: :banghead: :banghead:

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. :lol:

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;
        }
?>