Page 1 of 1

max 3 rows, why?

Posted: Sun Dec 25, 2011 8:32 am
by Fonis
ok, so i'm trying to make something like a forum, but this is the first time i'm doing any php (and i've never done any programming before). the problem is apparently that i can only have 3 comments/reply's overall, and i have no idea why. please take a look at my code, and explain to me where i'm failing

Code: Select all

<?php

error_reporting(E_ALL); 
ini_set("display_errors", 1);

//open database
$connect = mysql_connect("*******","********","*********") or die("couldn't connect!");
mysql_select_db("**********") or die("couldn't find db");


// get value of id that sent from address bar 
$id=$_GET['id'];

$sql="SELECT * FROM guide WHERE id='$id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>

<table width="900" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td><table width="100%" border="0" cellpadding="3" cellspacing="1" bordercolor="1" bgcolor="#FFFFFF">
<tr>
<td bgcolor="#F8F7F1"><strong><?php echo $rows['guide']; ?></strong></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><?php echo $rows['content']; ?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>By :</strong> <?php echo $rows['username']; ?></td>
</tr>

<tr>
<td bgcolor="#F8F7F1"><strong>Date : </strong><?php echo $rows['date']; ?></td>
</tr>
</table></td>
</tr>
</table>
<BR>

<?php

$sql2="SELECT * FROM answer WHERE guide_id='$id'";
$result2=mysql_query($sql2);

while($rows=mysql_fetch_array($result2)){
?>
<table width="800" border="0" align="center" cellpadding="10" 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"><?php echo $rows['a_id']; ?></td>
</tr>
<tr>
<td width="18%" bgcolor="#F8F7F1"><strong>User</strong></td>
<td width="5%" bgcolor="#F8F7F1">:</td>
<td width="77%" bgcolor="#F8F7F1"><?php echo $rows['a_username']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Comment</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_comment']; ?></td>
</tr>
<tr>
<td bgcolor="#F8F7F1"><strong>Date/Time</strong></td>
<td bgcolor="#F8F7F1">:</td>
<td bgcolor="#F8F7F1"><?php echo $rows['a_date']; ?></td>
</tr>
</table></td>
</tr>
</table><br>

<?php
}

$sql3="SELECT view FROM guide 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 guide(view) VALUES('$view') WHERE id='$id'";
$result4=mysql_query($sql4);
}

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

?>


<BR>
<BR>
<table width="600" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form name="form1" method="post" action="add_answer.php">
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">

<td valign="top"><strong>Comment</strong></td>
<td valign="top">:</td>
<td><textarea name="a_comment" cols="45" rows="3" id="a_comment"></textarea></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="id" type="hidden" value="<? echo $id; ?>"></td>
<td><input type="submit" name="Submit" value="Submit"> <input type="reset" name="Submit2" value="Reset"></td>
</tr>
</table>
</td>
</form>
</tr>
</table>

Code: Select all

