insert into mysql problem

Question

Hi, I have a table with 2 fields (name, confirm). confirm is of type tinyint because I want to put in 0 or 1

Now I want to put 1 in confirm; is this statement correct?

if($row['confirm']==0)
{
    $query = "INSERT INTO opt (confirmed) values('0')";
    echo 'The user selected options has confirmed';
}

Answer 1

When 'confirmed' is a numeric column, then you should not put quotes around the 1.

INSERT INTO OPT (confirmed) VALUES (1)

Answer 2

The statement you've written is only correct if you also want the associated 'name' field to be NULL.

If you actually want both fields to be populated you should do:

INSERT INTO opt (name, confirmed) VALUES (?, ?)

and then use the mysqli or PDO family of functions to bind the ? parameters to the user-supplied values. This is necessary to protected your database against SQL injection attacks.

I would also consider adding another couple of fields:

• A unique id field (MySQL's auto_increment is good for this) so that you can delete and/or modify specific records
• A timestamp field so that you've got a record (pun not intended) of when this data was added

Answer 3

Everything the previous posters said +: If you only have two possible values for the "confirmed" field then make it a ENUM.

Answer 4

It really sounds that you want to do an update:

UPDATE opt SET confirmed = 1 WHERE name = '$row[name]'

Right?