Stuck at this error: Incorrect integer value: '' for column '____' at row 1

Question

When I submit the form and use this script to insert the data in the db i get the error mentioned above...any ideas?

        //Include connect file to make a connection to test_cars database
        include("prototypeconnect.php");


        $proCode            =       $_POST["code"];
        $proDescr           =       $_POST["description"];
        $proManu            =       $_POST["manufacturer"];
        $proCPU             =       $_POST["cost_per_unit"];
        $proWPU             =       $_POST["weight_per_unit"];
        $proBarCode         =       $_POST["bar_code"];
        $proIngredients     =       $_POST["ingredients_list"];
        $proAllergens       =       $_POST["allergens_contains"];
        $proMayAllergens    =       $_POST["allergens_may_contain"];

        //Insert users data in database
        $sql = "INSERT INTO prodb.simplex_list 
                   code, description, manufacturer, 
                   cost_per_unit, weight_per_unit, bar_code,
                   ingredients_list, allergens_contains,
                   allergens_may_contain) 
                VALUES 
                  ( '$proCode', '$proDescr' , '$proManu', 
                   '$proCPU' , '$proWPU' , '$proBarCode', 
                   '$proIngredients' , '$proAllergens', 
                   '$proMayAllergens')";

        //Run the insert query

        if (!mysql_query($sql)) {
            echo mysql_error();
        }

    ?>

UPDATE: I removed id inserts as they are auto-increment and i learned from your answers that a null does not need to be coded and mysql looks after AI. Thanks guys!

