1

I have a table with a primary key id_form that is an autoincrement field.

I have a form that will insert into that table. I want to reuse the same form to edit the records. So my form has an hidden input that has value='' when I am inserting a new line in the table or value=<id of the edited row> while editing.

My query is the following:

INSERT INTO form_header SET 
    id_form= :id_form,
    nome_form=:nome_form,
    mainDescr=:mainDescr,
    step=:step,
    visibility=:visibility, 
    start=:start, 
    end=:end, 
    autore=:autore, 
    wizard_token=:wizard, 
    last_records=0 
ON DUPLICATE KEY UPDATE 
    nome_form=:nome_form,
    step=:step,
    visibility=:visibility, 
    start=:start, 
    end=:end

If I try to insert a new row I will pass id_form='' but I get:

Incorrect integer value: '' for column 'id_form' at row 1

My table structure is the following (only the relevant field):

Field       Type     Null   Key     Default     Extra   
id_form     int      NO     PRI     NULL        auto_increment

So how do I pass the id when it is empty? Without the ON DUPLICATE KEY part I'd only skip the field but now I have to pass it so that mysql can check if I am inserting or updating a row. Or there is another way to do it?

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
  • `id_form` is `auto_increment`. Why do you pass a value for it? Just remove: `id_form= :id_form,` – forpas Nov 23 '20 at 12:34
  • @forpas but then the `ON DUPLICATE KEY` part will not work. As I said in the question: _Without the ON DUPLICATE KEY part I'd only skip the field_ – Lelio Faieta Nov 23 '20 at 12:35
  • 1
    *If I try to insert a new row I will pass `id_form=''`* You must set it to NULL, not to empty string. – Akina Nov 23 '20 at 12:35
  • @Akina i added `if($id_form==''){$id_form=NULL;}` and now it works fine both on insert and update. Thanks! – Lelio Faieta Nov 23 '20 at 12:38
  • 2
    Or:`id_form= nullif(:id_form, '')` – forpas Nov 23 '20 at 12:39
  • Yeah I was going to suggest NULLIF() too. Read the docs on it here: https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html#function_nullif – Bill Karwin Nov 23 '20 at 14:05

0 Answers0