Page 1 of 1

INSERT...ON DUPLICATE

Posted: Thu Aug 30, 2007 11:05 am
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.

Posted: Thu Aug 30, 2007 1:04 pm
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

Posted: Thu Aug 30, 2007 3:27 pm
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;

Posted: Fri Aug 31, 2007 4:12 am
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')");
}
?>