Inserting array values in mysql using stored procedure

Question

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.

score 2 · Accepted Answer · answered Nov 18 '13 at 09:12

2

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);

answered Nov 18 '13 at 09:12

praveen

286
1
8

Refer to this link this might help you - http://stackoverflow.com/questions/17330557/how-to-insert-php-array-values-using-an-stored-procedure-sql-server-2008-r2mssq – praveen Nov 18 '13 at 09:26

score 1 · Answer 2 · answered Dec 21 '13 at 10:10

1

SELECT *

FROM fruits

WHERE FIND_IN_SET ( name, fruit_array );

Hope this will help you..

answered Dec 21 '13 at 10:10

Raj

706
8
18

Sandesh · Answer 3 · 2017-08-17T06:03:34.797

0

Another Solution:

Use foreach loop:

foreach(condition)
{
  //Create insert query string
}

//insert query
//Execute Query

For more help paste code

edited Aug 17 '17 at 06:03

answered Nov 18 '13 at 09:15

Sandesh

349
2
8

@leandronn I have just suggested another solution to insert multiple values in a single query. Please check edited answer. – Sandesh Aug 17 '17 at 06:05

Inserting array values in mysql using stored procedure

3 Answers3