Not able to display data of stored procedure

Question

I am new into PHP. I created Stored Procedure which displaying results in different tabs as shown below. I am able to print only "Result 1" data, but not able to print Others tabs data. I am getting null when i am trying to print "pid".

index.php

$query=mysqli_query($conn,"call vaamoz_admin.spGetOutletBusinessDetails(101)");

while($row=mysqli_fetch_assoc($query))
{
    $data[]=array("oid"=>$row['oid'],"bn"=>$row['bn'],"bd"=>$row['bd'],"pid"=>$row['pid']); //line 61
}

error

Undefined index: pid in C:\nginx\www\repos\Sportaz-repo\php\getVendorById.php on line 61

result

[{"oid":"101","bn":"Happy Junctions","bd":"Restaurant ","pid":null}]

spGetOutletBusinessDetails

    CREATE PROCEDURE `spGetOutletBusinessDetails`(IN $p_nOutletId INT)
BEGIN


     SELECT  bc.nBusinessDetailsId AS 'oid',
            bc.strBusinessName AS bn,
            bc.strBusinessDescription AS bd,
            o.strOutletUri AS ocp,
            bc.strCreatedOn AS t,
            bc.strUpdatedOn AS uo,
            bc.nItemStatus AS s,
            bc.nItemState AS ist,
            bc.nRequestType AS rt,
            $v_Rating AS r,
            $v_Followers as fc


    FROM tblBusinessDetails bc
    inner join tbloutletcoverimage o on bc.nBusinessDetailsId = o.nBusinessDetailsId
    WHERE bc.nBusinessDetailsId = $p_nOutletId AND bActive = 1;

    SELECT nPackageId AS pid

    FROM tblPackageAssociatedOutlets

    WHERE nOutletId = $p_nOutletId;


    SELECT  t1.nBusinessDetailsId AS 'bid',
            t1.strBusinessName AS 'strn', 
            t3.strLatitude AS 'blat',
            t3.strLongitude AS 'blng',
            t3.strAddressLineThree AS 'badd'
    FROM tblBusinessDetails t1
    INNER JOIN tblVendorBusinessCategory t2 on t1.nBusinessDetailsId = t2.nBusinessDetailsId
    INNER JOIN tblOutletAddresses t3 on t1.nBusinessDetailsId = t3.nBusinessDetailsId
    WHERE   t2.nMainCategoryId=(SELECT nMainCategoryId FROM tblVendorBusinessCategory WHERE nBusinessDetailsId = $p_nOutletId) AND 
            t3.strPincode=(SELECT strPincode FROM tblOutletAddresses WHERE nBusinessDetailsId = $p_nOutletId)
    ORDER BY t1.strBusinessName ASC;
END

Wait it's giving error "It looks like your post is mostly code; please add some more details." so i am reducing it — Ashish Mishra, Mar 24 '20 at 09:01
yes it's as the answer below says - you have multiple different result sets, so you must collect each one separately. — ADyson, Mar 24 '20 at 09:09
@ADyson i am new into PHP so i don't know how to do these things :( — Ashish Mishra, Mar 24 '20 at 09:11
Take a look at this: https://stackoverflow.com/questions/1683794/retrieving-multiple-result-sets-with-stored-procedure-in-php-mysqli — ADyson, Mar 24 '20 at 09:20

score 1 · Answer 1 · answered Mar 24 '20 at 08:54

Looks like your procedure returns multiple result sets. Therefore your PHP code should also handle all the incoming result sets. The reason you are getting the error is that the pid seems to be on the second result set and you are trying to access it as if it was in the first (mysqli_query handles just the first result set).

Take a look at the mysqli's multi_query

Not able to display data of stored procedure

1 Answers1