The string inserted into the database is not complete

Question

I have a very interesting issue. Only part of a value is being entered into my database.

I have a page that contains a form. The user will fill out the form and submit. All the POST values are correct and are submitting to the database. One paticular value ($criteria) is only submitting the last part of the string. If I echo the value of $criteria it is all there but, when I view it in mysqlAdmin only part of it is there.

It should be submitting:

Communication | 1 | dsafadf | Customer Service | 2 | asdfadf | Dependability | 3 | asdfadsf | Initiative | 4 | dsafadsfa | Job Knowledge | 5 | dsadafsadsf | Judy | 1 | asdfasdf | Punctuality | 2 | asdfdasdfadsf |

But in the database I only get - | Punctuality | 2 | asdfdasdfadsf |

I have tried to manually add the value using mysqlAdmin and it works fine.

If there is additional information need to help I will add.

Thanks Everyone!!

---

Here is my insert script:

---

      public function insertReview() {
      $fk_employee = $_POST['fk_employee'];

      // Current Date returned from JQuery and formatted to add to DB.
      $cdate = $_POST['current_date'];
      $current_date = explode("/", $cdate);
        $cmonth = $current_date[0];
        $cday = $current_date[1];
        $cyear = $current_date[2];
        $current_dateA = array($cyear, $cmonth, $cday);
      $review_date = implode("-", $current_dateA);

      // Review Begin Date returned from JQuery Datepicker and formatted to add to DB.            
      $bdate = $_POST['r_period_begin'];
      $begin_date = explode("/", $bdate);
        $bmonth = $begin_date[0];
        $bday = $begin_date[1];
        $byear = $begin_date[2];
        $begin_dateA = array($byear, $bmonth, $bday);
      $r_period_begin = implode("-", $begin_dateA);

      // Review End Date returned from JQuery Datepicker and formatted to add to DB.
      $edate = $_POST['r_period_end'];
      $end_date = explode("/", $edate);
        $emonth = $end_date[0];
        $eday = $end_date[1];
        $eyear = $end_date[2];
        $end_dateA = array($eyear, $emonth, $eday);
      $r_period_end = implode("-", $end_dateA);

    // Criteria 
      $criterias = $_POST['criteria'];
      //var_dump($criterias);
      $criteriaValue = $_POST['criteriaValue'];
      //var_dump($criteriaValue);
      $comments = $_POST['Comments'];

      foreach ($criteriaValue as $key => $value ){
          foreach( $criterias as $criteria ){
              if( $criteria == $key ){
                  $string1 = $key;
                  foreach( $comments as $comment => $comm ){
                      if( $string1 == $comment ){
                          $string3 = $comm;
                      }
                  }
              }
          }
          //echo $key . '<br>';
          foreach ( $value as $result ){
              $string2 = $result;
          }

      $criteria = mysql_real_escape_string( $string1 . ' | ' . $string2 . ' | ' . $string3 . ' | ' );
      echo $criteria;
      }
      // End of Criteria

      $job_knowledge = $_POST['job_knowledge'];
      $jk_comments = $_POST['jk_comments'];
      $work_quality = $_POST['work_quality'];
      $wq_comments = $_POST['wq_comments'];
      $attendance = $_POST['attendance'];
      $attend_comments = $_POST['attend_comments'];
      $initiative = $_POST['initiative'];
      $init_comments = $_POST['init_comments'];
      $communication = $_POST['communication'];
      $comm_comments = $_POST['comm_comments'];
      $dependability = $_POST['dependability'];
      $depend_comments = $_POST['depend_comments'];
      $customer_service = $_POST['customer_service'];
      $cs_comments = $_POST['cs_comments'];
      $overall_rating = $_POST['overall_rating'];
      $additional_comments = $_POST['additional_comments'];
      $goals = $_POST['goals'];

    $conn = parent::connect();
    $sql = "INSERT INTO " . TBL_EMPLOYEE_REVIEW . " (
              fk_employee,
              review_date,
              r_period_begin,
              r_period_end,
              job_knowledge,
              jk_comments,
              work_quality,
              wq_comments,
              attendance,
              attend_comments,
              initiative,
              init_comments,
              communication,
              comm_comments,
              dependability,
              depend_comments,
              customer_service,
              cs_comments,
              overall_rating,
              additional_comments,
              goals,
              criteria
            ) VALUES (
              :fk_employee,
              :review_date,
              :r_period_begin,
              :r_period_end,
              :job_knowledge,
              :jk_comments,
              :work_quality,
              :wq_comments,
              :attendance,
              :attend_comments,
              :initiative,
              :init_comments,
              :communication,
              :comm_comments,
              :dependability,
              :depend_comments,
              :customer_service,
              :cs_comments,
              :overall_rating,
              :additional_comments,
              :goals,
              :criteria
            )";

    try {
      $st = $conn->prepare( $sql );
      $st->bindValue( ":fk_employee", $fk_employee, PDO::PARAM_STR );
      $st->bindValue( ":review_date", $review_date, PDO::PARAM_STR );
      $st->bindValue( ":r_period_begin", $r_period_begin, PDO::PARAM_STR );
      $st->bindValue( ":r_period_end", $r_period_end, PDO::PARAM_STR );
      $st->bindValue( ":job_knowledge", $job_knowledge, PDO::PARAM_STR );
      $st->bindValue( ":jk_comments", $jk_comments, PDO::PARAM_STR );
      $st->bindValue( ":work_quality", $work_quality, PDO::PARAM_STR );
      $st->bindValue( ":wq_comments", $wq_comments, PDO::PARAM_STR );
      $st->bindValue( ":attendance", $attendance, PDO::PARAM_STR );
      $st->bindValue( ":attend_comments", $attend_comments, PDO::PARAM_STR );
      $st->bindValue( ":initiative", $initiative, PDO::PARAM_STR );
      $st->bindValue( ":init_comments", $init_comments, PDO::PARAM_STR );
      $st->bindValue( ":communication", $communication, PDO::PARAM_STR );
      $st->bindValue( ":comm_comments", $comm_comments, PDO::PARAM_STR );
      $st->bindValue( ":dependability", $dependability, PDO::PARAM_STR );
      $st->bindValue( ":depend_comments", $depend_comments, PDO::PARAM_STR );
      $st->bindValue( ":customer_service", $customer_service, PDO::PARAM_STR );
      $st->bindValue( ":cs_comments", $cs_comments, PDO::PARAM_STR );
      $st->bindValue( ":overall_rating", $overall_rating, PDO::PARAM_STR );
      $st->bindValue( ":additional_comments", $additional_comments, PDO::PARAM_STR );
      $st->bindValue( ":goals", $goals, PDO::PARAM_STR );
      $st->bindValue( ":criteria", $criteria, PDO::PARAM_STR );

      $st->execute();
      parent::disconnect( $conn );
    } catch ( PDOException $e ) {
      parent::disconnect( $conn );
      die( "Query failed: " . $e->getMessage() );
    }
  } 

