cant insert data into postgresDB in php

Question

im having trouble inserting my data's from textbox into postgresdb. my insert into tbl_ingredients is working fine but my insert into tbl_item is having a troubles can't figure it out how and where?

Connect();      

            $sql="INSERT INTO tbl_item VALUES('$itemname', '$highthreshold', '$lowthreshold', '$Qpunit', '$description', '$date');";
            $iteminfo = pg_query($sql);

            $sql1="SELECT MAX(itemid) as newid FROM tbl_item;";
            $iden_new = pg_query($sql1);
                $fetched_row = pg_fetch_row($iden_new,NULL,PGSQL_BOTH);
                $newid=$fetched_row['newid'];

            $sql2="INSERT INTO tbl_ingredient VALUES('$newid', '$Brandname');";
            $ingredients = pg_query($sql2);

            CloseDB();

            if(!$sql)
            {
            $sucmsg = "Successfully added new Item, ".ucfirst($itemname)."!";       
            echo $sucmsg;           
            }
            else
            {
            echo "error in saving data";
            }

table structure: tbl_item

itemid>itemname>highquantitythreshold>lowquantitythreshold>qntyperunit>Itemtype>description>dateadded

tbl_ingredient

itemid>brandname

im getting wamp "Warning: pg_query(): Query failed: ERROR: invalid input syntax for integer: "Strawberry" LINE 1: INSERT INTO tbl_item VALUES('Strawberry', '6', '3', '1300gra... ^ in D:\Wamp\wamp\www\Php\CTea\AddItem.php on line 247"

can someone lend me a helping hand thanks!.

What is your table structure for `tbl_item`? Does it have an `id` or other integer column for the first column? Since `'Strawberry'` is not an integer it is failing. May need to do `VALUES(NULL,'$itemname',...`. For the 2nd issue, change `if(!sql)` to `if(!$sql)`. — Sean, Mar 11 '14 at 23:08
my bad forget to put table structure also on if(!$sql)..about the Null do i need to put **Null** even if its serial data type on postgress? — blackmaler, Mar 11 '14 at 23:42
see my answer about using `NULL` or specifying the column name. — Sean, Mar 11 '14 at 23:55

score 0 · Accepted Answer · answered Mar 11 '14 at 23:55

0

You either need to use NULL ->

$sql="INSERT INTO tbl_item VALUES(NULL, '$itemname', '$highthreshold', '$lowthreshold', '$Qpunit', '$description', '$date');";

OR

You need to specify the columns you are inserting into ->

$sql="INSERT INTO tbl_item (itemname, highquantitythreshold, lowquantitythreshold, qntyperunit, description, dateadded)  VALUES('$itemname', '$highthreshold', '$lowthreshold', '$Qpunit', '$description', '$date');";

Without the NULL or column name, the database does not know that you are skipping the first column - itemid - so it will try to insert the 1st value into that column.

from the manual - http://www.postgresql.org/docs/9.1/static/sql-insert.html

The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first N column names, if there are only N columns supplied by the VALUES clause or query. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right.

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

answered Mar 11 '14 at 23:55

Sean

12,443
3
29
47

can i ask u 1 more thing how can i keep radio button checked? im using ` ` but every time i press submit it always goes back to its default mode (unchecked). – blackmaler Mar 12 '14 at 00:58
That should work as you have it. The only thing I can think of is to make sure the field name is correct, as I see that your column name in your query is `Itemtpe`. You could also check the html source code in the browser to make sure there is not any syntax errors. – Sean Mar 12 '14 at 01:18
my html code for radio. ` > ` from what i think nothing seems off but still not working. – blackmaler Mar 12 '14 at 01:33
oh my bad...i already get it working thanks for the help. what im having trouble now is that my radio button wont return checked after submit. – blackmaler Mar 12 '14 at 01:51
Based off your html code, I don't see why it is not working. You could also try just `checked` -> ` >` (although does not work with XHTML) see http://stackoverflow.com/a/7852002/689579 – Sean Mar 12 '14 at 01:59
still wont return checked... :( im getting pissed on this radio thing for a while now, ive tried every possible way i know but still wont return checked. – blackmaler Mar 12 '14 at 02:53
can help me with this too? http://stackoverflow.com/questions/22347677/how-to-properly-make-drop-down-list-menu-for-my-tbl-item-in-php?noredirect=1#comment33966008_22347677 – blackmaler Mar 12 '14 at 12:22

cant insert data into postgresDB in php

1 Answers1