Pass array into a stored procedure

Question

I am trying to pass an array of values from php to mysql stored procedures as parameter list and how to use the array inside the stored procedure. The query in the procedure has three IN statements in in there so I would like to do IN(@listOfids) where @listOfids is 1,2,3,4 (an imploded array from php).

Answer 1

I think the main problem here is that MySQL doesn't support arrays as a data type. You need to form a one-to-many relationship to another table that contains a foreign key back to your main data and the array's data.

Answer 2

I think you have to pass it in as a csv. Mysql is not very friendly with looping and such so your probably best doing it outside anyways. As far as stored procedure languages go I find Mysql really is lacking.

Answer 3

So I got a workaround which is to concatenate the query and the parameters so the pseudo code is

CREATE PROCEDURE `related_stories`(IN param1 VARCHAR(255), IN param2 VARCHAR(255), IN param3 VARCHAR(255), IN publishDate INT(11), IN tlimit INT(11))
BEGIN

SET  @query =CONCAT( '

select s.* from 
(

select * from 
(
 SELECT something where condition IN (',param1,')
) as table1

UNION ALL

select * from 
(
 SELECT something where condition IN (',param2,')
) as table2

UNION ALL

select * from 
(
 SELECT something where condition IN (',param3,')
) as table3

) as s

WHERE (s.publish_date < ',publishDate,') 
GROUP BY id limit ',tlimit,';');

PREPARE stmtInsert FROM @query;
EXECUTE stmtInsert;

END

param1,param2,param3 are imploded arrays that is passed in via php e.g.('1,2,3,4'). Hope this helps someone