Syntax issue UPDATE works but not INSERT

Question

I have a dropdown lists which the default value is selected. If there is a value there the if statements allow an update to the data. If no value then instead a whole row will be inserted. It works if I hard code the third value as 1. However when I set this to $category_of_taxom1 the insert doesn't work. The Update works fine, so If I manually create the record within the DB, I can update it via the update SQL shown below. But if I try INSERT no luck? (I Have hard coded the first 3 items to be inserted, the third should be the variable mentioned above.

I have this select list

      <select name="categorySelect1fromDB" >
<option value ="">EMPTY</option>';
      <option value="1" <?php echo ($category_of_taxom1 == 1)?"selected":""; ?>>A</option>
      <option value="2" <?php echo ($category_of_taxom1 == 2)?"selected":""; ?>>B</option>
      <option value="3" <?php echo ($category_of_taxom1 == 3)?"selected":""; ?>>C</option>
      <option value="4" <?php echo ($category_of_taxom1 == 4)?"selected":""; ?>>D</option>
</select>

And this set of statements.

  if(isset($_POST['save']))
 {  
                  $category_of_taxom1 = $_POST['categorySelect1fromDB'];
                  $number_of_taxom1 = $_POST['number_of_taxom1'];

 if (!empty($category_of_taxom1)){ //This is the value fromDB if not empy then do below else do last

            pg_query("UPDATE record_tbl SET category_of_taxom ='$category_of_taxom1', number_of_taxom ='$number_of_taxom1' WHERE sheet_id = '$sheet_id' AND line = 1");
            echo "Updated!";

    } else 
            {

            pg_query("INSERT INTO record_tbl (line, taxom_id, category_of_taxom, number_of_taxom, sheet_id) VALUES (1,1,'$category_of_taxom1','$number_of_taxom1','$sheet_id')");

            echo "New Record ?Saved!";
}
}

This is an example of a working pgsql line I use else where in my site:

  $sql8 = "INSERT INTO record_tbl (line, taxom_id, category_of_taxom, number_of_taxom, sheet_id) VALUES (8,8,'$_POST[gammarus_numbers]','$_POST[countgammarus]','$sheetid')";
  $result = pg_query($sql8);

I will be switching this to PDO params once I have an example working. — Tom, Aug 08 '13 at 14:29
Put your values in single quotes like you've done for `$number_of_taxom1` — vee, Aug 08 '13 at 14:31
What database are you using? Does pg_query() mean PostgreSQL? Update: I have utilized the almighty power that is Google. So my question now becomes should the column names have single quotes around them? — MonkeyZeus, Aug 08 '13 at 14:34
Yes sorry I should have said that. I have edited the question to show a working example of this code elsewhere on my site. — Tom, Aug 08 '13 at 14:48
Update it is now working if i hard code the sheet_id. So its just a matter of me figuring out the ' ' I think. — Tom, Aug 08 '13 at 14:50

score 0 · Accepted Answer · edited May 23 '17 at 12:28

0

The datatype for the field 'category_of_taxom' is most likely set to varchar, meaning it should have quotes around the value when using INSERT. So you can either:

1) Change the datatype of category_of_taxom to integer in your database

2) Include quotes around the value in your INSERT statement:

('1','1','1','$number_of_taxom1',$sheet_id)

Related documentation: mysql - quote numbers or not?

edited May 23 '17 at 12:28

Community

1
1

answered Aug 08 '13 at 14:41

user1020317

654
5
22

It is set as a smallint? – Tom Aug 08 '13 at 14:42
No luck, when i use quotes or not. I updated the question. The insert only works if the 3rd value is hard coded. If i place quotes around everything and hard code it works, but if i replace the hard coded value with the variable it doesnt work – Tom Aug 08 '13 at 15:00
try using these quotes: ` around the field names in the INSERT – user1020317 Aug 08 '13 at 15:05
`pg_query("INSERT INTO record_tbl (line, taxom_id, category_of_taxom, number_of_taxom, sheet_id) VALUES (1,1,1,'$number_of_taxom1',$sheet_id)");` If I use this code, it works though the value is hard coded, however if i touch the dropdown when i click submit it skips out the whole if statement. Even though the if doesnt even refer to it – Tom Aug 08 '13 at 15:08
Did you try enclosing the table names in these quotes: ` ` – user1020317 Aug 09 '13 at 15:12

score 0 · Answer 2 · answered Aug 08 '13 at 15:15

I should probably delete this question: The quotes were correct, I needed them on each variable. However because the code was so long, i couldnt post it all on here. I had accidentally called that variable twice. So at one point of time it does have something in, but within the same code it may also not. So i ended up having to rename my variable. Not really a good answer, but going to delete it anyway. As this will be too narrow for anyone else.

Syntax issue UPDATE works but not INSERT

2 Answers2