CODEWITHSUNDEEP
phpmysqldatabase
DominusMors
DominusMors

Reputation: 129

Sql Insert into with duplicate key

Ok so, im still a beginner in databases. i have this code 

$sql="INSERT INTO complaints_members(password, complaint) VALUES ('$mypassword','$submit') ON DUPLICATE KEY UPDATE complaint='$submit' ; ";

This simply updates my complaint in the existing entry. How can i insert a new entry with the same key, instead of updating the old one?

Im thinking of it like this.

1st entry is like

Password : 123

Complaint : abc

2nd would be like

Password : 123

Complaint : def

Im not very familiar with the terms of SQL, so i'm sorry in advance, and thanks for your time.

EDIT: This is how the tables are

enter image description here

Upvotes: 0

Views: 266

Answers (3)

prodigitalson
prodigitalson

Reputation: 60413

For future reference when someone asks for your table structure its better to post the text from SHOW CREATE TABLE table_name instead of an image from a visual editor.

That said the problem is that your primary key is the password field. You need to add primary keys to both tabled than can be uniquely identified and then you need to link them.

Your table structure should be more like this:

CREATE TABLE `register` (
   `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   `username` varchar(255) NOT NULL,
   `password` varchar(255) NOT NULL,
   `email` varchar(255) NOT NULL,
   `number` INTEGER(255),
   `address` varchar(255),
   PRIMARY KEY (`id`),
   -- I assume that email and username should always be unique here
   UNIQUE KEY (`email`)
   UNIQUE KEY (`username`)
);

CREATE TABLE `complaints_members`
   `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   `complaint` VARCHAR(255),
   `password` varchar(255),
   `member_id` INTEGER UNSIGNED NOT NULL,
   PRIMARY KEY (`id`),
   KEY (`member_id`),
   CONSTRAINT `complaints_members_register` FOREIGN KEY (`member_id`) REFERENCES `register` (`id`) ON DELETE CASCADE
);

So now to create a new member complaint your SQL would look like

INSERT INTO complaints_members(member_id, password, complaint) VALUES (?, ?, ?)

And to get all complaints for a member:

SELECT c.* 
FROM compalaints_members c
WHERE c.member_id = ?

Upvotes: 0

Tim Southard
Tim Southard

Reputation: 614

One option is to make password and complaint a composite primary key

Upvotes: 0

rick6
rick6

Reputation: 467

You can't have duplicate primary keys in a database. This is intentional.

Instead, consider re-designing your database so that each complaint has a unique ID (AKA a Primary Key). You should set it as something like complaint_id or simply id, and make it a PK (Primary Key) and AI (Auto-Increment). That way, when you do inserts you won't have to worry about updating the same row.

Upvotes: 5

Related Questions