How to insert multiple rows in mysql using procedures when we have array data to send in-CodePudding

I have set of codes where i need to insert multiple rows in MySQL using procedures

<?php
$conn = new mysqli("localhost","root","","company");
$array_item = 'Array
(
    [0] => Array
        (
        [user_name] => VANITHA
        [adddress] => 
        [mobile_no] => 8877665544
        
        )

    [1] => Array
        (
       [user_name] => VANITHA1
        [adddress] => rere
        [mobile_no] => 8877665544
       )
);

        
';
$sql=mysqli_query($conn,"CALL addnew($array_item)");

procedures codes are as follows

 CREATE  PROCEDURE addnew(IN ArrayDemo VARCHAR(100))

    BEGIN  

    insert into geektable (user_name,adddress,mobile_no) 
                    values(user_name,adddress,mobile_no);  
 
    END

CodePudding user response：

You can send multiple parameters, or you can create your own function to split the VARCHAR:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1))   1),
       delim, '');

Usage:

SELECT SPLIT_STR(string, delimiter, position)

Example:

SELECT SPLIT_STR('aa1|bb2|cc3|dd4', '|', 2) as second_item;

will return bb2

CodePudding user response：

There is no array parameter in MySQL. What you need to do it to loop through the array in PHP and call the procedure for each row.