PDO only saving if all parameters are full

Question

Hi I have an insert and an update statement. The update works fine, I have two fields, one category_of_taxom and number_of_taxom. The update allows either one to be changed and it will update the record.

However the insert which is pretty much the same, if it is not already existing creates the record. Will work if I input values for both options, but if only one is inputted it does not submit anything? The code is quite complicated, with all the variable names, but Im thinking I maybe need another if statement or something? to check if just one is inputted? or is this a PDO thing that all parameters need filling?

   $stmt6 = $conn ->prepare("UPDATE record_tbl SET category_of_taxom =?, number_of_taxom =? WHERE sheet_id = ? AND line = 6");
   $stmt6->bindParam(1, $category_of_taxom66);
   $stmt6->bindParam(2, $number_of_taxom66);
   $stmt6->bindParam(3, $sheet_id);
      $category_of_taxom66 = $_POST['categorySelect6fromDB'];
      $number_of_taxom66 = $_POST['number_of_taxom6'];
   $stmt6->execute();       
       echo "Saved!";
}
    else 
            {       
        if (isset($_POST['categorySelect6fromDB'])) {
         $category_of_taxom66 = $_POST['categorySelect6fromDB'];
         $param_cat = PDO::PARAM_INT;
 }
  else {
         $category_of_taxom66 = NULL;
         $param_cat = PDO::PARAM_NULL;
 }
    if (isset($_POST['number_of_taxom6'])) {
         $number_of_taxom66 = $_POST['number_of_taxom6'];
         $param_num = PDO::PARAM_INT;
 }
else {
         $number_of_taxom66 = NULL;
        $param_num = PDO::PARAM_NULL;
 }
 $stmt66 = $conn ->prepare("INSERT INTO record_tbl (line, taxom_id, category_of_taxom, number_of_taxom, sheet_id) VALUES (6,6,?,?,?)");
            $stmt66->bindParam(1, $category_of_taxom66, $param_cat);
            $stmt66->bindParam(2, $number_of_taxom66, $param_num);
            $stmt66->bindParam(3, $sheet_id);
          $stmt66->execute();
            echo "New Record Inserted!";        
   }
            }

Yes. Pseudocode: `if isset $var_x {bindParam $var_x}`. Also append to the query +1 placeholder — Royal Bg, Aug 09 '13 at 13:08
Could you please show an example of how that would be applied? @RoyalBg — Tom, Aug 09 '13 at 13:10
OK, let me try it with an answer, otherwise the code will be .. crap:) $sheet_id is the variable that isn't always set or what? — Royal Bg, Aug 09 '13 at 13:11
Sorry: Sheet ID is always set, however$category_of_taxom66 and $number_of_taxom66 may or may not be set some times. I have an if statement above it all. That checks that, as if nothing is set it skips the update or insert. But if one of those is set it needs adding. So parameters 1 and 2 can be both set, neither set or either or set. I have covered neither set, and both set works. — Tom, Aug 09 '13 at 13:13
If one is set - it should be inserted, the another one should be NULL? I have answered, see if it's the desired, if not - comment, I will edit the answer. — Royal Bg, Aug 09 '13 at 13:19

Royal Bg · Accepted Answer · 2013-08-09T13:34:23.697

1

I was thinking about several scenarios, and maybe later will edit the answer, if the one here is not the desired, but since the author said:

Sorry: Sheet ID is always set, however$category_of_taxom66 and $number_of_taxom66 may or may not be set some times. I have an if statement above it all. That checks that, as if nothing is set it skips the update or insert. But if one of those is set it needs adding.

It seems that the insert should happen, but what should happen with the value that doesn't set, it usually should became NULL?

$stmt66 = $conn ->prepare("INSERT INTO record_tbl (line, taxom_id, category_of_taxom, number_of_taxom, sheet_id) VALUES (6,6,?,?,?)");
         $stmt66->bindParam(1, $category_of_taxom66);
         $stmt66->bindParam(2, $number_of_taxom66);
         $stmt66->bindParam(3, $sheet_id);
         $category_of_taxom66 = isset($_POST['categorySelect6fromDB']) ? $_POST['categorySelect6fromDB'] : NULL;
         $number_of_taxom66 = isset($_POST['number_of_taxom6']) ? $_POST['number_of_taxom6'] : NULL;
         $stmt66->execute();

