Creating Unique Key in MySQL table referring to date

Question

Greetings to all. Newbie here. Question on preventing duplicated entry in my simple web form.

My table record user input from a web form, and distinguished by date e.g. DATE(). How to prevent user with the same name to enter information twice in a single date, e.g. same username cannot be entered twice in the same date, but can be entered at other date?

Hope someone can advice me on this. Thanks is advance.

Answer 1

Your table should have these:

create table tablename (
...
user_id bigint, -- or whatever
date_created date,
unique key(user_id, date_created)
...
);

Answer 2

Create a unique key on the user and date column

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Answer 3

You can simple create a composite primary key. For your case this means that your primary key must consists of a date field as well as the username field.

Answer 4

In several ways.

First, you can create index on your table. (i'm using simple table as an example).

CREATE TABLE `test` (
`id` INT NOT NULL ,
`name` VARCHAR( 255 ) NOT NULL ,
`date` DATE NOT NULL ,
PRIMARY KEY ( `id` ) 
) ENGINE = MYISAM;

ALTER TABLE `test` ADD UNIQUE (
`name` ,
`date` 
);

This is MySQL way.

You also should make checks in PHP ,although you can do it when inserting (MySQL will return error and you can check it). But you can make additional SELECT before inserting (SELECT * from test WHERE name=USER AND date=DATE) and check record count. If it's more than 0, you show error.

When saving, you seldom should worry about one additional SQL. If you should, just check MySQL statement for errors (MySQL way :)).