Do not write if such a record is already in the database.

Variables with data to be recorded in the database: u_id, rec_id , voice

User table:

 'user_id' , 'recipe_id' , 'voice'
  • what prevents to check through select? - Alex
  • show the code on the example please - Kill Noise

3 answers 3

Create an index for these fields and you will be happy.

 ALTER TABLE `user` ADD UNIQUE `INDEX_NAME`(`user_id`, `recipe_id`, `voice`);

We check that there is such a record and if it is not, then add it.

 $result = mysql_query("SELECT * FROM user WHERE user_id='$user_id' AND recipe_id='$recipe_id' AND voice='$voice'"); $num_rows = mysql_num_rows($result); if (!$num_rows) { //Инсертим свои данные }
  • So there will be an error when trying to add. - alias
  • one
    If someone else can intervene, you still have the possibility of overtaking (race condition). It is necessary to check, yes, through select. But maybe from another client try to add this, and therefore an error when trying to add is correct. - Netch
  • The mysql_query extension is outdated and is not supported in new versions of php . recommend using mysqli or pdo . - Alex

You can index fields using INSERT IGNORE instead of a simple INSERT, in which case non-unique key errors will be suppressed and only unique entries inserted into the table. But it’s safer to use a script like:

 INSERT ... ON DUPLICATE KEY UPDATE id = id ;

I advise you to read: https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update and https://dba.stackexchange.com/questions/38817/on-duplicate-key -do-nothing

UPD: Expand the thought about the insecurity of error suppression.

Suppose there is a table:

 CREATE TABLE `tmp_test` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `event_id` INT(11) NOT NULL, `type_id` INT(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `indx` (`event_id`, `type_id`) );

Note the specified NOT NULL constraints.

Add the line:

 INSERT INTO `tmp_test` (`event_id`,`type_id`) VALUES (1,1);

Now varinat with INSERT IGNORE:

 INSERT IGNORE INTO `tmp_test` (`event_id`,`type_id`) VALUES (1,1);

We get a warning, the line is not inserted. In principle, this can be stopped - I agree with the user @Alex, this option may be suitable for a number of applied tasks.

The same result, we get trail. request:

 INSERT INTO `tmp_test` (`event_id`,`type_id`) VALUES (NULL,1) ON DUPLICATE KEY UPDATE id=id;

There are no errors, no warnings, the line is not inserted - this option also satisfies the conditions of the problem.

The difference between these options is in the following query:

 INSERT IGNORE INTO `tmp_test` (`event_id`,`type_id`) VALUES (NULL,1);

We receive a warning and the row is inserted, despite the NOT NULL constraint specified when creating the table. There are a number of applications where this behavior can lead to a violation of the logic of the application and data storage.

And request:

 INSERT INTO `tmp_test` (`event_id`,`type_id`) VALUES (1,NULL) ON DUPLICATE KEY UPDATE id=id;

Return error:

 (1048): column `type_id` cannot be null

The logic is not broken, the data are integers. Sometimes it is better to interrupt the operation of the application than to allow it to work in an uncertain state (or to handle an exception, return the application to a certain state and continue the execution of the program).

The author of the question can now decide which option is better suited for his case.

  • Why update the data if it is not required? - Alex
  • Read the marked answer at: linkoverflow.com/questions/548541/… . IGNORE suppresses errors, which is not always safe. - Igor Karpenko
  • in the course, there will be warnings instead of errors, but everything depends on the conditions of the problem - this was what was meant in the first comment. - Alex
  • It cannot, but it will be suitable at least to solve this problem =) a true remark on the insertion of null with insert ignore . - Alex

It is possible through select, and it is possible shorter:

 INSERT IGNORE INTO `User` (`user_id` , `recipe_id` , `voice`) VALUES ('$user_id', '$recipe_id' , '$voice')

This approach is possible if you have a unique key for any field.

You can check whether a record has been inserted or not with mysql_affected_rows .

UPD

As correctly noted by @IgorKarpenko, this request has a feature, namely:

 INSERT IGNORE INTO `tmp_test` (`event_id`,`type_id`) VALUES (NULL,1);

The string will be added (in this case with the value event_id 0 ), even if the column event_id NOT NULL , which can lead to negative consequences. Therefore, you should not forget about this feature when building queries.