stored procedure for each row of an outer stored procedure

Question

I am new to stored procedures and am using mysql. I am creating the procedures in phpmyadmin.

What I need to achieve is a stored procedure which will return all user details from one table and their associated relations in a linking table.

I need my php web service to call the procedure and be able to return a json similar to the following:

{
    "userID": "2222",
    "userName": "mr x",
    "userAddress": "street, town, County",
    "userMobile": "1111",
    "userPhone2": "2222",
    "latitude": "53.2802937",
    "longitude": "-9.0515995",
    "relations": [
        {
            "relID": "3333",
            "relFName": "Mr",
            "relSName": "rel1",
            "relAddress": "street2, town2, county2",
            "relPhone": "3333",
            "relPhone2": "3333",
            "relType": "Brother"
        },
        {
            "relID": "4444",
            "relFName": "ms",
            "relSName": "rel4",
            "relAddress": "street 4, town 4, county 4",
            "relPhone": "4444",
            "relPhone2": "4444",
            "relType": "Friend"
        }
    ]
}

I currently have two stored procedures i.e.

getUsers:

BEGIN

SELECT * FROM Users ORDER BY userSName DESC;

END

and getRels:

BEGIN

SELECT * FROM relations WHERE relations.userID = UserIDParam;

END

But I'm not sure how to link them, before using sps, I had two queries. The outer one retrieved user details and the inner query was executed for each row in the results set of the first i.e. getting the relations array for each user. However when I switched from basic queries to calling stored procedures I get the error that commands are out of sync.

I can't seem to find an example of what I'm trying to achieve. Any help appreciated

UPDATE the php code looks something like this:

$fetch = mysql_query("CALL getUsers();");

                while ($row = mysql_fetch_array($fetch)){

                        $GetRelations_Query = "CALL getRels();";
                        $relations_results=mysql_query($GetRelations_Query);
                        $relations_arr = array();

                        while ($InnerRow = mysql_fetch_array($relations_results)){

                            $relationsReturned = array('relID' =>$InnerRow['personID'], 'relFName' => $InnerRow['FName'], 'relSName' => $InnerRow['SName'], 
                            'relAddress' => $InnerRow['Address'], 'relPhone' => $InnerRow['Phone'], 'relPhone2' => $InnerRow['Phone2'], 'relType' => 
                            $InnerRow['relationship']);

                            array_unshift($relations_arr,$relationsReturned);
                        }


                            $row_array = array('userID' => $row['userID'], 'userName' => $row['userName'].' '.$row['userSName'], 'userAddress' => $row['userAddress'],
                            'userMobile' => $row['userMobile'],'userPhone2' => $row['userPhone2'], 'latitude' => $row['latitude'], 'longitude' => $row['longitude'], 'relations' => $relations_arr);

                            array_unshift($return_arr,$row_array);
                }

and error is Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given referring to the line 'while ($InnerRow = mysql_fetch_array($relations_results)){'. I know that when I print out the error associated with that query, I am retrieving the error message stated above i.e. commands out of sync

How does relations table look like? Looks like a simple `JOIN` for me. — mwilczynski, May 08 '15 at 09:28
I'm sorry, seems that's not what you're asking for. Can we see the PHP file for mysql fetching part? Seems to be rather PHP issue and not releasing result set on the connector. — mwilczynski, May 08 '15 at 09:31
Little update with one more solution I would recommend and second I would not (but still should work). — mwilczynski, May 08 '15 at 10:41

mwilczynski · Answer 1 · 2015-05-08T16:57:15.630

0

I dig up a little and it seems that old mysql way of connecting to MySQL DB is not supporting multiple stored procedures at once. Not without going multiple connections which is (as you probably would guess) a not so good idea. You might stay with mysql that I don't recommend as PHP docs say that:

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

mysqli_query()

PDO::query()

As in: http://php.net/manual/en/function.mysql-query.php

This is how I understand your current data model:

Link to SQLFiddle

Recommended solution:

I would recommend to go to updated and newer mysqli as mysql will probably disappear in some time from PHP at all.

Second recommended solution:

You can also connect two stored procedures into one. Just join the two tables in one SELECT on userID and query using parameter you're providing. Also I can see UserIDParam in your stored procedure but can't see it used in your code. Anyway, remember to prevent yourself from SQL Injection. Stored procedures don't always protect you from such: http://blogs.msdn.com/b/brian_swan/archive/2011/02/16/do-stored-procedures-protect-against-sql-injection.aspx

Not recommended solution - alternative:

First get all the users at once. Clear results set after using getUsers. Then iterate on this result with second stored procedure getRels.

edited May 08 '15 at 16:57

answered May 08 '15 at 10:17

mwilczynski

3,062
1
17
27

My issue is that each user can have an ARRAY of relations (of varying lengths) so I'm unsure how to implement the second recommended solution you have provided? – user2363025 May 08 '15 at 11:38
If you JOIN two tables on userid and then look with parameter, you got the same result: resultset of relations for given user (user you passed as parameter). – mwilczynski May 08 '15 at 12:07
Something like `SELECT * FROM Users JOIN relations ON relations.userID = Users.userID ORDER BY userName DESC`; I dunno for what for are you using the param in general but you can add `WHERE` clause if you want to. – mwilczynski May 08 '15 at 12:09
Maybe I am missing something or maybe I am not explaining myself. User A could have 7 relations, each of which has their own set of associated details. The way your query is written, it would only allow for one relation per user? Or am I missing something? – user2363025 May 08 '15 at 13:40
No, it wouldn't limit it that way, provided it's 1:N relationship. But it depends how your relations table looks like. Does it have relationship defined with user table? If you don't know, can you at least provide columns that you have in relations table? – mwilczynski May 08 '15 at 16:27
I've added 'How I understand your data model' SQL fiddle, please check if that's how it looks like now. If not, let us know the correct model. – mwilczynski May 08 '15 at 16:57
Where can I access your fiddle? Yes the relations table contains a foreign key of userID which links to the primary key of the user's table – user2363025 May 14 '15 at 07:51
Click the link in my answer. Alternatively: http://sqlfiddle.com/#!9/8082d/1 – mwilczynski May 20 '15 at 19:05

stored procedure for each row of an outer stored procedure

1 Answers1