Trying To Get "on Duplicate Key Update" Working :o/

[Presto-X]

Hello guys,

 

I’m trying to build a query that adds a user’s check in / out date and time in to the database, basically I need to check if 5 columns match if so update the row, if not add a new row. I thought this was going to be easy but after a few hours of reading, I am still not able to get the query to work *sigh*

 

This is how my table is built:

CREATE TABLE `conferences_seminar_sessions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`eventid` int(11) NOT NULL,
`theaterid` int(11) NOT NULL,
`seminarid` int(11) NOT NULL,
`action` tinyint(1) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

And this is my query:

$query = "
INSERT INTO cswconferences_seminar_sessions
(userid, eventid, theaterid, seminarid, action)
VALUES ('" . $userid . "', '" . $eventid . "', '" . $theaterid . "', '" . $seminarid . "', '" . $action . "')
ON DUPLICATE KEY UPDATE userid=userid+'" . $userid . "'
AND eventid=eventid+'" . $eventid . "'
AND theaterid=theaterid+'" . $theaterid . "'
AND seminarid=seminarid+'" . $seminarid . "'
AND action=action+'" . $action . "'
;";

Edited December 26, 2012 by Presto-X

[Presto-X]

Or maybe I should be using REPLACE INTO or maybe even INSERT IGNORE INTO? heck I don't know lol

Edited December 26, 2012 by Presto-X

[Presto-X]

So if I set the userid, eventid, theatherid, and seminarid as primary keys and remove the key and auto increment from the id column it seems to work, but I want auto increment on the id column :-\

[Christian F.]

What's the error message you're getting, and are you actually handling the errors? Also, have you tried to echo out the finished query, to see how it looks and tested it manually against the database?

[Barand]

WHich fields are supposed to be unique, apart from the id which will not be duplicated on inserting?

[Presto-X]

Hey guys, thanks for the replies.

 

So here is my query

SQL=INSERT INTO conferences_seminar_sessions (userid, eventid, theaterid, seminarid, action) VALUES ('816', '1', '1', '6', '1') ON DUPLICATE KEY UPDATE VALUES (userid='816', eventid='1', theaterid='1', seminarid='6', action='1');

 

What I need to do is make sure that the user has not already signed in to a seminar, if so do nothing or just update the current row with the current timestamp ether of these options work what ever is easier. So I'm guessing I need to check if the user has checked in to the seminar and if the action matches (1=checked in, 2=checked out). So check if userid, seminarid, and action match any other row.

 

Should I be doing a query first to look if there are any matches if not add a new row, if so update the current row?

Edited December 27, 2012 by Presto-X

[Presto-X]

So I just set the userid, seminarid, and action columns as primary keys, and changed my query to the following, still no go:

INSERT INTO conferences_seminar_sessions (userid, eventid, theaterid, seminarid, action) VALUES ('816', '1', '1', '6', '1') ON DUPLICATE KEY UPDATE created=VALUES('2012-12-27 07:03:40')

Edited December 27, 2012 by Presto-X