[SOLVED] Problem with INSERT then.. UPDATE but what is DELETE?

[pkedpker]

So this is how it goes atm..

 

everytime someone logs in it enters into online_list table his id and timestamp.. (I did want to do this in flat-text.. php) but it turned out to be even harder then just using mysql.. because I don't want results to pile up.. and If I used flat-text.. there would of be a chance where it would be deleting some data while new one is coming in.. and that would require 2 files.. one for new entries and 1 for current entries too much work to be honest. So new ones would get emptied only when added to current entries.

 

 

So then in mysql I ran into a problem like that similar..

 

everytime user logs in added to online_list table okay..

then I DELETE any user who didn't use any action for over 15 mins..

 

BUT that doesn't mean hes logged off.. since its session based. Say a user is deleted from online_list and still logged in.. I cannot use UPDATE Query anymore.. but INSERT.. but I want to see if its possible with a MYSQL trick like..

 

here is a trick for INSERT

 

INSERT INTO online_list (id,hidden,date) VALUES ($id, '0', NOW()) ON DUPLICATE KEY UPDATE date=NOW()

 

ON DUPLICATE KEY.. is the trick to make sure no duplicate entries of same ID pop in due to the fact its not primary..

 

Now im searching for a trick to do UPDATE all the time without any inserts.. BUT if it doesn't exist.. then do a INSERT..

 

 

If none of of what im asking for exists in mysql.. then well... I'm stuck with always calling INSERT? when it might already exist? thats not stressful on mysql is it?

[Maq]

Now im searching for a trick to do UPDATE all the time without any inserts.. BUT if it doesn't exist.. then do a INSERT..

 

This isn't a trick.  Check out REPLACE.  It works similarly to insert except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE  index, the old row is deleted before the new row is inserted.

 

[pkedpker]

should I just do a UPDATE in php and check with int mysql_affected_rows () if = 0 then do another query with INSERT?

 

or should I use replace (kinda looks compliacted?)

[Maq]

should I just do a UPDATE in php and check with int mysql_affected_rows () if = 0 then do another query with INSERT?

 

or should I use replace (kinda looks compliacted?)

 

You could do it your way which would be slower, but you most likely won't notice a difference.  With REPLACE you only need 1 query.  You're choice.

[pkedpker]

I'd do it your way but I have no idea how?

 

REPLACE `online_list` SET `id`=".$_SESSION['id'], `date` = NOW();

 

or..

 

REPLACE `online_list` SET `date`=NOW() WHERE `id`=".$_SESSION['id']

 

which one? NVM.. solved only top one works.. too bad it affects 2 rows.. when it should only affect 1.. (probably just because it does delete then a insert again)

[Maq]

I like to use the INSERT syntax:

 

REPLACE INTO online_list (id, date) VALUES ({$_SESSION[id']}, 'NOW()')

 

Please refer to the manual for more information - REPLACE.

[pkedpker]

I like to use the INSERT syntax:

 

REPLACE INTO online_list (id, date) VALUES ({$_SESSION[id']}, 'NOW()')

 

Please refer to the manual for more information - REPLACE.

 

unforantely yours doesn't work. yah and i figured the missing quote in your $_SESSION['id'].. no compiler errors just doesn't update date.

 

Ahh fixed it NOW() was in quotes.. it cannot be in quotes as it is function

[Maq]

EDIT: Try removing the single quotes around NOW().  You should also back up your table by dumping it.