Inserting info into MySQL Table

[HanneSThEGreaT]

Hello, me again.

 

I have created a little forum - very very basic.

 

main_forum.php :

 

<?php
$host="localhost"; // Host name 
$username="yvonnedp"; // Mysql username 
$password="yvonne"; // Mysql password 
$db_name="forum"; // Database name 
$tbl_name="forum_question"; // Table name 

// Connect to server and select databse.
mysql_connect('localhost', 'yvonnedp', 'yvonne')or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name ORDER BY id";
// OREDER BY id DESC is order result by descending 
$result=mysql_query($sql);
?>
<table width="90%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td width="6%" align="center" bgcolor="#E6E6E6"><strong>#</strong></td>
<td width="53%" align="center" bgcolor="#E6E6E6"><strong>Topic</strong></td>
<td width="15%" align="center" bgcolor="#E6E6E6"><strong>Views</strong></td>
<td width="13%" align="center" bgcolor="#E6E6E6"><strong>Replies</strong></td>
<td width="13%" align="center" bgcolor="#E6E6E6"><strong>Date/Time</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){ // Start looping table row 
?>
<tr>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo $rows['topic']; ?></a><BR></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['view']; ?></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['reply']; ?></td>
<td align="center" bgcolor="#FFFFFF"><? echo $rows['datetime']; ?></td>
</tr>

<?php
// Exit looping and close connection 
}
mysql_close();
?>

 

create_topic.php :

 

<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form id="form1" name="form1" method="post" action="add_topic.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td colspan="3" bgcolor="#E6E6E6"><strong>Create New Topic</strong> </td>
</tr>
<tr>
<td width="14%"><strong>Topic</strong></td>
<td width="2%">:</td>
<td width="84%"><input name="topic" type="text" id="topic" size="50" /></td>
</tr>
<tr>
<td valign="top"><strong>Detail</strong></td>
<td valign="top">:</td>
<td><textarea name="detail" cols="50" rows="3" id="detail"></textarea></td>
</tr>
<tr>
<td><strong>Name</strong></td>
<td>:</td>
<td><input name="name" type="text" id="name" size="50" /></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td><input type="submit" name="Submit" value="Submit" /> <input type="reset" name="Submit2" value="Reset" /></td>
</tr>
</table>
</td>
</form>
</tr>
</table>

 

add_topic.php :

 

<?php
$host="localhost"; // Host name 
$username="yvonnedp"; // Mysql username 
$password="yvonne"; // Mysql password 
$db_name="forum"; // Database name 
$tbl_name="forum_question"; // Table name 

// Connect to server and select database.
mysql_connect('localhost', 'yvonnedp', 'yvonne')or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get data that sent from form 
$topic=$_POST['topic'];
$detail=$_POST['detail'];
$name=$_POST['name'];

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

$sql="INSERT INTO $tbl_name(topic, detail, name, datetime)VALUES('$topic', '$detail', '$name', '$datetime')";
$result=mysql_query($sql);

if($result){
echo "Successful<BR>";
echo "<a href=main_forum.php>View your topic</a>";

 $email_from = "forums@thenewme.co.za";
     $email_to = "info@thenewme.co.za";
     $email_subject = "The New Me - New Forum Topic";

     $email_message = "A new topic has been posted on the Forum!\n";
     $email_message .= "Check it out : http://www.thenewme.co.za/forum/main_forum.php";
     
     $headers = 'From: '.$email_from."\r\n";
'Reply-To: '.$email_from."\r\n" . 'X-Mailer: PHP/' . phpversion();
  @mail($email_to, $email_subject, $email_message, $headers);


}
else {
echo "ERROR";
}
mysql_close();
?>

 

add_answer.php :

 

<?php

$host="localhost"; // Host name 
$username="yvonnedp"; // Mysql username 
$password="yvonne"; // Mysql password 
$db_name="forum"; // Database name 
$tbl_name="forum_answer"; // Table name 

// Connect to server and select databse.
mysql_connect('localhost', 'yvonnedp', 'yvonne')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_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_answer, a_datetime)VALUES('$id', '$Max_id', '$a_name', '$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);

 $email_from = "forums@thenewme.co.za";
     $email_to = "info@thenewme.co.za";
     $email_subject = "The New Me - New Forum Answer";

     $email_message = "A new answer to a forum topic has been posted on the Forum!\n";
     $email_message .= "Check it out : http://www.thenewme.co.za/forum/main_forum.php";
     
     $headers = 'From: '.$email_from."\r\n";
'Reply-To: '.$email_from."\r\n" . 'X-Mailer: PHP/' . phpversion();
  @mail($email_to, $email_subject, $email_message, $headers);

}
else {
echo "ERROR";
}

mysql_close();
?>

 

view_topic :

 

<?php
$tbl_name2="forum_answer"; // Switch to table "forum_answer" 

$sql2="SELECT * FROM $tbl_name2 WHERE question_id='$id'";
$result2=mysql_query($sql2);