Answer 1

You have multiple logic errors in your loop which sets the value for $criteria: you try to loop on $value without needing to; if you could, it would always leave $string2 set as the final $value. You also have $criteria both as the holder for your second inner foreach loop and what you want to use. Finally you are setting $critera with each loop, instead of appending to it, and your output is misleading because you are echoing as you loop, rather than echoing the final result of $criteria from outside the loop:

 foreach ($criteriaValue as $key => $value ){
          foreach( $criterias as $criteria ){
              if( $criteria == $key ){
                  $string1 = $key;
                  foreach( $comments as $comment => $comm ){
                      if( $string1 == $comment ){
                          $string3 = $comm;
                      }
                  }
              }
          }
          //echo $key . '
';
          foreach ( $value as $result ){
              $string2 = $result;
          }

      $criteria = mysql_real_escape_string( $string1 . ' | ' . $string2 . ' | ' . $string3 . ' | ' );
      echo $criteria;
      }

Should be:

foreach ($criteriaValue as $key => $value ){
  foreach( $criterias as $criteriasValue ){
    if( $criteriasValue == $key ){
      $string1 = $key;
      foreach( $comments as $comment => $comm ){
        if( $string1 == $comment ){
          $string3 = $comm;
        }
      }
    }
  }

  $string2 = $result;

  $criteria .= mysql_real_escape_string( $string1 . ' | ' . $string2 . ' | ' . $string3 . ' | ' );

}

echo $criteria;

Answer 2

This looks like it might be the culprit:

$criteria = mysql_real_escape_string( $string1 . ' | ' . $string2 . ' | ' . $string3 . ' | ' );

It's at the end of the foreach() loop:

foreach ($criteriaValue as $key => $value ){
    ...

    $criteria = mysql_real_escape_string( $string1 . ' | ' . $string2 . ' | ' . $string3 . ' | ' );
}

You're inserting that value directly into the DB here:

$st->bindValue( ":criteria", $criteria, PDO::PARAM_STR );

But your loop is always creating a string from the last iteration of the loop. It doesn't appear to be concatenating the previous values. You prob. need something like this:

$criteria .= mysql_real_escape_string( $string1 . ' | ' . $string2 . ' | ' . $string3 . ' | ' );

(notice the ".=")

However, you're using $critera in your nested foreach loop as well, so you'll have to adjust that bit as well. Something like this should be easy enough:

foreach( $criterias as $crit){
          if( $crit == $key ){
....

I hope that helps!