Jump to content

use array in bind_param

Go to solution Solved by maxxd,

Recommended Posts

Hello, I am trying to use array in bind statement to avoid entering bind manually

Below, I set up the array, then imploded the array to insert on it    // to return 1,2,5 , but I got error Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

$arr = [1,2,5];
$arr_as_string = implode( ',',$arr);
$type = 'iii';

$params = [$type,$arr_as_string];

$tmp = [];

foreach($params as $key => $value) $tmp[$key] = &$params[$key];

call_user_func_array([$query, 'bind_param'], $tmp);

Link to comment
Share on other sites

  • Solution

You can also switch to PDO and get the benefits of not having go through extra steps like explicitly binding parameters (you can pass an array to PDOStatement::execute). Granted you'll still have to pass it an array that matches the parameters in the query, but it's less typing. There are also other reasons to move to PDO; the simplified interface and much easier readability, for example.

Link to comment
Share on other sites

I also find using PDO to be much cleaner. Here's an example -

        // ... more code above ...
		// Prepare the SQL query with placeholders
        $sql = "UPDATE gallery SET category = :category, heading = :heading, content = :content, image_path = :image_path, thumb_path = :thumb_path WHERE id = :id";
        $stmt = $pdo->prepare($sql);

        // Bind the values to the placeholders
        $savePath = $saveDirectory . $destinationFilename;
        $stmt->bindParam(':image_path', $savePath);
        $stmt->bindParam(':thumb_path', $thumb_path);

    } else {
        // Prepare the SQL query with placeholders
        $sql = "UPDATE gallery SET category = :category, heading = :heading, content = :content WHERE id = :id";
        $stmt = $pdo->prepare($sql);

    // Bind the values to the placeholders
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->bindParam(':category', $category);
    $stmt->bindParam(':heading', $heading);
    $stmt->bindParam(':content', $content);

    // Execute the prepared statement

    // Check if the update was successful
    if ($stmt->rowCount() > 0) {
        echo json_encode(['success' => true, 'message' => 'Record updated successfully.']);
    } else {
        echo json_encode(['success' => false, 'message' => 'No record updated.']);


Link to comment
Share on other sites

12 hours ago, mac_gyver said:

all of this code is unnecessary. you can use php's ... operator. see the Argument unpacking via ... example at this link - https://www.php.net/manual/en/migration56.new-features.php



Hello @mac_gyver
I tried this example in the link you shared Example #5 Execute a prepared statement using array for IN clause
Actually the error gone, but did not echo the result from the database



$arr_params = [1,2,5];
$placeholders = implode(',', array_fill(0, count($arr_params), '?'));
$query = "SELECT designation_id, designation_name FROM designations WHERE designation_id NOT IN ($placeholders)";

try {
$stmt = $conn->prepare($query);
$a_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
trigger_error('Wrong SQL: ' . $query . ' Error: ' . $e->getMessage(), E_USER_ERROR);

Link to comment
Share on other sites

  • the example you are using is actually from the link that @maxxd posted
  • this is using the PDO extension. did you change your connection code to use the PDO extension? you would be getting  a fatal runtime error if you didn't.
  • this can actually be further simplified by using FIND_IN_SET() or NOT FIND_IN_SET(), for the query you are showing us, and using a single prepared query place-holder.
  • the code you posted is fetching all the matching rows of data into $a_data. it is this variable that you would test/loop over to produce the output in the html document.
  • if you set the default fetch mode to assoc when you make the PDO connection, you don't need to specify it in each fetch statement, simplifying the code.
  • the only time you should handle database statement exceptions in your code are for user recoverable errors, such as when inserting/updating duplicate or out of range data. in all other cases, simply do nothing in your code and let php handle the exception, simplifying the code.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.