CODEWITHSUNDEEP
mysqlsqldatabase
Amir Rasti
Amir Rasti

Reputation: 768

Case WHEN error

this is my query

CASE
    WHEN (SELECT COUNT(*) FROM `table` WHERE `ip`= 'myip' )=1 
        THEN UPDATE `table`
        SET `last_active`=".date("Ymd").",`last_time`=".date("His")."
        WHERE `ip`= 'myip';

    ELSE (INSERT INTO `table`(ip)
        VALUES("myip"));

        END

but its not woking .the problem should be from here 

 (SELECT COUNT(*) FROM `table` WHERE `ip`= 'myip' )=1

how can I fix this ? or how to do this another way?

the date("Ymd") function is just the php function to return the date the problem isnt from that

thanks

Upvotes: 1

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you are trying to do this:

create unique index unq_table_ip on table(ip);

insert into table (ip)
    values ('myip')
    on duplicate key update last_active = curdate(),
                            last_time = curtime();

The unique index (or equivalently a unique constraint) guarantees that a given ip only appears once in the table. Let the database do this work for you -- this is called maintaining relational integrity.

Notes:

  • This assumes that last_active and last_time have default values in the table.
  • You should use the database time, not the application time for this purpose (after all, different clients could have different time values).
  • You should probably put the date/time values into a single datetime column, say last_active_datetime.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Define unique constraint on ip column and then use ON DUPLICATE KEY UPDATE:

insert into `table` (ip) values ("myup") 
on duplicate key update 
`last_active`=".date("Ymd").",`last_time`=".date("His")."

Upvotes: 1

Related Questions