Looping through array string in a stored procedure

I have this stored procedure.

DROP PROCEDURE IF EXISTS spTest$$
CREATE PROCEDURE spTest(pUser VARCHAR(15), pForm VARCHAR(15))
BEGIN
INSERT INTO test (user, form)
VALUES (pUser, pForm);
END$$

I'd like to pass pForm as an array string stored as TEXT and loop
through it to parse the values and do multiple inserts.
Something like below:

**pForm is passed {"form1","form2","form3"} as text
DROP PROCEDURE IF EXISTS spTest$$
CREATE PROCEDURE spTest(pUser VARCHAR(15), pForm TEXT)
BEGIN
**parse pForm and strip out each form (f)
**for each f in pForm
INSERT INTO test (user, form)
VALUES (pUser, f);
**end for loop
END$$

The stored procedure should have inserted 3 forms for the user.
I hope there is a solution. I prefer to handle the multiple inserts
in mySQL rather than in PHP which would connect 3 times to the
database. TIA...

On 23 May 2007 23:59:32 -0700, mouac01@yahoo.com wrote:

>I have this stored procedure.
>
>DROP PROCEDURE IF EXISTS spTest$$
>CREATE PROCEDURE spTest(pUser VARCHAR(15), pForm VARCHAR(15))
>BEGIN
> INSERT INTO test (user, form)
> VALUES (pUser, pForm);
>END$$
>
>I'd like to pass pForm as an array string stored as TEXT and loop
>through it to parse the values and do multiple inserts.
>Something like below:
>
>**pForm is passed {"form1","form2","form3"} as text
>DROP PROCEDURE IF EXISTS spTest$$
>CREATE PROCEDURE spTest(pUser VARCHAR(15), pForm TEXT)
>BEGIN
> **parse pForm and strip out each form (f)
> **for each f in pForm
> INSERT INTO test (user, form)
> VALUES (pUser, f);
> **end for loop
>END$$
>

Provided that '#' is a forbidden character for your user names and
forms, make your PHP implode the users and forms with '#'.
You can then loop on something like :

SET pos=LOCATE('#',pUser);
SET nextUser =SUBSTRING(pUser FROM 0 FOR pos-1) ;
+ the same for forms ...

+ your inserts ...

SET pUser=SUBSTRING(pUser,pos);
+ the same for forms ...

I'm not sure about the positions, you might take a closer look. But in
fine it might do what you want.

>The stored procedure should have inserted 3 forms for the user.
>I hope there is a solution. I prefer to handle the multiple inserts
>in mySQL rather than in PHP which would connect 3 times to the
>database. TIA...

It seems strange. You might need only one connection anyways. You can
send 3 requests from PHP without connecting 3 times, can't you ?

On May 24, 1:12 am, subtenante <zzsubtenant...@gmail.com> wrote:
> On 23 May 2007 23:59:32 -0700, moua...@yahoo.com wrote:
>
>
>
>
>
> >I have this stored procedure.
>
> >DROP PROCEDURE IF EXISTS spTest$$
> >CREATE PROCEDURE spTest(pUser VARCHAR(15), pForm VARCHAR(15))
> >BEGIN
> > INSERT INTO test (user, form)
> > VALUES (pUser, pForm);
> >END$$
>
> >I'd like to pass pForm as an array string stored as TEXT and loop
> >through it to parse the values and do multiple inserts.
> >Something like below:
>
> >**pForm is passed {"form1","form2","form3"} as text
> >DROP PROCEDURE IF EXISTS spTest$$
> >CREATE PROCEDURE spTest(pUser VARCHAR(15), pForm TEXT)
> >BEGIN
> > **parse pForm and strip out each form (f)
> > **for each f in pForm
> > INSERT INTO test (user, form)
> > VALUES (pUser, f);
> > **end for loop
> >END$$
>
> Provided that '#' is a forbidden character for your user names and
> forms, make your PHP implode the users and forms with '#'.
> You can then loop on something like :
>
> SET pos=LOCATE('#',pUser);
> SET nextUser =SUBSTRING(pUser FROM 0 FOR pos-1) ;
> + the same for forms ...
>
> + your inserts ...
>
> SET pUser=SUBSTRING(pUser,pos);
> + the same for forms ...
>
> I'm not sure about the positions, you might take a closer look. But in
> fine it might do what you want.
>
> >The stored procedure should have inserted 3 forms for the user.
> >I hope there is a solution. I prefer to handle the multiple inserts
> >in mySQL rather than in PHP which would connect 3 times to the
> >database. TIA...
>
> It seems strange. You might need only one connection anyways. You can
> send 3 requests from PHP without connecting 3 times, can't you ?- Hide quoted text -
>
> - Show quoted text -

