CODEWITHSUNDEEP
phpmysqlstored-procedures
Akeem
Akeem

Reputation: 8227

Pass array into a stored procedure

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

Answers (3)

Akeem
Akeem

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

Andrew Clark
Andrew Clark

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

Henrik Paul
Henrik Paul

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

Related Questions