CODEWITHSUNDEEP
phparrayspdo
FiftyStars
FiftyStars

Reputation: 328

PDO pass array into stored procedure

How can I pass array into:

$query = 'CALL myStoredProcedure(:array)'

to use it in IN clause of that stored procedure?

BTW: its not duplicate. Passing array into stored procedure is NOT EQUAL to passing array into SELECT query directly. So duplicate mark is wrong. I can pass array as a set of (?,?,?,?,?) but how should i call unknown count of parameters inside IN clause?

$params = array(1, 21, 63, 171);
$place_holders = implode(',', array_fill(0, count($params), '?'));
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);

Upvotes: 1

Views: 1622

Answers (2)

FiftyStars
FiftyStars

Reputation: 328

Solved!

PHP:

$params = implode(',', $array);//string arrays should escape each element with quotes
$stmt = $db->prepare('CALL myProcedure(:array)');
$stmt->bindParam(':array', $params, PDO::PARAM_STR);

STORED PROCEDURE:

CREATE PROCEDURE `myProcedure`(IN `array` VARCHAR(2048))
#VARCHAR(2048) - maximum expected total length of imploded array
BEGIN
SET @query:= CONCAT(
'SELECT
    *
FROM
    `some` AS `table`
WHERE
    `table`.`field` IN (',`array`,')');
PREPARE stmt FROM @query;
EXECUTE stmt;
END

Upvotes: 2

s3nzoM
s3nzoM

Reputation: 2207

You can't pass array to use in mysql, do something like

$array  = implode(',',$array);

$query  = "CALL myStoredProcedure($array)";

And from my experience queries like SELECT * FROM table1 WHERE column IN(?) don't work you have to put the IN values directly in the query

Upvotes: 0

Related Questions