error in mysql statement using IF@@

Question

I seem to have an error in my sql statement below

$mysql = mysql_query(
    "UPDATE rsvp SET (`userid` =  '$userid' ,  `screenname` =  '$screenname' ,  
      `picture` =  '$profile_image_url' ,  `status` =  '$rsvpstatus') 
    WHERE eventid='$eventid' 
    IF @@ROWCOUNT=0 
    INSERT INTO `rsvp` (`userid`, `screenname`, `picture`, `eventid`, `status`) 
      VALUES (`$userid`, `$screenname`, `$profile_image_url`, `$eventid`, `$rsvpstatus`)"
) or die(mysql_error());

using mysql 5, any ideas?

Answer 1

Why don't you use a REPLACE INTO query instead of what you have. The syntax is exactly the same as INSERT INTO, (except with REPLACE instead of INSERT, duh) and MySQL updates the row if it exist, inserts it if it doesn't, and is all based on the primary key passed.

Assuming your table looks like this:

CREATE TABLE rsvp (
  userid int(11) NOT NULL default '0',
  screenname varchar(255) default NULL,
  picture varchar(255) default NULL,
  eventid int(11) NOT NULL default '0',
  status varchar(8) default NULL,
  PRIMARY KEY  (userid,eventid)
)

The query

REPLACE INTO `rsvp` (`userid`, `screenname`, `picture`, `eventid`, `status`) 
  VALUES (`$userid`, `$screenname`, `$profile_image_url`, `$eventid`, `$rsvpstatus`)"

would insert the row if the combination of userid, eventid didn't exist, and update the row with the other values it if it did.

Answer 2

1. You shouldn't use parentheses like you're doing in the UPDATE statement.

2. The @@ROWCOUNT variable is a Microsoft SQL Server feature, and is not supported in MySQL.

3. The IF condition you have is not supported in MySQL syntax.

4. You should look into using INSERT ... ON DUPLICATE KEY UPDATE.

   MySQL also supports a REPLACE statement but I don't like it because internally it does a DELETE followed by an INSERT. This often has unintended effects like creating a new auto-increment ID, violating references from dependent tables, and firing delete and insert triggers in a confusing way.

PS: This is tangential to your question, but I want to give a caution that you should be using query parameters instead of interpolating variables directly into your SQL string. Preventing SQL Injection flaws begins with you! Note that ext/mysql doesn't support query parameters -- you have to use ext/mysqli or ext/pdo_mysql.

---

Here's an SQL script that demonstrates that REPLACE does an INSERT followed by a DELETE:

CREATE TABLE foo (
  foo_id SERIAL PRIMARY KEY,
  stuff VARCHAR(10) DEFAULT 'this'
) TYPE=InnoDB;

CREATE TABLE log (
  oper VARCHAR(10) NOT NULL,
  tablename VARCHAR(10) NOT NULL,
  pk BIGINT UNSIGNED NOT NULL
);

DELIMITER //
CREATE TRIGGER foo_del AFTER DELETE ON foo
FOR EACH ROW BEGIN
  INSERT INTO log VALUES ('delete', 'foo', OLD.foo_id);
END//
CREATE TRIGGER foo_upd AFTER UPDATE ON foo
FOR EACH ROW BEGIN
  INSERT INTO log VALUES ('update', 'foo', OLD.foo_id);
END//
CREATE TRIGGER foo_ins AFTER INSERT ON foo
FOR EACH ROW BEGIN
  INSERT INTO log VALUES ('insert', 'foo', NEW.foo_id);
END//
DELIMITER ;

INSERT INTO foo () VALUES ();

CREATE TABLE bar (
  foo_id BIGINT UNSIGNED,
  stuff VARCHAR(10) DEFAULT 'that',
  FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE
);

INSERT INTO bar (foo_id) VALUES (LAST_INSERT_ID());

SELECT * FROM foo LEFT OUTER JOIN bar USING (foo_id);

REPLACE INTO foo (foo_id, stuff) VALUES (1, 'other');

SELECT * FROM foo LEFT OUTER JOIN bar USING (foo_id);

SELECT * FROM log;

Output of the last query:

+--------+-----------+----+
| oper   | tablename | pk |
+--------+-----------+----+
| insert | foo       |  1 |
| delete | foo       |  1 |
| insert | foo       |  1 |
+--------+-----------+----+

Fortunately, MySQL seems to have implemented REPLACE as an atomic operation, so it doesn't delete rows in dependent tables by mistake.

Answer 3

Don't think you can use @@ROWCOUNT in MySQL, but there is functions to use, see this thread: http://forums.mysql.com/read.php?60,45239,45239#msg-45239