PHP PDO : How to call stored procedure to pass comma separated string to parameter

Question

I have worked out a stored procedure with a prepared statements inside to pass a comma separated string to one of the parameters with help from another thread. Now I'm having trouble binding values to positional parameters in PHP PDO. I need to call the stored procedure like this:

CALL load_things('''1283943kd9'',''2e9kk389334''','53')

The first parameter is a list of model numbers and the second one 53 is an user id. It is workings fine when I enter the command in Adminer or phpmyadmin.

Here's the PHP code to get the corresponding number of question marks into the brackets:

 $id_group = $_POST["group"]; // array

 $in  = str_repeat("''?'',", count($id_group) - 1) . "''?''";    

 $sql .= "call loadit('".$in."','?')";

 $users = $dbh->prepare($sql);

 $i = 1;

 foreach ($id_group as $id) 
 {
   $users->bindValue($i++, $id);
 }

 $lasti = (count($id_group) + 1);

 $users->bindValue($lasti,$_SESSION["user_id"]);

 $users->execute();

On a page that sends out 20 values in $_POST["group"], (each of them is 30 characters max) it seems to have generated the exact number of placeholders from the look of the following output. (The 21 st is the user id), but I'm not getting any results. Here's the response from Chrome console`:

SQL: [143] 

call loadit('''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?'',''?''','?')

Params:  21
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=2
Key: Position #1:
paramno=1
name=[0] ""
is_param=1
param_type=2
Key: Position #2:
paramno=2
name=[0] ""
is_param=1
param_type=2
Key: Position #3:
paramno=3
name=[0] ""
is_param=1
param_type=2
Key: Position #4:
paramno=4
name=[0] ""
is_param=1
param_type=2
Key: Position #5:
paramno=5
name=[0] ""
is_param=1
param_type=2
Key: Position #6:
paramno=6
name=[0] ""
is_param=1
param_type=2
Key: Position #7:
paramno=7
name=[0] ""
is_param=1
param_type=2
Key: Position #8:
paramno=8
name=[0] ""
is_param=1
param_type=2
Key: Position #9:
paramno=9
name=[0] ""
is_param=1
param_type=2
Key: Position #10:
paramno=10
name=[0] ""
is_param=1
param_type=2
Key: Position #11:
paramno=11
name=[0] ""
is_param=1
param_type=2
Key: Position #12:
paramno=12
name=[0] ""
is_param=1
param_type=2
Key: Position #13:
paramno=13
name=[0] ""
is_param=1
param_type=2
Key: Position #14:
paramno=14
name=[0] ""
is_param=1
param_type=2
Key: Position #15:
paramno=15
name=[0] ""
is_param=1
param_type=2
Key: Position #16:
paramno=16
name=[0] ""
is_param=1
param_type=2
Key: Position #17:
paramno=17
name=[0] ""
is_param=1
param_type=2
Key: Position #18:
paramno=18
name=[0] ""
is_param=1
param_type=2
Key: Position #19:
paramno=19
name=[0] ""
is_param=1
param_type=2
Key: Position #20:
paramno=20
name=[0] ""
is_param=1
param_type=2
[]

Stored Procedure and table schema (fiddle):

DELIMITER ;;
CREATE PROCEDURE `load_things` (IN `yr_model_no` varchar(1000), IN `yr_app_id` int(5))
BEGIN
    SET @s = 

CONCAT('
SELECT * FROM 
(
   SELECT COUNT( c.app_id ) AS users_no, ROUND( AVG( c.min ) , 1 ) AS avg_min, ROUND( AVG( c.max ) , 1 ) AS avg_max, a.mid, a.likes, a.dislikes, b.model_no
        FROM  `like` a
        RIGHT JOIN  `model` b ON a.mid = b.mid
        LEFT JOIN  `details` c ON c.mid = b.mid
        WHERE b.model_no IN (',yr_model_no,')
        GROUP BY b.model_no
)TAB1
JOIN
(
   SELECT a.app_id,b.model_no,IFNULL(c.isbooked,0) AS isbooked,d.min,d.max,e.like_type
     FROM `users` a
     JOIN `model` b
       ON b.model_no IN (',yr_model_no,')
LEFT JOIN `favorite` c 
       ON c.app_id = a.id
      AND c.mid = b.mid         
LEFT JOIN `details` d 
       ON d.app_id = a.id
      AND d.mid = b.mid 
LEFT JOIN `users_likes` e
       ON e.app_id = a.id
      AND e.mid = b.mid
    WHERE a.id = ',yr_app_id,'
)TAB2
ON TAB1.model_no = TAB2.model_no');

PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt3;
END;;
DELIMITER ;

Can anyone help me figure out what's wrong with the PHP code? Is my stored procedure not compatible with PDO?

Answer 1

You're quoting your placeholders. That's a no-no. The DB and the interface library will take care of all that themselves. All you need is to provide the placeholders themselves:

$in  = str_repeat('?,' count($id_group) - 1) . '?';

which should eventually produce

call loadit(?,?,?,......?)