How to write information through PHP code to the MySQL database without duplicates?
I recently started learning PHP, and I'm not familiar with MySQL at all. Now I'm writing code, performing a task assigned to myself for self-learning purposes. And now there is a problem in the code - you need to do the most optimal reconciliation of values or something else, so that duplicates are not recorded. Here is the code in text form and a screenshot, for easy reading.
P. S: 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, then you can write it), the code filled in the database 700+ records (I did not check for duplicates), and according to the idea, it should have filled in only 10 (because there are only 10 articles on one page).
The piece of code itself:
/*запись данных в базу*/
$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();
}
}}}
1 answers
INSERT IGNORE is relevant only if you have a unique or primary key in the table, but if it (them) is not present, then this expression is meaningless. In this case, you need to first check the existence of an entry 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 too not 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 ones it is the records that should not be duplicated and only check them, and it is better to immediately create unique keys. If you don't want to do this, then optimize or organize the table correctly - for example, full_text if it is too bulky, then create another column with md5 full_text and compare them, and it is better to move it to another table altogether. Or here you have 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 there, then there is a record.