Check if SQL entry exists before adding to database from array

Question

Values from the array $sql are being added to the database $safe_id. $sql gets its values from the array $list which contains crawled dynamic title and price information from a website. Here is some example data from $list:

[0]=> array(2) {
    ["title"]=> string(53) "Axe Heaven Miniatuur gitaar | Vintage Sunburst Finish"
    ["price"]=> string(10) "€ 31,50"}
[1]=> array(2) {
    ["title"]=> string(59) "Axe Heaven Miniatuur gitaar | Neil Young Vintage Distressed"
    ["price"]=> string(10) "€ 31,50" }

Adding to the database works. However, every time the script is run all data is added without a check for duplicates, resulting in a lot of duplicate entries. I'm trying to check if the data exists in the table with the variable $exists. The goal is to only add the data to the table when it does not exist. Can anyone shed some light on this?

 $sql = array();

 foreach($list as $row) {

   $title = mysqli_real_escape_string($con,$row['title']);
   $price = mysqli_real_escape_string($con,$row['price']);

     $exists = mysqli_query($con, "SELECT title FROM $safe_id WHERE title = '$title'");
     $doesexists = $exists->fetch_object()->title;

       if(!$doesexists) {
         $sql[] = '("'.$title.'" , "'.$price.'")';
       }
 }

mysqli_query($con, "INSERT INTO $safe_id (title, price) VALUES ".implode(',', $sql));

Try adding unique key reference https://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html — Guillermo Andres Fuentes Moral, Jul 31 '17 at 21:30
Possible duplicate of [Best way to avoid duplicate entry into mysql database](https://stackoverflow.com/questions/2219786/best-way-to-avoid-duplicate-entry-into-mysql-database) — Don't Panic, Jul 31 '17 at 21:31

score 0 · Answer 1 · answered Jul 31 '17 at 21:33

0

Create a primary/unique key in the DB and use the INSERT ... ON DUPLICATE KEY UPDATE OR INSERT IGNORE INTO TABLE syntax:

In phpmyadmin create a unique index on column title
In your php script, use the query that updates the existing record with a new price or just ignore the Insert statement completely

answered Jul 31 '17 at 21:33

Cagy79

1,610
1
19
25

1

phpmyadmin is not required – Jul 31 '17 at 21:35
I agree completely. But easiest to do when not being an 'experienced' mysql user. – Cagy79 Jul 31 '17 at 21:37

Check if SQL entry exists before adding to database from array

1 Answers1