CODEWITHSUNDEEP
php
chris b
chris b

Reputation: 64

i am trying to bind array value a prepared statement in php pdo but only the first element of the array that is bind

I wish to show a time table and below is the code snippet where I suspect the error is. The error therein is that the bind is using only the first element of the array I have named class though there are two elements in the class array. Please can anyone help me figure out where I am going wrong?

while($classes = $stmt->fetch(PDO::FETCH_ASSOC)){

       $showtimetable.= "{\"class\":\"";
       $showtimetable.= $classes["Form"].$classes["Label"];
       $showtimetable.= "\",\"subjects\":[";

      //iterate every class in different times to check if they are having a session in the timetable
           foreach($time as $times){

             $selectlessonquery="SELECT `staffnumber`, `subject` FROM `".$timetablename."` WHERE 
            `Form`=? AND `Label`=? AND daytime=?"; 
                 try{ 
                        //this variable contain the array of variable classes
                         echo $classes["Form"].$classes["Label"];
                        //this works fine with output of classes as 1E and IN
                     //on adding these code that will use the value in $classes  only 1E it binding 
                       in prepared statement
                           $stmt=$conn->prepare($selectlessonquery);
                           $stmt->bindParam(1,$classes["Form"]);
                           $stmt->bindParam(2,$classes["Label"]);
                           $stmt->bindParam(3,$times);
                           $stmt->execute();
                           while($subject = $stmt->fetch(PDO::FETCH_ASSOC)){
                               if(!$subject){
                                  //if no matching row then make the value to be empty string
                                   $showtimetable.= "{\"subject\":\"";
                                   $showtimetable.= " \"";
                                   $showtimetable.= " "."},";  
                               }else{
                                   $showtimetable.= "{\"subject\":\"";
                                   $showtimetable.= $subject["subject"]." ";
                                   $showtimetable.= $subject["staffnumber"]."\"},";
                               }  

                           }
                      }catch(Exception $e){

                          echo "error Ocuured ". $e->getMessage();

                      }
                 }

                  $showtimetable=rtrim($showtimetable,",");
                  $showtimetable.="]";
                 $showtimetable.="},";

             }
      echo $showtimetable
 //the output contains on data relevant to 1E and it isnt looping to the other value as expected

Upvotes: 1

Views: 63

Answers (2)

chris b
chris b

Reputation: 64

the problem was the positioning of if inside a while causing the code to stop unexpectedly even though it was intended to supply the default value for subject in cases where there was no row fetched. the correct way was

      foreach($time as $times){
         $selectlessonquery="SELECT `staffnumber`, `subject` FROM `".$timetablename."` 
          WHERE `Form`=? AND `Label`=? AND daytime=?"; 
              try{ 

                   //this variable contain the array of variable classes
                   $subjects=$conn->prepare($selectlessonquery);
                   $subjects->bindParam(1,$classes["Form"]);
                   $subjects->bindParam(2,$classes["Label"]);
                   $subjects->bindParam(3,$times);
                   $subjects->execute();

                   if($subjects->rowCount()==0){
                       $showtimetable.= "{\"subject\":\"";
                       $showtimetable.= " \"";
                      $showtimetable.= " "."},";  
                  }else{
                    while($subject=$subjects->fetch(PDO::FETCH_ASSOC)){

                         $showtimetable.= "{\"subject\":\"";
                         $showtimetable.= $subject["subject"]." ";
                        $showtimetable.= $subject["staffnumber"]."\"},";
                   }  

               }

          }catch(Exception $e){

                echo "error Ocuured ". $e->getMessage();

          }
       }

Upvotes: 0

Towsif
Towsif

Reputation: 320

You must do this way. Because the second argument must be passed in bindParam() by reference.

$table = 'users';
$sql = "INSERT INTO `{$table}` (username, password, name, age) VALUES(?, ?, ?, ?)";

$stmt = $pdo->prepare($sql);
$stmt->bindParam(1, $username);
$stmt->bindParam(2, $password);
$stmt->bindParam(3, $name);
$stmt->bindParam(4, $age);

$username = 'zill';
$password = '12345';
$name = 'zilanicse';
$age = 31;

// Executes the query
$stmt->execute();

Upvotes: 2

Related Questions