CREATE TABLE `answer` (
  `guide_id` int(11) NOT NULL,
  `a_id` int(11) NOT NULL,
  `a_username` varchar(25) NOT NULL,
  `a_comment` longtext NOT NULL,
  `a_date` datetime NOT NULL,
  PRIMARY KEY  (`a_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Re: max 3 rows, why?

Posted: Sun Dec 25, 2011 10:35 am
by Eric!
My guess is there is something going on in add_answer.php that is writing that field to the database.

Re: max 3 rows, why?

Posted: Sun Dec 25, 2011 11:08 am
by Fonis
haha forgot to show that...

add_answer.php

Code: Select all

<?php
error_reporting(E_ALL); 
ini_set("display_errors", 1);

session_start();

if ($_SESSION['username'])
{
//open database
$connect = mysql_connect("*******","*******","**********") or die("couldn't connect!");
mysql_select_db("**********") or die("couldn't find 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 answer WHERE guide_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_comment=$_POST['a_comment']; 
$a_username=$_SESSION['username'];
$date=date("y-m-d H:i:s"); // create date and time 

// Insert answer 
$sql2="INSERT INTO answer(guide_id,a_id,a_username,a_comment,a_date) VALUES('$id','$Max_id','$a_username','$a_comment','$date')";
$result2=mysql_query($sql2);

echo "You succesfully uploaded a comment.<BR>";
echo "<a href='view_guide.php?id=".$id."'>Click to view</a>.";

// If added new answer, add value +1 in reply column 
$sql3="UPDATE guide SET reply='$Max_id' WHERE id='$id'";
$result3=mysql_query($sql3);


}	
else
	die("You must be logged in to see this page");

?>

Re: max 3 rows, why?

Posted: Sun Dec 25, 2011 4:03 pm
by Fonis
can anybody see why this won't work??

Re: max 3 rows, why?

Posted: Tue Dec 27, 2011 11:01 am
by Fonis
?

Re: max 3 rows, why?

Posted: Tue Dec 27, 2011 2:16 pm
by twinedev
Ok, first while developing a script, you should add error output to the mysql_query so you can tell when a query doesn't execute, and example:

Code: Select all

$result4=mysql_query($sql4) or die ('SQL Error: '.mysql_error().'<br>SQL: '.htmlspecialchars($sql4));
This way when you hit this line, you will see that the query is failing due to (if your database is setup right) trying to insert another record with the same PrimaryKey

(You didn't see this was broken, because technically you are doing an UPDATE to increase the value that already exists in the database for that record).

Another safety note, you should never directly use data that a user can manipulate, it should be protected for SQL by using mysql_real_escape_string() and for use in HTML output, htmlspecialchars($variable,ENT_QUOTES) Samples of what not to "trust" are $_POST, $_GET, $_REQUEST, $_COOKIE, $_SERVER['HTTP_USER_AGENT'], $_SERVER['PHP_SELF'], $_SERVER['HTTP_REFERRER'].

Now back to the question asked about, why you can only have 3, I am not seeing what would be causing only a total of 3 items. Can you clarify that you mean that you can only do the original item, and then 2 replies to it, or 3 replies to to question. Also try putting in the error example above for the queries (don't forget to change the variable for the SQL statement to match each one) you may find that it lets you know something had an issue posting (ie, the insert of the 4th item failing for some reason). Also, when you post the 4th item, is one of the other items getting replaced with the new one, or it just doesn't exist in the database).

-Greg

Re: max 3 rows, why?

Posted: Tue Dec 27, 2011 5:25 pm
by Fonis
thank you for the reply twinedev. to specify what i mean with only 3 reply's, is that it will only write a total amount of 3 rows in the mysql database, so i can only have a total maximum of 3 reply's on the whole website.

btw thanks for the advice with the security and that stuff but at the moment i'm not able to test it since i'm working... :(

Re: max 3 rows, why?

Posted: Tue Dec 27, 2011 6:06 pm
by twinedev
When you go to add a 4th item, what do you get? Any error from the added error (the or die(....) code? What values are in the database before you add it? what is in there after you add it?

Re: max 3 rows, why?

Posted: Tue Dec 27, 2011 6:28 pm
by Fonis
when i try to add a fourth item, it says completely the same as if it all worked out well. It even increases the max_id but the reply wont update itself into the database, so it's still the same old 3 replies that can be seen. If i delete all the replies, then i can write 3 new replies, but then it won't write any more till i delete some of the existing.

Re: max 3 rows, why?

Posted: Tue Dec 27, 2011 6:41 pm
by twinedev
so you are doing the following line in add_answer.php

Code: Select all

$result2=mysql_query($sql2) or die ('SQL Error: '.mysql_error().'<br>SQL: '.htmlspecialchars($sql2)); 
It just logically doesn't make sense that this line is issuing an INSERT statement, yet nothing is in the database, which must be the case as you said it is doing the UPDATE statement that is right after it.

Try for debugging echoing out $sql2 to the screen and manually running it on the database.

-Greg

Re: max 3 rows, why?

Posted: Tue Dec 27, 2011 6:49 pm
by Fonis
thanks for the help, i'll try that when i get back home tomorrow night... :)

Re: max 3 rows, why?

Posted: Wed Dec 28, 2011 10:15 am
by Fonis
ok, so i tried it out, and now it writes:

"SQL Error: Duplicate entry '1' for key 1
SQL: INSERT INTO answer(guide_id,a_id,a_username,a_comment,a_date) VALUES('11','1','fonis','læjasdæfkljælkj','11-12-28 16:05:54')"

any idea about what this is?

Re: max 3 rows, why?

Posted: Wed Dec 28, 2011 10:34 am
by Fonis
wow, it's a christmas miracle!! i went to the bathroom, and when i came back the whole thing was working. I've got NO idea what i did, but strangely it's working...

Re: max 3 rows, why?

Posted: Wed Dec 28, 2011 6:05 pm
by Fonis
okay, so new problem :( It will write as many replies as i want to one specific guide/topic, but with all the rest of the guides, it'll only write up to the total maximum of 3 replies. In the case that there's 3 or more replies, it'll write back (as i said):

"SQL Error: Duplicate entry '1' for key 1
SQL: INSERT INTO answer(guide_id,a_id,a_username,a_comment,a_date) VALUES('11','1','fonis','læjasdæfkljælkj','11-12-28 16:05:54')"

Re: max 3 rows, why?

Posted: Wed Dec 28, 2011 6:49 pm
by Fonis
now, i fixed the problem so the topic is now closed.

to help anyone else who also has this problem, the problem is the primary key. It will only accept unique numbers, but since i told it to start all over at every single topic/guide, it wouldn't work. i therefore had to define a new primary key for the add_answer.php