Professional Web Applications Themes

Looping through array string in a stored procedure - MySQL

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 p 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 **p 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 ...

  1. #1

    Default 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 p 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
    **p 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...

    mouac01@yahoo.com Guest

  2. #2

    Default Re: Looping through array string in a stored procedure

    On 23 May 2007 23:59:32 -0700, com wrote:
     

    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.
     

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

  3. #3

    Default Re: Looping through array string in a stored procedure

    On May 24, 1:12 am, subtenante <com> wrote: 



    >
    > 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.

    >
    > 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 -[/ref]

    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 p in mySQL.
    You are right about PHP connecting only once. I meant to say request.
    I may just resort to use PHP to p the array.
    I'll keep on trying to modify your solution to work for the multi-
    dimensional array.
    Thanks for your help...

    mouac01@yahoo.com Guest

  4. #4

    Default Re: Looping through array string in a stored procedure

    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
    p anything within MySQL. Just beware of SQL injection with PREPARE
    STATEMENT, but that's not a huge threat.
    subtenante Guest

  5. #5

    Default Re: Looping through array string in a stored procedure

    On 24 May 2007 23:53:52 -0700, com wrote:
     

    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.
    subtenante Guest

  6. #6

    Default Re: Looping through array string in a stored procedure

    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 p 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...

    mouac01@yahoo.com Guest

  7. #7

    Default Re: Looping through array string in a stored procedure

    On 25 May 2007 10:18:07 -0700, com wrote:
     

    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.
     

    No problem.
    subtenante Guest

Similar Threads

  1. About string parameters to stored procedure
    By Owen in forum ASP.NET Security
    Replies: 4
    Last Post: September 4th, 12:55 PM
  2. to get array from stored procedure
    By qgh in forum Coldfusion Database Access
    Replies: 1
    Last Post: September 22nd, 09:26 PM
  3. pass a column ID as string to a stored procedure
    By Anton in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 8th, 01:59 PM
  4. Do looping on Stored procedure
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 05:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139