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
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
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.