Geek Answers Handbook

Creating a unique key in a MySQL table with a date reference

The question is about preventing duplicate entries in my simple web form.

My table records user input from a web form and differs by date, such as DATE (). How to prevent a user with the same name from entering information twice for the same date, for example, the same user name cannot be entered twice on the same date, but can be entered on a different date?

+7
sql php mysql
source share
4 answers

Your table should have the following values:

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

You can simply create a composite primary key. For your case, this means that your primary key must consist of a date field, as well as a username field.

+1
source share

in several ways.

First you can create an index in your table. (as an example, I use just a table).

 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 the MySQL way.

You should also do checks in PHP, although you can do this on insert (MySQL will return an error, and you can check it). But you can do an extra SELECT before inserting ( SELECT * from test WHERE name=USER AND date=DATE ) and check the number of records. If it is greater than 0, you are showing an error.

When saving, you rarely have to worry about one additional SQL. If you need to, just check the MySQL statement for errors (MySQL path :)).

+1
source share

Create a unique key in the user and date column

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

0
source share

More articles:


All Articles