update database field....

pointsplat

PLEASE PLEASE PLEASE READ!! ITS LONG BUT I REALLY NEED THE HELP!!

Hey guys heres my problem, I have a sectionof my site for rating teachers and in this section you can leave comments about the teachers. I have everything put together the only problem is the comment leaving part is a little flaky, sometimes it works and sometimes it doesnt. The code is listed below the first being the form which the people submit their info through and the second being the code that updates the database table. For an example of one of the users I cant add another comment to for a reason unbeknownst to me go to http://www.duparties.com/profrate/view_prof.php?prof=2 and try leaving a comment (I will erase all these eventually so feel free to leave aas many as you want to test if they work).

The PHP code basically takes the inputs and forms them like this in the comments section so they can later be decoded into individual comments.

(--)[--]name1[--]email1[--]course1[--]date1[--]comment1--

For most of the people I attempted leaving comments for it works perfectly but for the Prof. Zoski and a couple of the others when I try making a submission it goes back to the profile view page but did not make the update to the database.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Professor Rater - Add Professor</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<LINK REL="stylesheet" TYPE="text/css" HREF="style.css">
</head>

<body>
<div align="center"> 
  <table border="5" cellpadding="0" cellspacing="0" bordercolor="#1F0168" width="350" id="AutoNumber3">
    <tr> 
      <td width="100%"> <p align="center"><b><font face="Arial" size="5">Professor 
          Rater</font></b></td>
    </tr>
  </table>
  <p><a href="add_prof.htm">Add Professor</a> --- <a href="index.php">View Listings</a></p>
  <table border="3" bordercolor="#000099">
    <tr align="center"> 
      <td width="361" nowrap bgcolor="#000099"><font color="#FFFFFF">Add a Professor</font></td>
    </tr>
    <tr align="center">
      <td nowrap bgcolor="#CCCCCC">
	  <table width="100%" border="0" bordercolor="#666666">
          <form action="add_prof.php" method="POST">
          <tr> 
            <td width="24%" align="right" nowrap><strong>Your Name:</strong></td>
            <td width="76%"><input type="text" name="user_name" size="30"></td>
          </tr>
          <tr> 
            <td align="right" nowrap><strong>Your Email:</strong></td>
            <td><input type="text" name="user_email" size="30"></td>
          </tr>
          <tr> 
            <td align="right" nowrap>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr> 
            <td align="right" nowrap><strong>Professor Name:</strong></td>
            <td><input type="text" name="prof_name" size="30"></td>
          </tr>
          <tr> 
            <td align="right" nowrap><strong>Department:</strong></td>
            <td><select name="department">
                <option>Accounting &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;</option>
                <option>Architecture</option>
                <option>College of Business</option>
                <option>Chemical Engineering</option>
		<option>Chemistry</option>
                <option>College of IST</option>
                <option>Computer Science</option>
		<option>Culture and Communication</option>
		<option>Decision Sciences</option>
                <option>Economics</option>
		<option>English and Philosophy</option>
                <option>Electrical/Computer Eng.</option>
		<option>Fashion Design</option>
                <option>Finance</option>
                <option>Health Professions</option>
                <option>International Studies</option>
		<option>IST</option>
                <option>Legal Studies</option>
                <option>Management</option>
                <option>Materials Engineering</option>
                <option>Marketing</option>
                <option>Mathematics</option>
                <option>Mechnical Engineering</option>
                <option>Media Arts &amp; Design</option>
                <option>Medicine</option>
                <option>Night Courses</option>
		<option>Performance Arts</option>
                <option>Physics</option>
                <option>Psychology</option>
                <option>Sociology</option>
		<option>Visual Studies</option>
              </select></td>
          </tr>
          <tr> 
            <td align="right" nowrap><strong>Website:</strong></td>
            <td>
            <input name="website" type="text" value="Dont edit if none..." size="30"></td>
          </tr>
          <tr> 
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr> 
            <td align="right" valign="top" nowrap><strong>Comments:</strong></td>
            <td><textarea name="comments" cols="23" rows="4" wrap="VIRTUAL">Add any comments you have for us here...</textarea></td>
          </tr>
          <tr>
            <td align="right" valign="top" nowrap>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td align="center" valign="top" nowrap colspan="2">
            <input type="submit" name="Submit" value="Submit Professor!"></td></form>
          </tr>
        </table></td>
    </tr>
  </table>

</div>
</body>
</html>

<?php

