INSERT...ON DUPLICATE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bizzy
Forum Newbie
Posts: 10
Joined: Mon Apr 16, 2007 1:15 pm

INSERT...ON DUPLICATE

Post by Bizzy »

Hey dudes,

please help me with this situation:

I have 4 columns (mode, position, pageid, counter) and I need to have unique combinations of columns except counter. If there is situation I already have any combination then counter + 1. I wanted to use:

Code: Select all

INSERT INTO ana_produkt (mode,position,pid) VALUES ('$mode','$position','$pid') ON DUPLICATE KEY UPDATE counter=counter; UPDATE ana_produkt SET counter=counter+1 WHERE mode='$mode' AND position='$position' AND pid='$pid';
It works in SQL command line, but it's strange and PHP doesn't work with it at all.

Thanks.
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by josa »

I looks like you are trying to execute two queries one after the other. If that's the case you have to do the insert first and then the update because mysql_query() will not look past the first semicolon (;). From the PHP manual:
mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.
/josa
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I don't understand what you're trying to do... What's wrong with the following? (If i understood well, that's exactly what you're trying to achieve).

Code: Select all

INSERT INTO ana_produkt (mode,position,pid) VALUES ('$mode','$position','$pid') 
ON DUPLICATE KEY UPDATE counter = counter + 1;
Bizzy
Forum Newbie
Posts: 10
Joined: Mon Apr 16, 2007 1:15 pm

Post by Bizzy »

It doesn't depend just on PRIMARY KEY, example:

PID - MODE - POSITION
22 - recommend - 2
22 - related - 26
22 - NULL - NULL
24 - recommend - 3
26 - related - 6
26 - recommend - 2


Do you understand? I need to have unique row, not unique pid.

I've solved it.

Code: Select all

<?
@mysql_query("UPDATE ana_produkt SET counter=counter+1 WHERE mode='$_REQUEST[mode]' AND position='$_REQUEST[pos]' AND pid='$pid'");
if (mysql_affected_rows() == 0){
  @mysql_query("INSERT INTO ana_produkt (mode,position,pid) VALUES ('$_REQUEST[mode]','$_REQUEST[pos]','$pid')");
}
?>
Post Reply