Cannot use positional arguments after argument unpacking while preparing a statement

Question

I am trying to bind a variable number of values into the IN () condition of my prepared statement AND bind a few more values later in the query, but I am getting an error:

PHP Fatal error: Cannot use positional argument after argument unpacking.

My code is as follows :

$ccarr=explode(",", $cc);
$in = str_repeat('?,', count($ccarr) - 1) . '?';
$op_r=$tfvarr[2]; 
$budg_et=$tfvarr[1];
$budg_et1=$tfvarr111111;

$sqldesk="SELECT subsubcatid_parent, plink, deskid, sum(itprice) as totprice FROM desktop_items a, items_table b, obsubsubcat c where subsubcatid_parent IN ($in) and a.itno = b.itno and a.subsubcatid_parent=c.subsubcatid group by subsubcatid_parent, deskid having totprice > ? && totprice ? ?" ;

if($stmtdesk = $conn->prepare($sqldesk))
{
    $types = str_repeat('i', count($ccarr));
    $types .= 'isi';
    $stmtdesk->bind_param($types, ...$ccarr, $budg_et1, $op_r, $budg_et);
    $stmtdesk->execute();
    $stmtdesk->store_result();
    $stmtdesk->bind_result($subsubcatid_parentdesk, $plinkdesk, $deskiddesk, $totpricedesk);
}

How can I bind all of the values without the unpacking error?

Please do not engage in a rollback war. I have improved your question by applying appropriate formatting and removing the irrelevant sign-off "please help". — mickmackusa, Nov 10 '20 at 16:47

mickmackusa · Accepted Answer · 2020-11-10T15:06:51.607

2

I recommend that you push the additional values into your $ccarr array, so that you only need to splat $ccarr inside of bind_param().

It looks like $op_r is an "operator" and not a value. So that should not be bound. Listen to Victor. The operator value should be validated against a whitelist of hardcoded values and injected directly into your query string.

$ccarr = explode(",", $cc);
$in = str_repeat('?,', count($ccarr) - 1) . '?';
$op_r = $tfvarr[2]; 
$budg_et = $tfvarr[1];
$budg_et1 = $tfvarr111111;

array_push($ccarr, $budg_et1, $budg_et);
$types = str_repeat('i', count($ccarr));

$sqldesk = "SELECT subsubcatid_parent, plink, deskid, SUM(itprice) AS totprice
            FROM desktop_items a
            JOIN items_table   b ON a.itno = b.itno
            JOIN obsubsubcat   c ON a.subsubcatid_parent = c.subsubcatid
            WHERE subsubcatid_parent IN ($in)
            GROUP BY subsubcatid_parent, deskid
            HAVING totprice > ? AND totprice {$op_r} ?";

$stmtdesk = $conn->prepare($sqldesk);
$stmtdesk->bind_param($types, ...$ccarr);
$stmtdesk->execute();
$stmtdesk->store_result();
$stmtdesk->bind_result($subsubcatid_parentdesk, $plinkdesk, $deskiddesk, $totpricedesk);

Additionally, I do not advice the use of the old-skool comma-JOINs. It is clearer and more modern to explicitly state the JOIN and this sets up using ON expressions to describe the joining relationship.

Finally, the && in MySQL should be AND. Always use all-caps to write MySQL keywords in your sql string.

edited Nov 10 '20 at 15:06

answered Nov 10 '20 at 14:45

mickmackusa

43,625
12
83
136

Thanks for the response. I could push more values inside one array, but that will create issues. Actually, its all dynamic, with different number of values coming in array (and also of different data types). So basically I want to have 2 arrays, one specifically for IN() operator for which I am using &in, and other array for rest of SQL Query. && is the AND operator, however ?? will take the values at the run time. I hope you got it. – Gurpreet Singh Nov 10 '20 at 15:07
I can only answer the question that you posted. I cannot possibly know how your project is written. I have been very generous in my answer; but I fear that I have stumbled into a "moving target" question. I don't see any reason that you cannot merge any additional parameters into an earlier declared array so that you only have to "spread" the first array variable. – mickmackusa Nov 10 '20 at 15:09
https://stackoverflow.com/a/51036322/2943403 Just keep building the parameters and the types as you go. – mickmackusa Nov 10 '20 at 15:31
1

Mick, many thanks for your time on this issue. You literally solved the issue I was stuck to and also advising me on good code styling (I am actually an old school guy, and working on php+mysql after 12 long years). That operator part that you enclosed in {}, didn't came in my mind. Thanks a lot again! – Gurpreet Singh Nov 10 '20 at 16:08

Cannot use positional arguments after argument unpacking while preparing a statement

1 Answers1