Reputation: 8227
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).
Upvotes: 8
Views: 9378
Reputation: 8227
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
Upvotes: 5
Reputation: 1403
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.
Upvotes: 0
Reputation: 67723
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.
Upvotes: 1