remove single quotes from $id in insert query. Also check if filed is type integer in db then not to pass single quotes. — Disha V., Jul 29 '15 at 11:32
@DishaV. actually my id is auto incrementing so i removed that...still same error — Cian Woods, Jul 29 '15 at 11:35
I find it helpful if you can echo the complete sql before attempting to insert /update etc if there are any problems. Then try that command directoy in your mySql client application (heidi etc) - just a thought — Professor Abronsius, Jul 29 '15 at 11:35
execute this statement on mysql server either directly or from any gui like sqlyog etc and check what error it produce. — Zafar Malik, Jul 29 '15 at 11:37
This depends upon your MySQL table column definitions. There is at least one field which is defined as `INT` and you are inserting non-integer/string value there. — Pupil, Jul 29 '15 at 11:37
@CianWoods Can you share create table simplex_list query with data type? — alagu, Jul 29 '15 at 11:41
Cian was your `id` field defined as `auto increment`? If so you normally leave those auto inrement fields out of the INSERT query as they are looked after by MySQL itself. **Changing the datatype just masks the issue it does not fix it** _Likely as not you will later have problems with using integer values that have been stored as text._ — RiggsFolly, Jul 29 '15 at 11:45
@CianWoods Just by changing the INT to Varchar won't be a right solution. Perhaps you have to use INT in some cases based on your programming logic. Please explain what were your fields used INT? — alagu, Jul 29 '15 at 12:16
@kuttyraj i was using cost_per_unit and weight_per_unit as int — Cian Woods, Jul 29 '15 at 13:31
@RiggsFolly yes ID is auto-increment...have no idea how to fix it? — Cian Woods, Jul 29 '15 at 13:40
Just leave the `id` column off the column list and also remove the `'$proId' ,` from the parameter list. MySQL will take care of creating that column for you and ensuring its uniqueness. See @AK-Sonu answer — RiggsFolly, Jul 29 '15 at 13:42
@RiggsFolly yes, removed the id variables and such and just changed back types to int and it working now. Thank you! — Cian Woods, Jul 29 '15 at 13:48
Dont forget to mark @AK-Sonu answer as correct. I have edited out the mistake and formatted it so its a bit more readable — RiggsFolly, Jul 29 '15 at 13:48
@RiggsFolly yep just marked it :) he says its bad to use mysql_* instead of mysql_* but is it ok to use mysql_error()? or should mysql_error() work? — Cian Woods, Jul 29 '15 at 13:51
As you are obviously just learning, I suggest you start by learning either the `mysqli_` or `PDO` database access extensions. The `mysql_` extensions are dead and have been removed completely from the soon to be released PHP7. Unfortunately most tutorials on the web use `mysql_` extensions, but you just have to ignore them completely from now on — RiggsFolly, Jul 29 '15 at 13:52
Unfortunately `mysqli` and `pdo` are completely different. You cannot use anything out of the `mysql_*` set of instructions. They are completely different but now is the time to make the effort to learn these new extensions. Other wise you are wasting your time learning the `mysql_*` extension — RiggsFolly, Jul 29 '15 at 13:55
_Never pass your parmeters in your SQL query without escaping them!_ It's a severe security issue, with that code you use anyone can run a query in your database through the form. — vard, Jul 29 '15 at 13:55
[This post is a VERY GOOD READ](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) and shoud help you make your mind up to move and what to move to. — RiggsFolly, Jul 29 '15 at 13:58
@CianWoods When you do this `$proCode = $_POST["code"];` then this `VALUES ('$proCode'` you pass your POST parameter directly into the query without escaping it. If I put something like this `);DROP TABLE prodb.simplex_list` in a form field it will delete your table. You can escape the POST datas by using [`mysqli_escape_string`](http://php.net/manual/en/function.mysqli-escape-string.php). — vard, Jul 29 '15 at 14:06
@vard ah wow! I should ALWAYS use mysqli_escape_string so when using POST from forms.. — Cian Woods, Jul 29 '15 at 14:09
@CianWoods Personally I like to use [prepared queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php), that way parameters are always escaped, so I don't have to worry about them. And when you're passing an integer, you can parse it as int in that way: `$my_integer = (int) $my_integer` — vard, Jul 29 '15 at 14:17

score 0 · Accepted Answer · edited Jul 29 '15 at 13:45

0

Query need to be like:-

 $sql = "INSERT INTO prodb.simplex_list 
         (code, description, manufacturer, 
          cost_per_unit, weight_per_unit, 
          bar_code, ingredients_list, allergens_contains, 
          allergens_may_contain) 
         VALUES ('$proCode', '$proDescr', '$proManu',
                 '$proCPU','$proWPU', '$proBarCode', 
                 '$proIngredients', '$proAllergens', 
                 '$proMayAllergens')";

Note:- please stop using mysql_*. Use mysqli_* or PDO. Also this will work only when id field must be auto incremented.

edited Jul 29 '15 at 13:45

RiggsFolly

93,638
21
103
149

answered Jul 29 '15 at 11:40

Alive to die - Anant

70,531
10
51
98

Hi, please remove id from insert. as you dont pass the prodid. – alagu Jul 29 '15 at 12:08
Hi, No need to use id. Remove that field. As well as remove empty ' ' parameter inside VALUES(). Like this, $sql = "INSERT INTO prodb.simplex_list (code, description, manufacturer, cost_per_unit, weight_per_unit, bar_code, ingredients_list, allergens_contains, allergens_may_contain) VALUES ('".$proId."' , '".$proCode."', '".$proDescr."' , '".$proManu."' , '".$proCPU."' ,'".$proWPU."' , '".$proBarCode."' , '".$proIngredients."', '".$proAllergens."', '".$proMayAllergens."')"; – alagu Jul 29 '15 at 12:13
@AK-Sonu why should i stop using mysql_* instead of mysqli? causes more errors saying I'm missing parameters... – Cian Woods Jul 29 '15 at 13:34
because now `mysql_*` is deprecated library officially. – Alive to die - Anant Jul 29 '15 at 13:35
Hope you dont mind I edited the `'$proId' ,` out of the parameter list and removed all the unnecessary concatenation, and formatted it so it can be read easily – RiggsFolly Jul 29 '15 at 13:46
No you have to stop use any function related to `mysql_*`. – Alive to die - Anant Jul 29 '15 at 13:50
@AK-Sonu it expects one parameter, what parameter do i pass it? – Cian Woods Jul 29 '15 at 13:53
check it on `php.net` site. you will get complete reference for it. check this link:- http://php.net/manual/en/function.mysql-query.php – Alive to die - Anant Jul 29 '15 at 15:11

Stuck at this error: Incorrect integer value: '' for column '____' at row 1

1 Answers1

Linked