Error: Unknown column 'ID' in 'where clause'

[GeraintR]

I have verified that the object names I've given to the various editboxes on my form exactly match the column names in the database table I've created, but the data from my form input fields is not being posted to the database. Individual records are being created showing the system generated timestamp and URL data, which proves that it's working, and emails are being generated. The system did generate the timestamp/URL etc columns when I first tested the process, and did not generate any other new columns from the form user input objects when the form was processed, suggesting that there are no incompatibilities between the form's object names and the database table's column names, but I get the error message relating to an unknown column "ID". I'm aware that the php code that generates the timestamp/URL etc is separate from the code used to process the user input data, but can you provide any insight about the failure to post the user input data to the database? Surely I should be able to use my own names for the form's input objects?

All the code is wholly WYSIWYG generated. There are no hidden objects. Below is the error message, a dump of the table structure, and a couple of rows that have been created in the table.

ERROR MESSAGE:
<div id="container">
<div id="wb_Text1" style="position:absolute;left:151px;top:184px;width:275px;height:47px;z-index:0;">
<span style="color:#000000;font-family:'Times New Roman';font-size:16px;">Unknown column 'ID' in 'where clause'<br></span></div>
</div>

DB TABLE STRUCTURE
CREATE TABLE `screaming_swift` (
`Count_id` smallint(5) UNSIGNED NOT NULL,
`Location_id` smallint(6) NOT NULL,
`Street` tinytext NOT NULL COMMENT 'Street or grid reference',
`Survey_date` date NOT NULL COMMENT 'Date of count',
`Survey_start_time` time NOT NULL COMMENT 'Time of count',
`Count` tinyint(4) NOT NULL COMMENT 'Nos. birds',
`Building_type` tinytext NOT NULL COMMENT 'Building type',
`Building_age` smallint(4) NOT NULL COMMENT 'Approximate year built',
`email` tinytext NOT NULL COMMENT 'Submitter email ',
`Comments` text NOT NULL,
`DATESTAMP` date DEFAULT NULL,
`IP` varchar(15) DEFAULT NULL,
`BROWSER` varchar(255) DEFAULT NULL,
`FORMID` varchar(255) DEFAULT NULL,
`SUBMIT` varchar(255) DEFAULT NULL,
`TIME` varchar(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

DATA DUMP

INSERT INTO `screaming_swift` (`Count_id`, `Location_id`, `Street`, `Survey_date`, `Survey_start_time`, `Count`, `Building_type`, `Building_age`, `email`, `Comments`, `DATESTAMP`, `IP`, `BROWSER`, `FORMID`, `SUBMIT`, `TIME`) VALUES

DATA
(7, 0, '', '0000-00-00', '00:00:00', 0, '', 0, '', '', '2025-05-04', '2a00ff85:', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:138.0) Gecko/20100101 Firefox/138.0', NULL, NULL, '21:58:48'),
(8, 0, '', '0000-00-00', '00:00:00', 0, '', 0, '', '', '2025-05-04', '2a00ff85:', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:138.0) Gecko/20100101 Firefox/138.0', NULL, NULL, '22:40:55');

[pmacdonald]

Hello,

Interesting that the error message you have given indicates the ID column is contained in a WHERE clause of an SQL statement. I am wondering if this error message is not directly related to the INSERT statement you are showing?

I think a striped down sample of your project is going to be required to nail this issue down.

Unrelated, but something that might bite you later on, is using a reserved word for a column name, specifically the TIME column. I would suggest something like TIME_SUBMITTED, but that's just me.

Regards

Parker

[Pablo]

The table should have a column named 'id' for the primary key.

[GeraintR]

Hi Pablo, thanks for your responses.

I've altered the primary key name to 'id'. I actually created a new column in the database having deleted all the rows. The 'ID' error message has disappeared, but I now get a 'Duplicate entry '0' for Primary Key' error. The TIME/URL data that's created by the form's automated script is posted to the database, but again the user input is missing. Strangely, the records are actually posted with an autoincremented key. This suggests that there's an issue with the key generation. The Form is presumably sending the value '0' to the database because I'm not entering anything as it's set to autogenerate, the Form's logic identifies that it's a duplicate, issues the error message to me, and then the database generates a new key and posts the data, leaving the user input fields blank. As the code for the autogenerated data is processed slightly differently to the user generated data, it isn't affected by the seemingly duplicated key. Is there some setting that I've missed in the properties relating to the key, or do I have to generate a local value for the primary key?


Hi Parker, thanks for responding. The column names are actually generated by the Form's automated scripting. I haven't written any of the code myself. I stopped writing code about 15 years ago, and I'm beyond rusty!

[Pablo]

The script does not send a id value to the server. It is assumed that it's auto generated.
This has not been an issue for anyone else as far as I know. So, i think it something in the database/table configuration.

[GeraintR]

Hi,

I've contacted the IONOS helpdesk and they can't shed any light on the issue. I created a new form and a new table, with just three fields on it and I've got the same result. Seemingly, when I submit the first record to the table, the value '0' is posted to the id column, and only the system generated data is posted to the table, then when I submit a second record it detects the previous record with the id=0, creates the error message that the PK is duplicated, and then a second record is created on the table with an id=2, also without the user submitted data.

I thought it odd when an id=0 was posted because the autogeneration should start at 1. I'm not quite sure how I can extract the page for you to duplicate the issue, but I can, of course send you all the code. I cannot be sure whether it's a database issue, a WYSIWYG issue or a combination of the two.

[Pablo]

Where do you see a id=0 posted?
What is the code of the form?

Note that the code generated by the software is very straight forward so you can easily check it for errors.

[GeraintR]

The id=0 is posted to the table. I've dumped the first two rows of the new table I created. The id values are 0 and 2.

INSERT INTO `swift_results` (`id`, `Editbox2`, `Editbox3`, `DATESTAMP`, `TIME`, `IP`, `BROWSER`, `FORMID`, `SUBMIT_SWIFT_RESULTS`) VALUES
(0, 0, '', '2025-05-06', '15:56:52', '2a00ff85:', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:138.0) Gecko/20100101 Firefox/138.0', 'form1', NULL),
(2, 0, '', '2025-05-06', '15:59:37', '2a00ff85:', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:138.0) Gecko/20100101 Firefox/138.0', 'form1', NULL),

I can see from the code that the id isn't tranmitted, butthe '0' is coming from somewhere. My limited knowledge is that if the autogenerated field is set to '0' or Null, then the autogeneration kicks in. My coding knowledge is not good, but I did work as an IT auditor, so I have some background in checking code, but it was mainly Cobol, I am 70!

[GeraintR]

I forgot to mention that the id value in the success email is always blank, even though the other user input fields are there

[Pablo]

I am confused by the 'id' field in the query. Did you add a form field named 'id' to your form?
This is not correct. The 'id' is the primary key of table but it should not be a field in your form.

If you need further assistance then please read this first:

[GeraintR]

Thank you; issue resolved. I clearly misunderstood how the key auto generation was programmed :