Mysql if data exists then do not insert it again

Question

I use this code below for getting data from a different website by using PHP curl function.everything is working fine but I am getting duplicate data into my database when the website will reload or refresh.
duplicate data screenshot

does there have any why based on this code below to stop inserting duplicate data on reload if the data already exist in the Mysql database? I search on google about this issue but don't get any useful information.

if(isset($_POST['url'],$_POST['theme'])){

    $db = new Mysqli("localhost" , "iamniloy_wp###" , "(5uSE6[3OP" , "iamniloy_#@");

    $url = $db->real_escape_string($_POST['url']);
    $theme = $db->real_escape_string($_POST['theme']);


    $query = "INSERT INTO twist_data ( url,theme ) VALUES ('$url','$theme')";
    $db->query($query);

    }

You can use a Try Catch, and just ignore the duplicate ID exception, provided you have a unique field. — ArtisticPhoenix, Feb 16 '17 at 00:43
What about the data makes it duplicate? The url, the theme, or both? — Tim Biegeleisen, Feb 16 '17 at 00:44

Tim Biegeleisen · Accepted Answer · 2017-02-16T00:59:40.680

1

If you can't add a unique index for whatever reason, you can use EXISTS to check for an already existing record:

INSERT INTO twist_data (url, theme)
SELECT * FROM (SELECT '$url','$theme') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM twist_data WHERE url = '$url' AND theme = '$theme'
)

edited Feb 16 '17 at 00:59

answered Feb 16 '17 at 00:47

Tim Biegeleisen

502,043
27
286
360

`LIMIT 1` ? :-) – Paul Spiegel Feb 16 '17 at 00:59
@PaulSpiegel Who put that there!? – Tim Biegeleisen Feb 16 '17 at 01:00
I heard of some goblins on SO ;-) – Paul Spiegel Feb 16 '17 at 01:02
Thanks @TimBiegeleisen . – Niloy Feb 16 '17 at 01:05

score 0 · Answer 2 · answered Feb 16 '17 at 00:42

0

You should change the structure of your table to have a unique index on the relevant columns.

For example:

ALTER TABLE `twist_data` ADD UNIQUE INDEX `url` (`url`)

This way you will not able to insert the same url to your table.

If you need the unique to be on both the url and the theme you can use:

ALTER TABLE `twist_data` ADD UNIQUE INDEX `url_theme` (`url`, `theme`)

More information in the MySQL Documentation.

answered Feb 16 '17 at 00:42

Dekel

60,707
10
101
129

You might also mention the `INSERT IGNORE` syntax. – Paul Spiegel Feb 16 '17 at 01:10

score 0 · Answer 3 · answered Feb 16 '17 at 00:45

0

Create a Select count query where in you will check if the data exists or not then make an if condition. if it is true, then don't insert, if it has no data (false), insert a query.

Ex:

Select count(*) from twist_data where url='".$url."';

answered Feb 16 '17 at 00:45

Earvin Nill Castillo

70
1
11

score 0 · Answer 4 · edited May 23 '17 at 12:01

0

Use the ON DUPLICATE key words in your insert query. See: INSERT ... ON DUPLICATE KEY (do nothing)

And set the column's index to unique.

edited May 23 '17 at 12:01

Community

1
1

answered Feb 16 '17 at 00:48

Jamhead

191
9

1

The `on duplicate` is relevant **only** if you have a unique index. – Dekel Feb 16 '17 at 00:48
Edited. Thanks. :) – Jamhead Feb 16 '17 at 00:50
Did you check my answer from 7 minutes ago? :) (a vote there will be appreciated) – Dekel Feb 16 '17 at 00:51
I'm pretty sure yours will throw an error if you only set the index to unique. You have to also define what to do in that situation from my experience. – Jamhead Feb 16 '17 at 00:53
The `query(...)` will return false, but thats ok. – Dekel Feb 16 '17 at 00:54

score 0 · Answer 5 · answered Feb 16 '17 at 00:50

Replace the last two ligne with this code: $query = "select * from twist_data where url='$url'"; $db->query($query); if($db->num_rows>0){ $query = "INSERT INTO twist_data ( url,theme ) VALUES ('$url','$theme')"; $db->query($query); }

Mysql if data exists then do not insert it again

5 Answers5