0

I have a table - views - in MySQL that tracks when an email is sent, and then how many opens it has (using a pixel). The table is created when the email is sent.

Most of the process works fine - when the email is opened, pixel loads, sends query etc.

The problem is when the query is sent to the table to update the number of views it is not updating is the value is 'NULL'.

The default value is 'NULL' as there are no views when the email is first sent.

If I manually change the field to a number, then send email again, it updates normally.

Column type is INT(6)

This also happens with another field (location) that should be updated on view as well (although the is VARCHAR).

The update query is:

UPDATE $views SET Email_Read = Email_Read + 1 WHERE id=:id

Is this normal behaviour - does NULL stop fields being updated?

If not (I think it is down to an error by me) do you know what might be causing the problem?

HG123
  • 23
  • 5
  • No your issue is that you cannot use a parameter in place of the table or view name – RiggsFolly Jun 07 '18 at 08:29
  • Sorry, typed the query out wrong, it's using a variable. It does work as long as NULL is not in the Email_Read field to begin with, so don't think it is a duplicate – HG123 Jun 07 '18 at 08:34
  • I would start by making the default value Zero instead of NULL – RiggsFolly Jun 07 '18 at 08:37
  • Thanks - I am concerned that that is just avoiding the problem, do you know what is causing the problem in the first place? – HG123 Jun 07 '18 at 08:39
  • This may help explain https://stackoverflow.com/questions/5367212/mysql-update-increment-int-field-that-is-null – RiggsFolly Jun 07 '18 at 08:46

0 Answers0