$prof = $_POST['prof'];
$name = strip_tags($_POST['name']);
$email = strip_tags($_POST['email']);
$course = strip_tags($_POST['course']);
$comment = strip_tags($_POST['comment'], '<a><b><i><u>');
$comment = str_replace("(--)","",$comment);
$comment = str_replace("[--]","",$comment);

if($name!="" && $comment!="")
{
	$db = mysql_connect("localhost", "username", "password");
	mysql_select_db("pictures",$db);
	$profinfo = mysql_query("SELECT * FROM teachers WHERE prof_id='".$prof."'",$db);
	$profrow = mysql_fetch_row($profinfo);
	if($profrow[9]=="")
		$ammendstring = "(--)";
	$origcomments = $profrow[9];

	if($email=="dont edit for anonymous...")
		$email = "";

	$hoursdiff = 3;
	$hoursdiff = $hoursdiff * 3600;
	$timestamp = time();
	$timestamp = $timestamp + $hoursdiff - 720;
	$todaydate = date("m/d/Y g:iA", $timestamp);

	$ammendstring .= "[--]".$name."[--]".$email."[--]".$course."[--]".$todaydate."[--]".$comment."[--](--)";
	$finishedstring = $origcomments.$ammendstring;


	$updateinfo = mysql_query("UPDATE teachers SET comments=$finishedstring WHERE prof_id='".$prof."'",$db);

	mysql_close($db);


echo "<head>
<script language='javascript'>
alert('Thank you for submitting your comments, we're sending you back to their profile!');
</script>
<META http-equiv='refresh' content='0; url=http://www.duparties.com/profrate/view_prof.php?prof=".$prof."'> 
</head>
";

}

else
{
	echo "<head>
	<META http-equiv='refresh' content='0; url=http://www.duparties.com/profrate/add_comment.php?prof=".$prof."&error=An_error_occurred_please_try_again!'> 
	</head>
	";
}

?>

Stoned_Gecko

Depending on how many comments there is for a given professor, you might exceed the maximum size of the DB field. I noticed you're doing everything with a single table. Is there a reason for that? Can you use more than one table? If a single professor is only part of a single department, then you can have 2 tables, one to hold the teacher information, and the other one to hold the comments. Here are the fields:

Table teachers:
teacher_id (AUTO_INCREMENT)
teacher_name
department
email
website

Table comments:
comment_id (AUTO_INCREMENT)
teacher_id
comment
guest_name
guest_email

Feel free to add the fields as you see fit. Here's how it's going to work:

A user will fill out the form
The script will run a query to get the teacher_id from the teachers table. Something like this: "SELECT teacher_id FROM teachers WHERE teacher_name='$teacher_name'"
If no teacher with that name is found, you can either pop-up an error message or have the script add a teacher and assign an unique ID to the row. You can use the AUTO_INCREMENT option when creating the table. If you choose the error message, then you gotta have all the teachers entered in advance.
Insert the comment into the comments table: INSERT INTO comments (comment_id,teacher_id,comment,guest_name,guest_email) VALUES (NULL,$teacher_id,'$comment','$guest_name','$guest_email')

If you can see the logic, then you can guess how you get the comment:

Get the teacher name.
Look up the teacher_id in the teachers table.
Select all the comments from the comments table with that teacher_id: "SELECT * FROM comments WHERE teacher_id=$teacher_id"

Hope this helps.

pointsplat

Wow that clarifies alot, there is a reason I have it all in one table, my brain was fried between work and everything else going on I couldn't figure out the "missing link" for allowing comments to be in a seperate table. Forgot about the unique professor id :o) That will make things alot simpler thanks for the info!!!

God I love this site always people around to do my thinking for me hehe.

pointsplat

I dont know why but now this code wont work I made a test table to test it and the insert code wont work which may explain why the update code wasnt working.

$db = mysql_connect("localhost", "username", "password");

$result = mysql_query("INSERT INTO test (test1) VALUES ('hola')", $db);

am I missing something??? this code is perfect is it not? OY!!

Stoned_Gecko

Don't forget mysql_select_db() 😉

And if that's not it, then take a look at the error that's produced (mysql_error() ) and see what's up.

pointsplat

yupp I just realized that too, im so used to having connect db stuff in a centralized file but giving the few number of pages for this project I decided to go in-line. Forgetting to copy and paste it all 😉

Stoned_Gecko

Copy paste? Require it dude!! Have a db.php that connects to the database and selects the database and just require that file at the top of every script. Makes it so much easier. 🙂