0

I have a simply query to fetch products based on a comma separated list of IDs:

CREATE PROCEDURE `databaseName`.`fetchRecentlyViewed` (IN productIdCsv VARCHAR(128))
BEGIN
SELECT listingTitle, productName, productID FROM products WHERE productID IN (productIdCsv );
END

If I run this query against it:

CALL fetchRecentlyViewed ('6,7,4,3')

I only ever get the first product returned - its so simple I really don't know where to go from here!

I'm missing something obvious I'm sure!

Mr Fett
  • 7,979
  • 5
  • 20
  • 21
  • possible duplicate of [Pass array to MySQL stored routine](http://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine) – georstef Oct 09 '14 at 13:06
  • georstef, I don't see how it's a duplicate? That's actually the question I used to help me put together my routine and PHP. I don't need to know how to 'Pass array to MySQL stored routine', I'm trying to determine why - when I do that - I'm only getting one result. – Mr Fett Oct 09 '14 at 13:13
  • The accepted answer in that question says exactly what you need to solve your problem. You are actually executing `SELECT listingTitle, productName, productID FROM products WHERE productID IN ('6,7,4,3')` which is incorrect, you have to prepare the statement and then run it with execute. Also `find_in_set` could help. – georstef Oct 09 '14 at 13:19
  • georstef - apologies if I'm missing something (MySQL isn't my strong point), as far as I can tell the accepted answer returns only one row as it's essentially the same as the SQL in my question above, just constructed using Dynamic SQL? My question is how to return more than one row? Effectively the answer to my question is - as you say - 'FIND_IN_SET' – Mr Fett Oct 09 '14 at 14:29

1 Answers1

0

So it seems that the most popular answer in the question relating to this also only returns one row: Pass array to MySQL stored routine

If you want to return more than one row, you need to use FIND_IN_SET

Here's a good resource on this:

FIND_IN_SET() vs IN()

Community
  • 1
  • 1
Mr Fett
  • 7,979
  • 5
  • 20
  • 21