Array binding issue with UNION ALL

Question

I am trying to join multiple queries with UNION ALL.

I tried the following code. which is giving error of Invalid parameter number: number of bound variables does not match number of tokens.

$code = '1,2,3,4';
$codeArray = explode(',', $code);
$inQuery = implode(',', array_fill(0, count($codeArray), '?'));    
$full_dt = date('Y-m-d H:i:s');
$start_date = "2020-08-28 14-44-23";
$medication = "OD";
    $query = "SELECT SUM(counts) AS allcounts FROM
  (SELECT COUNT(b.id) AS counts
   FROM pat_info a
   INNER JOIN pat_medication b ON a.id = b.pat_id
   WHERE a.status != 2
     AND b.status != 2
     AND b.directions = '$medication'
     AND b.last_med_time < '$start_date'
     AND '$full_dt' BETWEEN b.start_date AND b.end_date
     AND a.location_code IN($inQuery)
   UNION ALL SELECT COUNT(d.id) AS counts
   FROM pat_info c
   INNER JOIN prn_medication d ON c.id = d.pat_id
   WHERE c.status != 2
     AND d.status != 2
     AND d.dose_frequency = '$medication'
     AND d.last_med_time < '$start_date'
     AND '$full_dt' BETWEEN d.start_date AND d.end_date
     AND c.location_code IN($inQuery) ) x ";
    $statement = $conn->prepare($query);
    $codeArray = array_merge($codeArray, $codeArray);
    $statement->execute($codeArray);

With print_r($inQuery); result ?,??,?

With print_r($codeArray);

before array_merge($codeArray, $codeArray);

result Array ( [0] => 1 [1] => 2 ) Array ( [0] => 1 [1] => 2 [2] => 1 [3] => 2 )

With print_r($codeArray);

After array_merge($codeArray, $codeArray);

result Array ( [0] => 1 [1] => 2 [2] => 1 [3] => 2 ) Array ( [0] => 1 [1] => 2 [2] => 1 [3] => 2 [4] => 1 [5] => 2 [6] => 1 [7] => 2 )

Answer 1

I think in this occasion it is a good tactic to use bindParam with named parameters, for example:

$code = '1,2,3,4';
$full_dt = date('Y-m-d H:i:s');
$start_date = "2020-08-28 14-44-23";
$medication = "OD";
    $query = "SELECT SUM(counts) AS allcounts FROM
  (SELECT COUNT(b.id) AS counts
   FROM pat_info a
   INNER JOIN pat_medication b ON a.id = b.pat_id
   WHERE a.status != 2
     AND b.status != 2
     AND b.directions = :medication
     AND b.last_med_time < :start_date
     AND :full_dt BETWEEN b.start_date AND b.end_date
     AND FIND_IN_SET(a.location_code, :code)>0
   UNION ALL SELECT COUNT(d.id) AS counts
   FROM pat_info c
   INNER JOIN prn_medication d ON c.id = d.pat_id
   WHERE c.status != 2
     AND d.status != 2
     AND d.dose_frequency = :medication
     AND d.last_med_time < :start_date
     AND :full_dt BETWEEN d.start_date AND d.end_date
     AND FIND_IN_SET(c.location_code, :code)>0 ) x ;";
    $statement = $conn->prepare($query);
    $statement->bindParam(':code', $code, PDO::PARAM_STR);
    $statement->bindParam(':full_dt', $full_dt, PDO::PARAM_STR);
    $statement->bindParam(':start_date', $start_date, PDO::PARAM_STR);
    $statement->bindParam(':medication', $medication, PDO::PARAM_STR);
    $statement->execute();