My table is:
CREATE TABLE A( id serial NOT NULL, date timestamp without time zone, type text, sub_type text, filename text, filepath text, filesize integer, );
I have an update routine:
$Query = "UPDATE A SET type=\"" . $strType . "\" where id=" . intval($ID);
When $strType is a string, like “lettuce” I get this error:
ERROR: column "lettuce" does not exist
When it is an int, no error.
- the insert code adds filename,filepath, both text types successfully
In SQL, double-quotes are identifier delimiters. Your UPDATE statement is trying to set the
type column to the value of the
"lettuce" column, which is an error if there is no column named lettuce.
You want to use single-quotes to delimit a string literal in SQL:
$Query = "UPDATE A SET type='" . $strType . "' where id=" . intval($ID);
Your coding is open to SQL injection. Use placeholders (probably unquoted question marks) and pass the value separately. That avoids the problems at XKCD.
Bill has the answer to your immediate problem about column names vs strings.