Noob PHP problem

[Alexander]

Hey folks,

I have a problem.

I'm editing a forum skeleton (very basic), and I keep coming up with a PHP error when I try to post a reply.

Here is the reply code:

$tbl_name2="forum_replies"; // Switch to replies table
          $sql2="SELECT * FROM $tbl_name2 WHERE thread_id='$id'";
          $result2=MYSQL_QUERY($sql2);
          WHILE($rows=MYSQL_FETCH_ARRAY($result2)){
               ECHO "<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\">".$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\">".$rows['a_name']."</td></tr><tr><td bgcolor=\"#F8F7F1\"><strong>Email</strong></td><td bgcolor=\"#F8F7F1\">:</td><td bgcolor=\"#F8F7F1\">".$rows['a_email']."</td></tr><tr><td bgcolor=\"#F8F7F1\"><strong>Answer</strong></td><td bgcolor=\"#F8F7F1\">:</td><td bgcolor=\"#F8F7F1\">".$rows['a_answer']."</td></tr><tr><td bgcolor=\"#F8F7F1\"><strong>Date/Time</strong></td><td bgcolor=\"#F8F7F1\">:</td><td bgcolor=\"#F8F7F1\">".$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);
		
		 ECHO "<BR><table width=\"400\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"1\" bgcolor=\"#CCCCCC\"><tr><form name=\"form1\" method=\"post\" action=\"".$PHP_SELF."?type=reply\"><td><table width=\"100%\" border=\"0\" cellpadding=\"3\" cellspacing=\"1\" bgcolor=\"#FFFFFF\"><tr><td width=\"18%\"><strong>Name</strong></td><td width=\"3%\">:</td><td width=\"79%\"><input name=\"a_name\" type=\"text\" id=\"a_name\" size=\"45\"></td></tr><tr><td><strong>Email</strong></td><td>:</td><td><input name=\"a_email\" type=\"text\" id=\"a_email\" size=\"45\"></td></tr><tr><td valign=\"top\"><strong>Answer</strong></td><td valign=\"top\">:</td><td><textarea name=\"a_answer\" cols=\"45\" rows=\"3\" id=\"a_answer\"></textarea></td></tr><tr><td>&nbsp;</td><td><input name=\"id\" type=\"hidden\" value=\"".$id."\"></td><td><input type=\"submit\" name=\"Submit\" value=\"Submit\"> <input type=\"reset\" name=\"Submit2\" value=\"Reset\"></td></tr></table></td></form></tr></table>";
     }ELSEIF (ISSET($_GET['type']) && $_GET['type'] == "reply") {
          // 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_name2 WHERE thread_id=$id";
          $result=MYSQL_QUERY($sql) or die(mysql_error()); 
          $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(thread_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='".$PHP_SELF."?type=view&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";
          }

I get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE thread_id=13' at line 1

I believe the error is referring to this line:

$sql="SELECT MAX(a_id) AS Maxa_id FROM $tbl_name2 WHERE thread_id=$id";

I see no syntax error there. I'm sorry, I'm a noob.

Please assist!

Thanks,

~Al

[egg82]

At 9/11/12 04:53 PM, Alexander wrote: Please assist!

Thanks,

~Al

You actually have a few issues i'd like to address here.

1. The code looks a bit spaghetti-ish. Are you using classes and functions? The "$result5" gave it away.
2. You're using deprecated mysql functions. Use the PDO class for handling database connections.
3. You're not sanitizing anything going into the database. That is very, very, very bad.
4. You're not using backquotes (`) for table and column names. This can be a problem if you have, say, a table named "select" or even "awesome table name" - it'll confuse MySQL.
5. Don't ever echo mysql's errors to the client. For one, it looks god-awful and confuses the hell out of the user, but more importantly there are injection programs out there that rely on mysql errors. Don't make breaking in easy.

this isn't really an issue, but the way you capitalize your PHP keywords bugs me. I look at it and see QBASIC >.>

as for "SELECT AS" - i've never needed to use the AS keyword. Is there some big special thing that i'm missing, or can I just go about what i've been doing and name my table's fields normally? ("SELECT `user`, `pass` FROM `accounts`" seems pretty straightforward to me)

here is a simplistic "SELECT AS" statement.
I don't see anything immediately wrong with the syntax. Have you tried echoing the query you're using? There's a good chance that "$tbl_name2" is blank.

[egg82]

have a PDO :3

class Connect{
	//vars
	public $masterLink = null;
	
	//constructor
	public function Connect() {
		$this->masterConnect();
	}
	
	//public
	public function masterDisconnect() {
		$this->masterLink = null;
	}
	
	public function insert($query) {
		$result = $this->masterLink->prepare($query);
		$result->execute();
		
		return $result;
	}
	public function update($query) {
		$result = $this->masterLink->prepare($query);
		$result->execute();
		
		return $result;
	}
	public function delete($query) {
		$result = $this->masterLink->exec($query);
		
		return $result;
	}
	public function select($query) {
		$result = $this->masterLink->query($query);
		if(!$result){
			return false;
		}else{
			$array = array();
			while($row = $result->fetch()){
				array_push($array, $row);
			}
			
			return $array;
		}
	}
	
	public function san($string) {
		return $this->masterLink->quote($string);
	}
	
	//private
	private function masterConnect() {
		try{
			$this->masterLink = new PDO("mysql:host=yourDBHost;dbname=yourDBName", yourDBUser, yourDBPass);
		}catch(PDOException $e){
			echo('Something bad happened :(');
			//$e->getMessage()
			//the above commented code is the PDO equivalent of mysql_error()
			exit();
		}
	}
}

[Alexander]

Well, to be honest, I can't answer most of those questions.

As I said, I didn't code this, I'm just editing it to try and learn PHP + MySQL for my own knowledge. :)

