Need help with mysql query.

[radhisha]

Hi friends,
I need your help with smf's sql insert function.
I want to check particular data row was already exist or not, if not exist means insert new row otherwise skip.
I can do this with two function [two means: select function and insert function], But I need to know how to do this with single function.

This is my actual SMF insert query function.

Koodi [Valitse] Laajenna


$smcFunc['db_insert']('replace',
'{db_prefix}mit_notification',
array('user_id' => 'int','msg_id' => 'int'),
array($mit['mention_mem_id'],$mit['msg_id']),
array()
);


Sorry for my poor english.
and thanks in advance.

[Arantor]

Technically there's no way to do exactly what you're asking. Simply not possible.

There are two things you can do that are almost the same as what you're asking.

Firstly, you can do it with select/insert as you point out. Secondly, you can do what you're doing in that statement, a REPLACE INTO.

A REPLACE INTO is not the same as select/insert. What happens is that it tries to do the insert and if there's a duplicate row (by primary key), that old row will be deleted and the new row inserted.

Theory says looking at that table definition, you would have a primary key made up of both columns anyway so that statement should actually do what you want.

[emanuele]

if not exist means insert new row otherwise skip.

Isn't that an insert ignore instead of a replace?

[Arantor]

Not exactly. It's all based on the primary key. For this specific case, yes, it probably will amount to the same thing.

Trouble is, INSERT IGNORE isn't exactly that. It's 'insert and ignore any errors', not just 'insert and ignore duplicates', there is actually a separate INSERT ... ON DUPLICATE KEY UPDATE instruction which isn't mapped into $smcFunc.

[radhisha]

First i have to say thank you guys for the reply. @Arantor and @emanuele
I did my job fine.

I have asked this help for develop a notification system for my forum.
Now the job done perfectly.

You guys can review the notification system in my forum.
My forum link : hxxp:www.multyinfotech.com [nonactive]

This is actually what i did.

1. Name mention notification. [done and executed]
    When you mention someone's username with @ symbol, the notification system will show a mention notification to that corresponding member with the thread/post's url link and mention time.

2. Quote notification. [under development]
    When you reply someone's thread/post with quote the notification system will show a quote notification to that corresponding member with the thread/post's url link and quote time.


You can check the system by using this thread. hxxp:www.multyinfotech.com/announcements/new-notification-system/ [nonactive]
Guys i need your suggestion.

[Arantor]

What suggestion do you need exactly? I don't really understand what information you need from us at this point in time.

[radhisha]

First time i have edited/modified smf's source code
Developed a notification system in my forum
You can see the system from here hxxp:www.multyinfotech.com [nonactive]

I need suggestion [how to improve the system] about my notification system from smf experts.

[Arantor]

I don't really know what I'm looking for...

I do see that you're posting warez links in one board which isn't tolerated here, though I realise that's off-topic. As is the fact you're running a version with multiple known security holes.

[radhisha]

I don't really know what I'm looking for...

I do see that you're posting warez links in one board which isn't tolerated here, though I realise that's off-topic. As is the fact you're running a version with multiple known security holes.

So in which version of smf don't have security hole? 

[Arantor]

The *current* one, i.e. 2.0.5. You're using an older version.

[radhisha]

The *current* one, i.e. 2.0.5. You're using an older version.

updated. thanks.