将可变数量的参数传递给PDO语句并返回JSON obj

This function takes an array of integers

  $this->grades

this array varies in size depending on what the user inputs. I can get it to work perfectly with only one number, but when I try more than one I run into the problem. Do I need to somehow concatenate the responses together before encoding them? Or is there a more efficient way to run this?

 private function retrieve_standards_one(){
    $dbh = $this->connect();
    for($x = 0; $x < (count($this->grades)); $x++){
    $stmt = $dbh->prepare("SELECT code, standard_one_id 
                           FROM standard_one 
                           WHERE grade_id = :grade_id 
                           ORDER BY standard_one_id");
    $stmt->bindParam(':grade_id', $this->grades[$x], PDO::PARAM_STR);
    $stmt->execute();
    $stnd = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    $json = json_encode($stnd);
    return $json;
}

Just use an array to store the results and encode the array

 private function retrieve_standards_one(){
    $dbh = $this->connect();
    $data = array();
    for($x = 0; $x < (count($this->grades)); $x++){
    $stmt = $dbh->prepare("SELECT code, standard_one_id 
                           FROM standard_one 
                           WHERE grade_id = :grade_id 
                           ORDER BY standard_one_id");
    $stmt->bindParam(':grade_id', $this->grades[$x], PDO::PARAM_STR);
    $stmt->execute();
    $data[] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    $json = json_encode($data);
    return $json;
}

The problem is this:

$stnd = $stmt->fetchAll(PDO::FETCH_ASSOC);

Each time you go through the loop, you overwrite the contents of $stnd. So yes, in order for it to work properly, you'd need to instead append each individual result to an overall array, and then encode the array.

Here's a rewritten version of your function that both utilizes an array and also doesn't unnecessarily re-prepare the query each loop iteration:

private function retrieve_standards_one(){
    $dbh = $this->connect();
    $stmt = $dbh->prepare("SELECT code, standard_one_id 
                           FROM standard_one 
                           WHERE grade_id = :grade_id 
                           ORDER BY standard_one_id");
    $stnd = array();
    for($x = 0; $x < (count($this->grades)); $x++){
        $stmt->bindParam(':grade_id', $this->grades[$x], PDO::PARAM_STR);
        $stmt->execute();
        $stnd[] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    return json_encode($stnd);
}

Note: the $stnd = array(); line is not strictly necessarily, but it'll prevent things from crashing if $this->grades ever has 0 elements in it.

If you’re wanting to pass an array of numbers, then you can do this:

<?php

$grades = array(1,2,3,4,5);

private function retrieve_standards_one($grades)
{
    // ensure only numbers get into the SQL statement
    $grade_ids = array();
    foreach ($grades as $grade) {
        if (is_numeric($grade)) {
            $grade_ids[] = $grade;
        }
    }
    $grade_ids = implode(',', $grade_ids);

    $dbh = $this->connect();
    $sql = "SELECT code, standard_one_id
            FROM standard_one
            WHERE grade_id IN ($grade_ids)
            ORDER BY standard_one_id";
    $stmt = $dbh->query($sql);
    $stmt->execute();
    $stnd = $stmt->fetch(PDO::FETCH_ASSOC);
    $json = json_encode($stnd);
    return $json;
}