Unfortunately, the reply function has NEVER worked, even when the code was unedited. So that's a bummer.

Here's the full code, so you know what I'm working with.

Here's the website, so you can see what I'm working with.

Thanks!

~Al

[Alexander]

Also, as a little post script, the way this entire forum is written bugs me. Everything about it. I'm not saying it's done correctly by any means, but I'm just trying to make it work so I can figure some stuff out.

[egg82]

i'm waiting for Diki or liljim to come in and squash me xD
I honestly learned PHP's classes and PDO not 4 hours ago.

I hope the DB's password isn't "clickhere1" or is just temporary.

anyway, here's some tips on that code (so you don't end up learning the wrong way)

I'm not sure why there's quotes around every damn variable.

MYSQL_CONNECT($host, "$username", "$password")or DIE("cannot connect");

should be

MYSQL_CONNECT($host, $username, $password)or DIE("cannot connect");

to make life easier.

also, the whole thing's very insecure. I could just go to "http://www.shitstorm.co.nf/?type=setup" and get a nice error because the table already exists.

the main difference between GET and POST:
GET comes from the url (?type=setup)
POST comes from a form of some sort (<form action="./" method="post">)

When would you want to use GET?
when you want to allow your users to be able to bookmark or save the page it directs to in some way.
ie. a forum thread.

When would you want to use POST?
when you want to do the same thing as GET, but don't want the user bookmarking or saving the page.
ie. posting in a forum thread. Every time they re-visit that page, they make a new post with the same content.

ECHO "<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\">".$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\">".$rows['a_name']."</td></tr><tr><td bgcolor=\"#F8F7F1\"><strong>Email</strong></td><td bgcolor=\"#F8F7F1\">:</td><td bgcolor=\"#F8F7F1\">".$rows['a_email']."</td></tr><tr><td bgcolor=\"#F8F7F1\"><strong>Answer</strong></td><td bgcolor=\"#F8F7F1\">:</td><td bgcolor=\"#F8F7F1\">".$rows['a_answer']."</td></tr><tr><td bgcolor=\"#F8F7F1\"><strong>Date/Time</strong></td><td bgcolor=\"#F8F7F1\">:</td><td bgcolor=\"#F8F7F1\">".$rows['a_datetime']."</td></tr></table></td></tr></table><br>";

that's disastrous. Avoid doing that. Human readability comes before anything.

[egg82]

At 9/11/12 06:01 PM, Alexander wrote: Also, as a little post script, the way this entire forum is written bugs me. Everything about it. I'm not saying it's done correctly by any means, but I'm just trying to make it work so I can figure some stuff out.

good :P

what are you trying to figure out? Maybe we can tell you about it without having to dig around code.

[Alexander]

I see!

Well, honestly I was trying to make this forum "work" to see if I could.

Do you have an instant message service I could message you on by any chance? I'd very much love to chat and learn about PHP.

[egg82]

At 9/11/12 06:29 PM, Alexander wrote: Well, honestly I was trying to make this forum "work" to see if I could.

Oh, well in that case you'd need to completely re-write it anyway xD

Do you have an instant message service I could message you on by any chance? I'd very much love to chat and learn about PHP.

sure, just PM me. I've got that mailbox bug saying I have 2 unread messages anyway.