Incrementing a value via SQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
HuwPioden
Forum Newbie
Posts: 2
Joined: Mon Jan 30, 2006 8:15 am

Incrementing a value via SQL

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Code: Select all

UPDATE `table` SET `counter` = `counter` + '1' WHERE `destination` = 'http://www.yoursite.com'
HuwPioden
Forum Newbie
Posts: 2
Joined: Mon Jan 30, 2006 8:15 am

Post 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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

might want to put a limit 1 on the end
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

jshpro2 wrote:might want to put a limit 1 on the end
not if he is updating multiple records like he said
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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...
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Incrementing a value via SQL

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