#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    249
    Rep Power
    17

    Invalid parameter number: mixed named and positional parameters


    I have a requirement where for a user I need to find if they have one or more variables which are pulled from one column,
    I am getting "Invalid parameter number: mixed named and positional parameters" error. I start by placing all the variables into an array, which will then be
    used as an IN, I then have the userid variable to check against also. The error is throwing on the last line, what is the appropriate way of mixing this in and = to get the results?

    PHP Code:
        $arr = [$ability2$ability3$ability4$ability5$ability6$ability7$ability8$ability9$ability10];
    $in  str_repeat('?,'count($arr) - 1) . '?';
    $validation_abilities_scan $DB_con2->prepare("SELECT userid, ability_id from validation_abilities where ability_id IN ($in) AND userid = :userid_confirmed");
    $validation_abilities_scan->execute(); 
    Thanks,

    G
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    142
    Rep Power
    66
    The error is both because you cannot mix ? and named place-holders in a single query and you are not supplying any input data as an array parameter in the ->execute(...) call.

    Use all ? place-holders, append the userid input value to $arr, and supply $arr as the parameter in the ->execute($arr) call.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    249
    Rep Power
    17
    Thanks for the reply, much appreciated and I get what you are saying. I have changed the code as below, however from this
    I now get: Invalid parameter number: number of bound variables does not match number of tokens, on the execute.

    PHP Code:
    $arr = [$ability2$ability3$ability4$ability5$ability6$ability7$ability8$ability9$ability10$userid]; 
    $in  str_repeat('?,'count($arr) - 1) . '?'
    $validation_abilities_scan $DB_con2->prepare("SELECT userid, ability_id from validation_abilities where ability_id IN ($in) AND userid IN ($in)"); 
    $validation_abilities_scan->execute($arr); 
    Thanks again,

    G
  6. #4
  7. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,885
    Rep Power
    9646
    Swing and a miss. Go back to your original version and simply make the :userid_confirmed be a positional parameter with a question mark instead of a named parameter.

    That's assuming the part of your code where you pass in the parameters to the query does exist and was merely omitted from your post.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    142
    Rep Power
    66
    While I can see where someone purely reacting the verb 'append' in my reply could have ended up putting the $userid variable into the initial $arr assignment statement, rather than appending it later, I cannot see how anyone could have written - "... to find if they have one or more variables which are pulled from one column, ... I start by placing all the variables into an array, which will then be used as an IN," could have done so, unless they are just copying things they see without actually looking at what the code is doing.

    The three actions/changes to the code I listed, while brief and not spelling out each key you need to press to perform them, were in order, from top to bottom in the code, where they needed to be changed at.

    Use all ? place-holders
    Change this -
    PHP Code:
    $validation_abilities_scan $DB_con2->prepare("SELECT userid, ability_id from validation_abilities where ability_id IN ($in) AND userid = :userid_confirmed"); 
    To this -
    PHP Code:
    $validation_abilities_scan $DB_con2->prepare("SELECT userid, ability_id from validation_abilities where ability_id IN ($in) AND userid = ?"); 
    append the userid input value to $arr
    Add the following line, after the point where you have used $arr to produce $in -
    PHP Code:
    $arr[] = $userid
    and supply $arr as the parameter in the ->execute($arr) call
    You got this one correct.

    And finally, the $arr variable assignment statement you have shown, should instead be coming from something like a set of checkbox form fields, in which case you won't have an actual array building statement like that in your code.
    Last edited by DSmabismad; July 13th, 2018 at 07:13 AM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    249
    Rep Power
    17
    ok thanks that did the trick!

    G

IMN logo majestic logo threadwatch logo seochat tools logo