Question 40098 in PHP Question posted in
Official manual can be found here.

Fixing dynamic unknown named parameters – PHP

In this snippet below which is used only as a SELECT statement for multiple rows of data, it gives a error on $stmt->bind_param($types, ...$values);:

mysqli_stmt::bind_param() does not accept unknown named parameters

I understand the error in general, but $Query is dynamic with the necessary question marks already in it but may have any number of $types and $values or maybe only one.

$results_array = [];
if (!empty($values)) :
    $stmt = $mysqli->prepare($Query);
    $stmt->bind_param($types, ...$values);
    $stmt->execute();
    $result = $stmt->get_result();
    while($row = $result->fetch_array()) :
        $results_array[] = $row;
    endwhile;
    $stmt->close();
    return $results_array;
endif;

$mysqli of course contains the connection string while $Query is the dynamic query being used which already contains the question marks and $types contains the related field types, ie "sssi" or whatever and matches the data in the $values array.

It seems to be working now thanks to the help below. Since some of this came from the answers and comments, I can’t take credit for so I’ll append what I have now here as the current problem appears to have been solved! I also elaborated on it a bit. This is a small part of a much larger function so $selType is being passed into it.

$results_array = [];
if (!empty($values)) :
    $stmt = $mysqli->prepare($Query);
    $stmt->bind_param($types, ...$values);
    $stmt->execute(array_values($values));
    $result = $stmt->get_result();
    if ($selType === "assoc") :
        while($row = $result->fetch_assoc()) :
            $results_array[] = $row;
        endwhile;
    else :                  
        while($row = $result->fetch_array()) :
            $results_array[] = $row;
        endwhile;
    endif;
    $stmt->close();
    return $stmt->get_result()->fetch_all();
 endif;
Tags:

2

Answers


  1. 0

    You can dynamically generate the question marks for the SQL query based on the number of values, and then bind the values to the parameters with mysqli_stmt::bind_param()

    After refactoring your code, it should look like this.

    $results_array = [];
if (!empty($values)) {
    $num_values = count($values);
    $question_marks = str_repeat("?,", $num_values);
    $question_marks = rtrim($question_marks, ",");
    $Query .= " VALUES (" . $question_marks . ")";

    $stmt = $mysqli->prepare($Query);
    $stmt->bind_param($types, ...$values);
    $stmt->execute();
    $result = $stmt->get_result();
    while($row = $result->fetch_array()) {
        $results_array[] = $row;
    }
    $stmt->close();
}
return $results_array;
    Login or Signup to reply.
  2. 0

    It seems that you are trying to unpack an associative array. It needs to be a list instead. You can just use array_values to convert it into a list.

    Your code simplified on PHP 8.1 should look like this:

    $results_array = [];
if (!empty($values)) :
    $stmt = $mysqli->prepare($Query);
    $stmt->execute(array_values($values));
    return $stmt->get_result()->fetch_all();
endif;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search