Top Poster
#1 ramon fincken 401
#2 Site admin 3
#3 Ber|Art 2
#4 braddmark 1
#5 accentmedia 1
#6 Xarzu 1
#7 pEcosespedex 1
#8 khadish 1
#9 moxxx 1
#10 nistelrock 1
[solution] Inserting data, ignoring any key conflicts



Rating : 4 / 5

 
Post new topic   Reply to topic    Ramonfincken.com Forum Index -> Mysql databases
Beta
Want to be notified by email when this topic gets a reply?  
View previous topic :: View next topic  
Author Message
ramon fincken
Site's programmer

Get a free globally recognized avatar
It's free!


Joined: 03 Aug 2007
Posts: 401
Location: A'dam/Diemen, The Netherlands

PostPosted: Sun Jun 07, 2009 8:24 pm    Post subject: [solution] Inserting data, ignoring any key conflicts Reply with quote

How to insert data, but you are not sure if the data exists, but dont' want to see a mysql error while inserting?
This is really handy when inserting XML / RSS feeds !

There are two solutions:

Solution1 :
Code:
$sql = 'INSERT INTO '.TABLE_NAME .' ( key1, key2 ) VALUES ( val1, val2 ) ON DUPLICATE KEY UPDATE fetched=fetched+1';

This will insert the data if there is no key conflict.
If there is a key conflict ( existing key = key you want to insert ), no error will be given and NO data will be updated except for the row called "fetched" which has a counter and will increase with the count of 1;
( fetched NULL allowed, DEFAULT = 0 )

Solution2a :
Code:
$sql = 'INSERT IGNORE INTO '. TABLE_NAME . '  ( key1, key2 ) VALUES ( val1, val2 )';

This will insert the data if there is no key conflict.
If there is a key conflict ( existing key = key you want to insert ), no error will be given and NO data will be updated.

Solution2b :
You can even do cool things like this, if you don't care if the insert is carried out right away:
Code:
$sql = 'INSERT LOW_PRIORITY IGNORE INTO '. TABLE_NAME . ' ( key1, key2 ) VALUES ( val1, val2 )';
Back to top
View user's profile Send private message Visit poster's website
Google adsense
Advertisement





PostPosted: Sun Jun 07, 2009 8:24 pm    Post subject: [solution] Inserting data, ignoring any key conflicts

Advertisement
Back to top
GravityForms
Advertisement





PostPosted: Sun Jun 07, 2009 8:24 pm    Post subject: [solution] Inserting data, ignoring any key conflicts

Advertisement
Gravity Forms Plugin for WordPress
Back to top
Post new topic   Reply to topic    Ramonfincken.com Forum Index -> Mysql databases All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
General forums
The world of freelance
Programming
Other projects
Poll
I love gifts

Yes goodies and presents! [3]
No but I love polls ! [1]

Related google ads