forum trouble

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

simmsy
Forum Newbie
Posts: 24
Joined: Wed Apr 14, 2010 9:50 am

forum trouble

Post 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();
?>
Last edited by John Cartwright on Wed Apr 21, 2010 2:11 pm, edited 1 time in total.
Reason: Please use proper PHP tags when posting code, and do not post your database credentials (they have been since removed).
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: forum trouble

Post 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;
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
simmsy
Forum Newbie
Posts: 24
Joined: Wed Apr 14, 2010 9:50 am

Re: forum trouble

Post by simmsy »

Hi i took that little part out and it still says error and doesnt add the reply?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: forum trouble

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
sophie
Forum Newbie
Posts: 10
Joined: Wed May 23, 2012 1:17 am

Re: forum trouble

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: forum trouble

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
sophie
Forum Newbie
Posts: 10
Joined: Wed May 23, 2012 1:17 am

Re: forum trouble

Post 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:
sophie
Forum Newbie
Posts: 10
Joined: Wed May 23, 2012 1:17 am

Re: forum trouble

Post 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.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: forum trouble

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
sophie
Forum Newbie
Posts: 10
Joined: Wed May 23, 2012 1:17 am

Re: forum trouble

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: forum trouble

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
sophie
Forum Newbie
Posts: 10
Joined: Wed May 23, 2012 1:17 am

Re: forum trouble

Post 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 :?:
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: forum trouble

Post 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?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
sophie
Forum Newbie
Posts: 10
Joined: Wed May 23, 2012 1:17 am

Re: forum trouble

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: forum trouble

Post 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;
        }
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply