HELP:Duplicate entry '1' for key 'PRIMARY'

[kleb]

Please i ran into this problem help me :

<p>Posted by <a href="">
			<?php 
			if(isset($_GET['id']))
			{
			$tpid=$_GET['id'];
			}
			else
			{
			$tpid=$_POST['id'];
			}
			include"header.php";
			$sql="SELECT*FROM topics WHERE topicsid='$tpid'";
			$result=mysql_query($sql) or die(mysql_error());
			while($row=mysql_fetch_array($result))
			     {
                 		echo"{$row['topics_by']}";

					echo"</a></p>";

			echo"<p>";
                 		echo"{$row['topics_subject']}";

					  echo" <p align='left'><img src='speakout_1.png'/>";
			echo"{$row['topics_content']}";

			echo"<p class='post-footer align-right'>					

				<a href='' class='comments'>";
session_start();
if(isset($_SESSION['views']))
$_SESSION['views']=$_SESSION['views']+1;
else
$_SESSION['views']=1;
echo"Comments:".$_SESSION['views'];
echo"</a>";
				echo"<span class='date'>"; 
                 	
			echo"{$row['topics_date']}";
			}
			?></span>	
			</p>

		</div>


			<h3>Comments:</h3>

				<table>
				<tr class="row-a">
					<td class="first"></td>
					<td><?php
                                 include"header.php";
                                  $sql="SELECT post_content,post_by FROM post WHERE topicsID='$tpid'";
                            $result=mysql_query($sql)or die(mysql_error());
                                while($row=mysql_fetch_array($result))
                             {

                               echo"<strong>{$row['post_by']}</strong>:  {$row['post_content']}"."</br>";
                             }
                                         ?></td>

				</tr>
				</table>
		<?php
include"header.php";
if(isset($_POST['submit']))
{
$comment=mysql_real_escape_string(trim($_POST['comment']));
$name=mysql_real_escape_string(trim($_POST['name']));
$hidden=$_POST['id'];
if($comment!=='' && $name!=='')
{
$ins="INSERT INTO post(topicsID,post_content,post_by)VALUES('$hidden','$comment','$name')";
mysql_query($ins) or die(mysql_error());
}
else
{
echo"you cannot post an empty field";
}
}


?>
			<h3>Post your comments here</h3>
			<form action=''method='post'>
				<textarea name="comment" id="content" style="width:400px;height:50px;background-color:#D0F18F;color:#000000;font:15px/20px cursive;scrollbar-base-color:#638E0D;"></textarea>
				<br />	
				Name:<input type="text"name="name"/> 		
				<input class="button" type="submit"name="submit"value="submit" />
				<input type="hidden"name="id"value='<?php echo "$tpid"; ?>'/>
				</p>		
			</form>	

			<br />				

	</div>					

[Psycho]

Well, you haven't really provided any information. You need to state what is causing the error. What you have tried and what the results were. I *assume* your problem is due to the insert query. It looks like you have a form for updating records - which includes a hidden field for the primary key of the record. But, you are taking that information and trying to run an INSERT query instead of an UPDATE query. If you want to INSERT a new record then you should not provide the primary key value - that should be automatically done by the database if the field is an auto-increment field

[PFMaBiSmAd]

Unfortunately, this is a continuation of an existing thread, not its own thread... 

 

You are allowing and storing multiple comments for any topic. The column you are storing the topic id in should not be a primary key.

[kleb]

although the codes here may look familiar but its a new thread! have being working on my codes and posting problems any time i run into it

 

what i want to do is to be able to display comments made on any topic by the user and i ran into this problem

[Psycho]

Again, you still have not provided the needed information. Is the error being generated by the INSERT statement?

$ins="INSERT INTO post(topicsID,post_content,post_by)VALUES('$hidden','$comment','$name')";

 

If so, then one of those fields is set as a primary key and must be unique. I suspect it is the "topicsID" field. So, if the intent is to allow multiple records associated with the same topic ID then that field should not be a primary or unique field.

[kleb]

The primary key is the topics ID this started when i tried to insert the id from the hidden field into the post table.please note that i joined TOPIC TABLE AND POST TABLE with a common id i named topicsID

[kleb]

please am still new in php. i wanted to insert new records into a column(am working on a comment page)but when i used INSERT it gave me duplicate key error but i was advised to use UPDATE but it also said that i have a syntax error.please how do i use the UPDATE because what i did was i just cleaned the INSERT and wrote UPDATE.

thanks

[AyKay47]

If you post your query we can help you clean up your code, documentation on MySQL's UPDATE syntax can be found here

[kleb]

this is the code:

			<?php
include"header.php";
if(isset($_POST['submit']))
{
$comment=mysql_real_escape_string(trim($_POST['comment']));
$name=mysql_real_escape_string(trim($_POST['name']));
$hidden=$_POST['id'];
if($comment!=='' && $name!=='')
{
$ins="UPDATE post(topicsID,post_content,post_by)VALUES('$hidden','$comment','$name')";
mysql_query($ins) or die(mysql_error());
}
else
{
echo"you cannot post an empty field";
}
}


?>
			<h3>Post your comments here</h3>
			<form action=''method='post'>
				<textarea name="comment" id="content" style="width:400px;height:50px;background-color:#D0F18F;color:#000000;font:15px/20px cursive;scrollbar-base-color:#638E0D;"></textarea>
				<br />	
				Name:<input type="text"name="name"/> 		
				<input class="button" type="submit"name="submit"value="submit" />
				<input type="hidden"name="id"value='<?php echo "$tpid"; ?>'/>
				</p>		
			</form>	

