4

I am looking to pass a list of id's from PHP to a MySQL stored procedure so that I can then join to this list. I know that I don't want to pass the array as a csv list to the SP, but I'm not sure what the best practice is to do.

With SQL Server I've built up a xml object which I then pass to the SP and join to this. This seems a bit like overkill, and I've never really been sure this is actually a good idea.

Is there a simple, efficient way of taking by list of id's in an array in PHP and passing this into a MySQL SP which I can the use in a join?

I've read through this related question, but haven't found the answer I was looking for: Parameterize an SQL IN clause

A related question that would solve my problem is this one: PHP Array as input to Stored Procedure

But I was looking to use a stored procedure rather than building up the SQL code within PHP.

What would best practice be in this case?

Community
  • 1
  • 1
infojolt
  • 5,244
  • 3
  • 40
  • 82

1 Answers1

0

Pass the array as a string parameter, then construct a query and run it using prepared statements.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Is it possible to expand this answer. If you pass the array in as a csv, what would the sql look like? Would you use the '?' character, or just concat the csv to the sql string? Are there any concerns with this approach, such as sql injection? – zod May 08 '12 at 15:23
  • Have a look at this example - http://buysql.com/mysql/39-pass-array-to-stored-procedure.html – Devart May 11 '12 at 07:58