Page 1 of 1

Incrementing a value via SQL

Posted: Mon Jan 30, 2006 8:20 am
by HuwPioden
Hi everyone. This is my first post so please be gentle :D :D

I'm coding a basic click counter and instead of keeping records of every single event I'm trying to have an incrementing value for each click ... i.e. instead of 25 seperate records for clicks http://www.yoursite.com I will have a field containing http://www.yoursite.com and a value of 25 ...

Is there a way to increment a value with a single SQL query? i.e. "INSERT +1 WHERE destination = "http://www.yoursite.com""

I hope that all makes sense!!

TIA

Huw

Posted: Mon Jan 30, 2006 8:29 am
by JayBird

Code: Select all

UPDATE `table` SET `counter` = `counter` + '1' WHERE `destination` = 'http://www.yoursite.com'

Posted: Mon Jan 30, 2006 8:41 am
by HuwPioden
Genius. Thanks pal. I knew there was some way of doing it. Works like a dream now - logs all the clicks and redirects without the punter seeing a thing. Sweet.

Cheers

Huw

Posted: Mon Jan 30, 2006 12:23 pm
by josh
might want to put a limit 1 on the end

Posted: Mon Jan 30, 2006 12:34 pm
by JayBird
jshpro2 wrote:might want to put a limit 1 on the end
not if he is updating multiple records like he said

Posted: Mon Jan 30, 2006 2:02 pm
by raghavan20
Pimptastic wrote:
jshpro2 wrote:might want to put a limit 1 on the end
not if he is updating multiple records like he said
i do not think it is a good idea...you are not logically coding rightly if you are limiting update using limit...

Posted: Mon Jan 30, 2006 2:24 pm
by josh
Actually, no where did he say he is updating multiple records, he said he will have one record for each site and a field that stores the number of hits. Limit 1 is so mysql stops after the first row so it need not load the entire index into memory - very excellent practice if you ask me. You might also consider running this kind of code with low priority as well (so your crucial selects jump the queue)

Posted: Mon Jan 30, 2006 2:36 pm
by raghavan20
jshpro2 wrote:Actually, no where did he say he is updating multiple records, he said he will have one record for each site and a field that stores the number of hits. Limit 1 is so mysql stops after the first row so it need not load the entire index into memory - very excellent practice if you ask me. You might also consider running this kind of code with low priority as well (so your crucial selects jump the queue)
What you are saying is right when there is only one record to be updated and there is only one record that matches the conditions as well. This would avoid searching the index further after the first match is found...but this is not a viable solution to stop updating something when more than one match exists...

Re: Incrementing a value via SQL

Posted: Mon Jan 30, 2006 2:58 pm
by josh
HuwPioden wrote:I will have a field containing http://www.yoursite.com and a value of 25 ...

He is only updating one row, that field should be unique anyways (which I think tells mysql to stop the table scan after the first match), the suggestion for the limit was for optimization, not to be taken as an alternative to using a where clause or maintaining unique data in his column/field.