[AyKay47]

$ins="UPDATE post SET topicsID = '$hidden', post_content = '$comment', post_by = '$name'";

 

if you only want to updates certain row(s), add a where clause onto the update query specifying the conditions of the update.

[Adam]

Why are you using UPDATE to INSERT a row? That doesn't make any logical sense. They're named what they are for a reason; INSERT is for inserting rows, and UPDATE is for updating existing rows.

 

Can you post the structure of your database table? Sounds like you might need to add an 'auto_increment' to the ID column, to automatically increment the numeric ID on each new insert.

[kleb]

at akay7 it gave me an error:Duplicate entry '2' for key 'PRIMARY'

[kleb]

@adam this is how my table looks like

<?php
$db_name="****";
$db_user="****";
$db_pass="****";
$con=mysql_connect('localhost',$db_user,$db_pass) or die(mysql_error());
mysql_select_db($db_name);
$user="CREATE TABLE IF NOT EXISTS user( userID int( not null auto_increment, primary key(userID), user_name varchar(50) not null,user_email varchar(20) not null,user_password varchar(30) not null,user_date TIMESTAMP  not null,UNIQUE INDEX email_unique(user_email))";
mysql_query($user);
$topic="CREATE TABLE IF NOT EXISTS topics(topicsID int( not null AUTO_INCREMENT,primary key(topicsID),topics_subject varchar (200) ,topics_content text not null,topics_date TIMESTAMP not null, topics_cat varchar(20) not null, topics_by varchar(100) not null)";
mysql_query($topic) or die(mysql_error());
$post="CREATE TABLE IF NOT EXISTS post(topicsID int( not null AUTO_INCREMENT,primary key(topicsID),post_content text not null,post_by varchar(100) not null)";
mysql_query($post);
$edit="CREATE TABLE IF NOT EXISTS editorial(editorialID int( not null AUTO_INCREMENT,PRIMARY KEY(editorialID),editorial_content text not null,editorial_by varchar(100) not null)";
mysql_query($edit) or die(mysql_error());

?>

[Adam]

Might want to quickly edit your post to remove the password there..

[AyKay47]

i've seen another active thread of yours only minutes ago. My advice would be to wait until one thread is resolved before moving on to another topic. Now, updating (or modifying in general) a PK that already exists will result in an error as this would defeat the purpose of primary keys. If you want to insert a new row into the database with its own PK then use an INSERT, if you want to editing a field(s) in an already existing row use UPDATE, and as Adam said, perhaps if we saw your db structure along with an explanation of your logic we could assist you further.

[Adam]

You have the 'topicsID' set as a primary key (a unique index), when there will multiple posts related to the same topic. Your table structure needs an extra column for the 'postID', which is the auto incremented primary key, and the 'topicID' should be a foreign key pointing to 'topicsID' column in the 'topics' table. Physically declaring the 'topicID' column as a foreign key isn't essential though, and not even possible if you're using the MyISAM engine.

 

Edit: And don't listen again to the guy who told you to use an UPDATE in place of an INSERT.

[trq]

Physically declaring the 'topicID' column as a foreign key isn't essential though, and not even possible if you're using the MyISAM engine

 

Highly recommended though. One thing Iv'e learnt at my latest place of employment is that data integrity is best handled at the database level.

[Adam]

For sure. I just didn't want to complicate this situation any more..

[kleb]

am so confused i dont even understand again what you are all saying :'(

[PFMaBiSmAd]

This is at least the 3rd thread you have started for the same problematic code. If you would stop creating new threads for your 'topic/add a comment' code, it would easier to help you because the information about what it is you are trying to do and what you have changed would already be in the thread. You would not be getting a bunch of guesses and suggestions that don't actually have anything to do with what your code is and what you are trying to do.

[Adam]

The problem is that you're trying to insert the topic ID into a primary key column, which only allows unique values. The solution is to add an additional column to the table - called "postID" if you want to keep the naming consistent - that will be the auto incremented primary key instead. That will act as a unique identifier for each row in the posts table.

 

Remove the primary key index and the auto_increment from the topcID column, and continue inserting the topic ID as you're trying to do. Your INSERT statement doesn't need to change, as the postID column will automatically insert the next number in the sequence without you needing to insert anything into it.

 

If the table is empty, rather than trying to alter it I would simply drop it and recreate it with the right structure.

[Psycho]

The primary key is the topics ID this started when i tried to insert the id from the hidden field into the post table.please note that i joined TOPIC TABLE AND POST TABLE with a common id i named topicsID

You can't have two records in the same table with the same value in a field that is the primary ID or set as a unique value. Your database structure is flawed. I would suggest the following.

 

1. Add a new field called something such as parent_topic_id.

2. Let the topicID be auto-assigned for ALL posts - even ones that are in response to another post

3. For new, original, posts set the value of parent_topic_id to 0

4. For posts that are in response to another post set the parent_topic_id to the id of the parent post

 

Then if you want to query a specific parent post and all the responses you could use a query such as

SELECT *
FROM topics
WHERE topicsid = '$tpid'
   OR parent_topic_id = '$tpid'
ORDER BY topic_date