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 )

Help us help you - share some sample data and the result you'd like to get for it — Mureinik, Sep 11 '20 at 11:06
In order to help we need to be able to read and understand the code. Thanks @NigelRen — RiggsFolly, Sep 11 '20 at 11:06
Why it should return any data? What data do you have in the tables? Why you need to join any other tables if only columns from `a` are selected? — AterLux, Sep 11 '20 at 11:07
@AterLux table b and c are the children tables of table a I want to check condition with table b and c and fetch data from table a. — lipon, Sep 11 '20 at 11:10
@lipon are you 100% sure you have data which achieves all your conditions? That's a lot of conditions to pass — imposterSyndrome, Sep 11 '20 at 11:23
also are these meant to be strings? AND b.stock_status != '2' AND c.stock_status != '2' — imposterSyndrome, Sep 11 '20 at 11:24
@jameson2012 Yes and its working properly with joining table a and b . But when i add table c then its returning empty result. — lipon, Sep 11 '20 at 11:27
so either a) ``a.id = c.pat_id`` there is not a matching row in both of these, b)``(c.total_qty - (c.given+c.not_taken))`` is not less than 12 ,c) ``AND '$full_dt' BETWEEN c.start_date AND c.end_date `` .... can you see where i'm going with this..? — imposterSyndrome, Sep 11 '20 at 11:30
@jameson2012 both tables have 1,1 row between these conditions. And its working properly when i remove table b or c from it. — lipon, Sep 11 '20 at 11:33
I can't debug your code for you, you haven't shown us the data in your database or the structure so there's not much I can add. You can join as many tables as you like in a query, and the way you have joined them is fine in itself. So the only logical conclusion is that one or other of your conditions (the AND) statements fails or the data you are joining on isn't there, or isn't as you expect. Looks like you need to pick you query apart one condition at a time until you find what isn't working the way you thought. It's nearly never the query that's wrong, and almost always the programmer — imposterSyndrome, Sep 11 '20 at 11:37
@jameson2012 Now i realized that the problem is in WHERE clause. The query checking conditions for `prn_medication c` ignoring conditions for `b`. — lipon, Sep 11 '20 at 14:12
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. — philipxy, Sep 11 '20 at 17:26
Do `print_r($inQuery);` and `print_r($codeArray);` near the end so we can check the contents. — Rick James, Sep 13 '20 at 15:05
I don't know why you commented to me since you clearly haven't done everything in my first comment. PS Debug in one level at a time. Show SQL works. Show your php inputs & outputs SQL correctly. Show what actually is input & output for particular SQL. Etc. — philipxy, Sep 13 '20 at 17:09
@lipon Please post TEXT results of SHOW CREATE TABLE x; for each of your tables used for a, b and c. We will likely find a data type mismatch for you to consider. — Wilson Hauck, Sep 13 '20 at 21:27
What was `$code` when you started? Sounds like it was some structure, not a string?? — Rick James, Sep 13 '20 at 22:18

score 1 · Accepted Answer · answered Sep 16 '20 at 21:16

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();

Thank you sir, It works fine that way. But one more issue , I have added one more query to UNION All .It checks only last row for the date between and counts all rows for one result.[https://pastebin.com/gXgBEiJi](https://pastebin.com/gXgBEiJi) — lipon, Sep 17 '20 at 06:57
@lipon Nice i could help you! I think you only need to add a variable and `bindParam` for "prn". One thing i don't know is if you need `UNION ALL` or `UNION DISTINCT` in this one. `UNION ALL` will give duplicates also in the result but is faster. — , Sep 17 '20 at 07:26
I tried UNION and UNION DISTINCT, but the result is same. Its checking the date between column for only last row. of the prn table. — lipon, Sep 17 '20 at 07:34
@lipon On the last `UNION ALL` i don't see a `JOIN` with `prn` table such as this one `INNER JOIN periods g ON e.id = g.pat_id AND f.id = g.drug_id`. Perhaps this is a new `MySQL` tagged question you should post. Provide there more details on the actual result you are trying to achieve by specifying more details on the tables also. — , Sep 17 '20 at 07:56

score 0 · Answer 2 · answered Sep 13 '20 at 22:29

The issue should be that the number of '?' does not match the number of parameters you are passing.

Here in "$statement->execute($codeArray)" $codearray should have the same number of elements as the number of comma separated '?' in $inQuery. What you shared with "With print_r($inQuery); result ?,??,?" Looks funny, shouldn't it be a comma separeated sequence of question marks?

score 0 · Answer 3 · answered Sep 17 '20 at 02:07

Bind all the parameters

Take a look at $full_dt = date('Y-m-d H:i:s');

Let's echo the query and look a bit closer at this very part:

...
...
AND '2020-09-16 18:57:31' BETWEEN d.start_date AND d.end_date
...
...

Then we go to the docs: https://www.php.net/manual/en/pdostatement.bindparam.php#refsect1-pdostatement.bindparam-parameters

Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.

Can you see two uninvited guests :57 and :31 that coming from :i and :s from date('Y-m-d H:i:s')?

They are expected to be bound to some data too.

score -1 · Answer 4 · answered Sep 11 '20 at 11:14

-1

INNER JOIN return result when it has data in each table. Meaning that the return lines will be the ones that have at least one entry on each table sharing a.id, b.pat_id, and c.pat_id.

May be you more interest in LEFT JOIN, that will return all of the entries of pat_info extended with pat_medication and prn_medication wherever you do have data or don't in these two tables.

https://dev.mysql.com/doc/refman/5.7/en/join.html

answered Sep 11 '20 at 11:14

SeeoX

565
3
18

Can you describe the wanted behavior of your SQL request? – SeeoX Sep 11 '20 at 11:23

score -1 · Answer 5 · answered Sep 11 '20 at 11:23

-1

select * from tableA a inner join tableB b on a.common = b.common inner join TableC c on b.common = c.common

answered Sep 11 '20 at 11:23

Rajesh Kumar Yadav

1
1

Array binding issue with UNION ALL

5 Answers5

Bind all the parameters