I think case the value of $category_of_taxom66 will be either the user input, if one is present, or NULL if not.

Another variation:

if (isset($_POST['categorySelect6fromDB'])) {
    $category_of_taxom66 = $_POST['categorySelect6fromDB'];
    $param_cat = PDO::PARAM_INT;
}
else {
    $category_of_taxom66 = NULL;
    $param_cat = PDO::PARAM_NULL;
}
if (isset($_POST['number_of_taxom66'])) {
    $number_of_taxom66 = $_POST['number_of_taxom66'];
    $param_num = PDO::PARAM_INT;
}
else {
    $number_of_taxom66 = NULL;
    $param_num = PDO::PARAM_NULL;
}
$stmt66 = $conn ->prepare("INSERT INTO record_tbl (line, taxom_id, category_of_taxom, number_of_taxom, sheet_id) VALUES (6,6,?,?,?)");
        $stmt66->bindParam(1, $category_of_taxom66, $param_cat);
        $stmt66->bindParam(2, $number_of_taxom66, $param_num);
        $stmt66->bindParam(3, $sheet_id);
        $stmt66->execute();

So, if category_taxom is populated by the user with "100", but number_taxom is not, it should produce:

        $stmt66->bindParam(1, 100, PDO::PARAM_INT);
        $stmt66->bindParam(2, NULL, PDO::PARAM_NULL);

The other way is to use '0' instead of 'NULL' in the first example:

         $category_of_taxom66 = isset($_POST['categorySelect6fromDB']) ? $_POST['categorySelect6fromDB'] : 0;
         $number_of_taxom66 = isset($_POST['number_of_taxom6']) ? $_POST['number_of_taxom6'] : 0;

edited Aug 09 '13 at 13:34

answered Aug 09 '13 at 13:17

Royal Bg

6,988
1
18
24

So this checks if there is a value in the variable, if not it sets to NULL and that enables the PDO query to insert it, skipping the missing value? – Tom Aug 09 '13 at 13:21
Yes, I hope it works, I think it's normal PDO to insert NULL values. Otherwise, you can set it to "0", if fits your requirements. I'm sure about that PDO can insert integers hehe :) – Royal Bg Aug 09 '13 at 13:23
No luck, If $category_of_taxom66 or $number_of_taxom66 equals a value it should INSERT, even if ONLY one of these is inputted. Will check my error log – Tom Aug 09 '13 at 13:26
I could probably create it into potentially 3 inserts, if X and Y set insert, Else is X set just insert X, if Y set just insert Y. But I have 8 update 8 insert, then that would ad another 16 inserts – Tom Aug 09 '13 at 13:28
try to set to 0, instead of NULL. But, yes, that's the logic, which I'm following. If $category... is set, but $number is not, it will insert the input for category and NULL for number – Royal Bg Aug 09 '13 at 13:29
Doesn't like that either just submitting nothing, I will edit my question with latest try – Tom Aug 09 '13 at 13:30
[Does this link mean anything](http://stackoverflow.com/questions/1391777/how-do-i-insert-null-values-using-pdo) – Tom Aug 09 '13 at 13:35
See my edit, but you might want to provide examples what your database wnat to be populated, when one is submited, but the other not – Royal Bg Aug 09 '13 at 13:35
Yes, I read this up before made the edit. It seems for bindParam you need to add PDO::PARAM_NULL, and a variable before that. For bindValue it seems to work with PDO::PARAM_INT. You can try all the variations? – Royal Bg Aug 09 '13 at 13:36
It works for categoryseletc6fromDB but not number_of_taxom, im just reading through it now – Tom Aug 09 '13 at 13:41
Could it be possible, that name field is with '66' not with '6'. See here: `$number_of_taxom66 = $_POST['number_of_taxom6']` maybe it should be `$number_of_taxom66 = $_POST['number_of_taxom66']` ? – Royal Bg Aug 09 '13 at 13:43
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/35145/discussion-between-tom-and-royal-bg) – Tom Aug 09 '13 at 13:44
I ended up getting this too work, however when I simplified the code to remove the first update, and just allow the user to insert, again both fields must be filled in to submit? http://stackoverflow.com/questions/18529533/dpo-only-submitting-when-both-fields-filled-in – Tom Aug 30 '13 at 09:31

PDO only saving if all parameters are full

1 Answers1