I try to add 1 to an counter-field in Postgres/php.
But it seems not to work well with a query with parameters
My query is:
UPDATE calendar."event" SET "sequence"=$1,"id_calendar"=$2,"starttime"=$3,"endtime"=$4,"summary"=$5,"description"=$6,"remark"=$7,"id_address"=$8,"available"=$9,"changed"=$10,"auth_user"=$11 WHERE ("id" = 23)
The array with parameters:
'sequence' => 'sequence+1',
'id_calendar' => '1',
'starttime' => '2017-06-28 11:59:00',
'endtime' => '2017-06-28 18:00:00',
'summary' => 'test',
'description' => '',
'remark' => '',
'id_address' => '25',
'available' => '1',
'changed' => 'now()',
'auth_user' => '5',
The function for column 'changed' is working well.
But the sum for 'sequence' is giving an error.
'ERROR: 22P02: invalid input syntax for integer: "sequence+1"
I have tried extra quotes or adding a function like abs(). But nothing helps.
Google could not help also.
To prepare and execute queries I use:
- pg_send_prepare()
- pg_send_execute()
What am I doing wrong?
Thanks in advance!
You can't bind a function call, but there is no need to bind now(). You only need to bind variables (dynamic). Just put the now() directly in your statement.
I believe, with the syntax listed above, that you're trying to update an integer column with the value 'sequence + 1'. You'll need to calculate the value and provide that to your function, I.e. "Sequence"=>5. Look at your error, it's telling you exactly what the issue is.
The most likely more "correct" way of doing this would be to create an actual sequence and use the nextval() function on your query. No need to increment on your side when Postgres has a built in way of doing it better.
https://www.postgresql.org/docs/9.6/static/sql-createsequence.html https://www.postgresql.org/docs/9.6/static/functions-sequence.html