Database update Increase field value by one

Wappler General How To
#1

I have a blog table where i want the “Hits” field to be increased by one every time a post is viewed. Like UPDATE Posts set hits = hits + 1 where id = :$_GET_id
What is the best approach to achieve this?

#2

Hi, you could use an update server action, that executes on every page load.

Do this
(1) get the current post hits
(2) create a repeat action and use as expression query created in step (1)
(3) use the hits value from the query in step (1) inside the repeat
(4) create an update server action that sets hits like this: hits got from query in step (1) + 1

Assign this server action in AppConnect. The issue is that it will update on every page refresh. So maybe caching user’s IP so to update only if the IP has not visited before. Or maybe assign a cookie of read=1 and only update when cookie value not exists.

Hope it helps
Thank you

#3

Thanks, it helped a lot and fixed the problem with updating.

But I have some issues about the refresh problem, is there a way to make this server connect to load only on page initial load and not on postback/refresh.

With the caching of Ip or Cookie approach we would not get an hit update if the user go back and select another post to view, all posts are viewed in the same page :slight_smile:

Regards Olav

#4

I am sure there is a workaround for this but depends on how your website webapp works. A quick suggestion would be

1.Use a condition before running the hits update serveraction that checks if session loaded==1
2.After running the hits update set the session loaded=1

This way the first time you visit the page it will execute since session wont be =1 and all the rest it wont since session will be=1

#5

Regarding the hits update how do you save them? It counts individualy for each blog post right? If yes I think that cookie or IP could work.

#6

Thanks for your previous help.

After I changed the sql server from MYSQL to MS SQL something changed.

Now it dont add 1 to the hits field value, the hits value of 345 becomes 3451, next time 34511. Almost as if + was used with a string value. (it is a integer field)

Is there a logic explanation for this or is it a bug.

Not sure if this is because of the platform change or Wappler version upgrade

Regards

Olav Nielsen

#7

check your database types - somehow they are probably text types so that is why 1 is appended to the text

#8

I will check.

It is a integer in the database. And the update action list the field as numeric.

The field type is lost somewhere

Mvh fra min S9+ mobil
Olav Nielsen

91861000

-------- Opprinnelig melding --------

#9

I think it could be a bug in the PDO driver for MS SQL.

#10

That is a possible reason, although field type type is listed as numeric in update action.

{{Hits.toNumber() + 1}} as new value solved the problem

Mvh fra min S9+ mobil
Olav Nielsen

91861000

-------- Opprinnelig melding --------

1 Like
#11

It is listed as numeric because the database field is integer, Server Connect gets the metadata from the table fields but does not check the actual result returned from the query. So a broken driver that returns the value incorrect could cause this kind of problems.

#12

I read in pdo bug post above that the option “PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE” for the connection string could solve the problem. Any thought?
Could this be or is it implemented in wappler

#13

We have to check if it would not break anything else first, there was also a similar problem with MySQL which we fixed with an extra PDO option, but there we now have the problem that some MySQL versions the views don’t work. So we have to be careful with making changes.

#14

No problems converting newvalues to numbers before updating, until the problem is solved : )
By the way, problems listed with date values seem to be solved. No issues with dates