Master Yoda Master Yoda - 1 month ago 16
MySQL Question

Inserting array values in mysql using stored procedure

I have a table in which i want to insert multiple rows having values from a php array , now i can't figure out how to pass an array in stored procedure .

Example-: i have a php array having names

['sqlite','mysql','sql']


now what i want is to send this array to stored procedure and loop through the array taking one value at a time and inserting into the database table.

Answer Source

You can pass a string with your list and use a prepared statements to run a query, e.g. -

DELIMITER $$

CREATE PROCEDURE GetFruits(IN fruitArray VARCHAR(255))
BEGIN

  SET @sql = CONCAT('SELECT * FROM Fruits WHERE Name IN (', fruitArray, ')');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END
$$

DELIMITER ;

How to use:

SET @fruitArray = '\'apple\',\'banana\''; CALL GetFruits(@fruitArray);