Click here to Skip to main content
14,691,000 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a problem with a line of sql and php code. I keep getting this error and I'm so confused as to what it is

the problem:

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

the sql code:

CREATE TABLE `attendanceout` (
  `out_ID` int(10) NOT NULL,
  `ID` int(10) NOT NULL,
  `time_out` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `date_info` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `Room` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `attendanceout`
  ADD PRIMARY KEY (`out_ID`);

ALTER TABLE `attendanceout`
  MODIFY `out_ID` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

this is the php script:

$sql = "INSERT INTO attendanceout (out_ID, ID, time_out, date_info, Room)
VALUES ('". $out_id . "', '". $id . "', '" . $time . "', '" . $date. "', '" . $dataSendOut. "')";


What I have tried:

I already tried to removed the quote in $out_id and $id but a different error appears. 


this is the error that appears if I remove the quote. "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"
Posted
Updated 3-Jul-20 6:52am

$sql = "INSERT INTO attendanceout (out_ID, ID, time_out, date_info, Room)
VALUES ('". $out_id . "', '". $id . "', '" . $time . "', '" . $date. "', '" . $dataSendOut. "')";

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
   
Comments
franciskane 3-Jul-20 11:56am
   
Duly noted. Will revise my code tomorrow. Thanks!
You don't put ints in quotes

VALUES (". $out_id . ", '". ...


Your code probably still won't work though as you are trying to insert a specific value into an auto_increment field which you can't do. Don't try and insert a value for out_ID, let the database manage it.
   
Comments
franciskane 3-Jul-20 11:25am
   
Yes I already removed the quotes on ints but a different error appears when I do that. and I'm not trying to insert a value on the db. It requires user input.
F-ES Sitecore 3-Jul-20 11:37am
   
Yeah, the "other error" is probably the one I've warned you about in this post :)
franciskane 3-Jul-20 11:40am
   
what do you mean when you say don't insert an value for out_ID?
F-ES Sitecore 3-Jul-20 11:44am
   
INSERT INTO attendanceout (out_ID, ID,

you are providing your own value for out_ID, but that column is controlled by the database, you let it select a value. So instead you do

INSERT INTO attendanceout (ID,

and update the "VALUES" close to remove the out_ID value from there too.
franciskane 3-Jul-20 12:00pm
   
I'll just clarify. (Sorry english is not my first language). I'll just remove the out_ID from the INSERT INTO?
F-ES Sitecore 3-Jul-20 12:36pm
   
Yes

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900