use array in bind_param

[amirelgohary1990]

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

[requinix]

$param is an array of values. It is not an array of values where one of them is the values combined together separated by spaces.

If your query has 3 integers then your parameters array needs to have 3 integers.

[mac_gyver]

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

 

 

[maxxd]

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.

[Strider64]

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
    $stmt->execute();

    // 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.']);
    }

 

[amirelgohary1990]

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);
$stmt->execute($arr_params);
$a_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
trigger_error('Wrong SQL: ' . $query . ' Error: ' . $e->getMessage(), E_USER_ERROR);
}

[Barand]

38 minutes ago, amirelgohary1990 said:

but did not echo the result from the database

I don't see any code there that should output the results.

[amirelgohary1990]

27 minutes ago, Barand said:

I don't see any code there that should output the results.

@Barand
I am new in oop

I assumed that $stmt->execute($arr_params); will echo

[Barand]

Your call to fetchAll() puts the returned rows into $a_data.

You need to loop through the array "$a_data" and output the content of each row.

[mac_gyver]

• 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.