while($rows=mysql_fetch_array($result2)){
?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong>ID</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_id']; ?></td>
</tr>
<tr>
<td width="18%" bgcolor="#F8F7F1"><strong>Name</strong></td>
<td width="5%" bgcolor="#F8F7F1">:</td>
<td width="77%" bgcolor="#F8F7F1"><? echo $rows['a_name']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Answer</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_answer']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Date/Time</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><? echo $rows['a_datetime']; ?></td>
</tr>
</table></td>
</tr>
</table><br>

<?
}

$sql3="SELECT view FROM $tbl_name WHERE id='$id'";
$result3=mysql_query($sql3);

$rows=mysql_fetch_array($result3);
$view=$rows['view'];

// if have no counter value set counter = 1
if(empty($view)){
$view=1;
$sql4="INSERT INTO $tbl_name(view) VALUES('$view') WHERE id='$id'";
$result4=mysql_query($sql4);
}

// count more value
$addview=$view+1;
$sql5="update $tbl_name set view='$addview' WHERE id='$id'";
$result5=mysql_query($sql5);

mysql_close();
?>

 

the problem is. With add_answer, I am not able to insert an answer. This has worked for the very first question, but all subsequent questions, I am unable to add answers. It keeps telling me ERROR.

 

This is where the live forum is : http://www.thenewme.co.za/forum/main_forum.php

 

Can it be an issue with the field that gets autmatically incremented? i have echo'd the data which get entered in when someone types in an answer, and everything is perfect!

 

Can anyone help me?

[HanneSThEGreaT]

The attached picture is what gets inserted into my questions table. This may be the whole cause of my problem. My database structure is as follows :

 

Table - forum_question

Fields :

 

`question_id` int(11) NOT NULL DEFAULT '0',

  `a_id` int(11) NOT NULL DEFAULT '0',

  `a_name` varchar(65) NOT NULL,

  `a_answer` longtext NOT NULL,

  `a_datetime` varchar(25) NOT NULL,

  PRIMARY KEY (`a_id`)

 

Table - forum_answer

Fields :

 

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `topic` varchar(255) NOT NULL,

  `detail` longtext NOT NULL,

  `name` varchar(65) NOT NULL,

  `datetime` varchar(25) NOT NULL,

  `view` int(11) NOT NULL DEFAULT '0',

  `reply` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

 

I firmly believe it has something to do with the db, but I am not sure, hence this question

 

 

[Buddski]

Instead of just writing ERROR write something a little more useful.

echo "ERROR" , mysql_error();

 

I have also notices that you are setting $tbl_name to 'forum_answer'

Then run this query

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

But according to you database structure forum_answer does not contain any of those columns..

 

[HanneSThEGreaT]

Hi!

 

Thanx for teaching me how to get a proper error message!

 

I got this :

 

ERRORDuplicate entry '1' for key 'PRIMARY'

 

[attachment deleted by admin]

[Buddski]

My guess is this part is failing and returing 1

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

As a suggestion, you could remove this code altogether if you set a_id to auto-incrementing, then you will never have to pass a value to a_id. Why do all the work when MySQL can do it for you.

[HanneSThEGreaT]

Thanx again. I removed that part and was able to enter a reply to a question ( other than the first question, so this is defibnitely progress ).

 

I then tried adding a reply to another question and got the same error message.

 

Does the effects of savinbg into the db take long to update? Should I exit the forum and then return again?

[Buddski]

Inserting data will happen instantaneously (kind of)..

Did you make the a_id auto-incrementing?

[HanneSThEGreaT]

Wow, OK, that works now, thank you. I was stupid not making it so in the first place... You are a genius!

 

I have one little problem now, the reply counter doesn't seem to keep track of where what was posted, so I have to manually edit the replies to show correctly

[HanneSThEGreaT]

OK, This part :

 

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

 

I had to put back, as this is the reply counter.

 

It seems as if the whole problem was that my fields were not set to auto increment.

 

Thank you for all your help!  If anything goes wroing now, I'll come back to this thread, but I'll mark it resolved, for now - hoepfully completely as it seems

[Buddski]

Well, to start with your counting for the replies would never have really worked, you dont need to store them in the database at all!!

Here is a little query you can use on your main_forum.php

SELECT `forum_question`.*, COUNT(`forum_answer`.`a_id`) as `replies` 
FROM `forum_question` 
Left Join `forum_answer` ON `forum_answer`.`question_id` = `forum_question`.`id` 
GROUP BY `forum_question`.`id` 
ORDER BY `forum_question`.`id`

[HanneSThEGreaT]

You are correct, sorry for my wrong doing!

 

Yes, I see now what you mean. I should not have used Max there, as that will give me the latest reply's ID. How could I be so stupid??!!

 

Yes, I do agree with your way of doing things, I should have used Count from the start.

 

Thank you for helping me! It does look like it is now solved

[Buddski]

Always welcome