php - stored procedure for each row of an outer stored procedure -

this question has answer here:

• mysqli_fetch_array()/mysqli_fetch_assoc()/mysqli_fetch_row() expects parameter 1 resource or mysqli_result, boolean given 33 answers

i new stored procedures , using mysql. creating procedures in phpmyadmin.

what need achieve stored procedure return user details 1 table , associated relations in linking table.

i need php web service call procedure , able return json similar 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 have 2 stored procedures i.e.

getusers:

begin  select * users order usersname desc;  end 

and getrels:

begin  select * relations relations.userid = useridparam;  end 

but i'm not sure how link them, before using sps, had 2 queries. outer 1 retrieved user details , inner query executed each row in results set of first i.e. getting relations array each user. when switched basic queries calling stored procedures error commands out of sync.

i can't seem find example of i'm trying achieve. appreciated

update php code looks 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 warning: mysql_fetch_array() expects parameter 1 resource, boolean given referring line 'while ($innerrow = mysql_fetch_array($relations_results)){'. know when print out error associated query, retrieving error message stated above i.e. commands out of sync

i dig little , seems old mysql way of connecting mysql db not supporting multiple stored procedures @ once. not without going multiple connections (as guess) not idea. might stay mysql don't recommend php docs that:

this extension deprecated of php 5.5.0, , removed in future. instead, mysqli or pdo_mysql extension should used. see mysql: choosing api guide , related faq more information. alternatives function include:

mysqli_query()

pdo::query()

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

this how understand current data model:

link sqlfiddle

recommended solution:

i recommend go updated , newer mysqli mysql disappear in time php @ all.

second recommended solution:

you can connect 2 stored procedures one. join 2 tables in 1 select on userid , query using parameter you're providing. can see useridparam in stored procedure can't see used in code. anyway, remember prevent sql injection. stored procedures don't protect 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 users @ once. clear results set after using getusers. iterate on result second stored procedure getrels.