I recently started learning PHP, and I don’t know everything about MySQL. Now I am writing code, performing the task I set myself for self-study. And now there is a problem in the code - you need to make the most optimal comparison of values ​​or something else, so that duplicates are not recorded. I provide the code in text form and a screenshot for easy reading.

PS: I read about INSERT IGNORE but somehow its logic is not clear, please explain it clearly. Or suggest a better option. When I ran the code with if - (logic) - checking the link in the database (because the link is unique and if it is not in the database, it means you can write) the code uploaded 700+ records to the database (did not check for duplicates), but according to the idea, he had to fill in only 10 (since only 10 articles on one page).

question

Himself a piece of code:

/*запись данных в базу*/ $link_test=mysqli_query($bd, "SELECT * FROM `php_dns`.`habra_post`"); if(!$link_test){ $bd-> query("INSERT IGNORE INTO habra_post SET title='$title1', text_body='$text1', reg_date='$date1', full_text='$full_text', next_date='$date_nextd', link='$html_in'"); if($bd==false) { // echo "<br>Oll OK"; // } // else // { echo mysqli_error(); } }}}
  • 3
    If repetitions are not allowed in the table field, the UNIQUE parameter can be assigned to this field. - lolbas
  • As an option - to make a field in the database with the property UNIQUE. (see stackoverflow.com/questions/5038040/… ) - DimXenon
  • I read about unique that it is extremely undesirable as a solution to this problem. - ALPHA
  • 3
    Extremely undesirable? If the specified data is unique in your area, the unique constraint is necessary to avoid integrity violations with parallel changes. - D-side

1 answer 1

INSERT IGNORE is relevant only if you have a unique or primary key in the table if it (s) is not there, then this expression is meaningless. In this case, you first need to check the existence of a record in the table, and then make a record. That is, first check the existence of the record

 SELECT * FROM `php_dns`.`habra_post` WHERE title='$title1' AND text_body='$text1' AND reg_date='$date1' AND full_text='$full_text' AND next_date='$date_nextd' AND link='$html_in'

If the record is found then ignore it. If not, then create a new one.

But this record is not very optimal.

 SELECT * FROM `php_dns`.`habra_post` WHERE title='$title1' AND text_body='$text1' AND reg_date='$date1' AND full_text='$full_text' AND next_date='$date_nextd' AND link='$html_in'

You need to determine which records should not be duplicated and only check them, and it is better to create unique keys immediately. If you do not want to do this, then optimize or properly organize the table - for example, full_text if it is too cumbersome, then create another column with md5 full_text and compare them, but it is better to transfer it to another table. Or you have a link - if this is a link to a page, then for each post it is unique - you can only check it - that is, if it is, then there is a record.