Thanks, subtenante...
I couldn't get your example to work, but it seems like your solution
should work.
It just go into an infinite loop.

DROP PROCEDURE IF EXISTS spTest$$
CREATE PROCEDURE spTest()
BEGIN
DECLARE pForm TEXT;
DECLARE pos INT;
DECLARE frm VARCHAR(5);
SET pForm = "{'a','b','c'}";
SET pos = LOCATE(',',pForm);
WHILE pos > 0 DO
SET frm = SUBSTRING(pForm FROM 0 FOR pos-1);
SELECT frm; /*this would be the INSERT*/
SET pos = LOCATE(',',SUBSTRING(pForm,pos));
END WHILE;
END $$

However, I just realized that the array example I gave is a simple
array only.
I would actually be passing a multi-dimensonal array like:
[{"fld1":"val1","fld2":"val2","fld3":"val3"},
{"fld1":"val4","fld2":"val5","fld3":"val6"},
{"fld1":"val7","fld2":"val8","fld3":"val9"}]
This would then insert 3 records for each of the 3 fields with the
appropriate value.
I think it would be too difficult to parse in mySQL.
You are right about PHP connecting only once. I meant to say request.
I may just resort to use PHP to parse the array.
I'll keep on trying to modify your solution to work for the multi-
dimensional array.
Thanks for your help...

Just a question, what's wrong with multiple INSERTs ?

INSERT INTO `tbl1` (`fld1`,`fld2`,`fld3`) VALUES
('val1','val2','val3'),
('val4','val5','val6'),
('val7','val8','val9')

PHP can do this and send it right away to MySQL.

If it is just a security issue (granting only EXECUTE to the user) or
if you need other things to be done in that PROCEDURE, you still can
make a PROCEDURE that will PREPARE STATEMENT with the arguments

_table='`tbl1` '

_fields='(`fld1`,`fld2`,`fld3`)'

_values="('val1','val2','val3'),
('val4','val5','val6'),
('val7','val8','val9')"

which are all very easy to get from PHP. And then you need not to
parse anything within MySQL. Just beware of SQL injection with PREPARE
STATEMENT, but that's not a huge threat.

On 24 May 2007 23:53:52 -0700, mouac01@yahoo.com wrote:

> WHILE pos > 0 DO
> SET frm = SUBSTRING(pForm FROM 0 FOR pos-1);
> SELECT frm; /*this would be the INSERT*/
> SET pos = LOCATE(',',SUBSTRING(pForm,pos));
> END WHILE;

By the way, if you stick to that solution, you get an infinite loop
because you never reset pForm.
You need to
SET pForm=SUBSTRING(pForm,pos);
in your loop, or something like that (it might be pos+1), check the
manual for SUBSTRING to be sure.

Nothing wrong with multiple INSERTs. It just that that's not how I
have my app set up.
My app is mostly javascript based using AJAX requests to only 1 PHP
page (json.php).
The AJAX requests POST to json.php only the stored procedure with
parameters.. ex. spTest('prm1','prm2').
json.php then connects to MySQL and executes CALL
spTest('prm1','prm2') and returns a message or recordset back in JSON
format.
I want to keep the design consistent. Javascript handles the look/
feel/validation and prepares the stored procedure. All PHP does is
execute the stored procedure and return data. The stored procedures
contain more logic to validate the check the data.
In my array example AJAX would POST something like below to json.php
as a stored procedure with 3 parameters:
spTest('userid','name','[{"fld1":"val1","fld2":"val2","fld3":"val3"},
{"fld1":"val4","fld2":"val5","fld3":"val6"},
{"fld1":"val7","fld2":"val8","fld3":"val9"}]')
I need MySQL to parse the third parameter which is a multi-dimensional
array and insert records based on the length of the array.

BTW, thanks for the infinite loop fix...

On 25 May 2007 10:18:07 -0700, mouac01@yahoo.com wrote:

>In my array example AJAX would POST something like below to json.php
>as a stored procedure with 3 parameters:
>spTest('userid','name','[{"fld1":"val1","fld2":"val2","fld3":"val3"},
>{"fld1":"val4","fld2":"val5","fld3":"val6"},
>{"fld1":"val7","fld2":"val8","fld3":"val9"}]')

Ok, but i still can't see why your PHP script can't make the parsing
out of the third element sent by javascript, to create a multiple
INSERT query... It seems json is a bit lazy there. It would be far
easier for json to do the job.

>BTW, thanks for the infinite loop fix...

No problem.