CODEWITHSUNDEEP
phpsqlsql-serverstored-proceduresprepared-statement
Roy
Roy

Reputation: 1518

How do I return multiple results from a SQL Server Stored Procedure with PHP prepared statements?

I have an application tied to an SQL Server 2012 instance. I'm trying to return a record from the main table tblProjects and all records from a related table tblPartners. When I execute the stored procedure in SQL Server I get results from both tables:

EXEC getProjectDetails @prjID = 14

enter image description here

My stored procedure:

CREATE PROCEDURE getProjectDetails @prjID int = NULL
AS
BEGIN
    SELECT ProjectID, ProjectTitle, Latitude, Longitude, Acreage, LastModifiedDate, LastModifiedBy, Boundary.STAsText() As Boundary
    FROM tblProjects 
    WHERE ProjectID = @prjID

    SELECT PartnerName As Name, Type, Role, ProjectID
    FROM tblPartners
    WHERE ProjectID = @prjID
END
GO

The PHP:

<?php
    $prjID = $_GET["prjID"];

    include_once("connection.php");
    $connectionInfo = array("Database"=>"$dbname", "UID"=>$user, "PWD"=>$pass);
    $DBH = sqlsrv_connect($server, $connectionInfo);

    if( !$DBH ) {
        echo "Connection could not be established.<br />";
    }

    $sql = "{CALL getProjectDetails(?)}";
    $params = array(&$prjID);

    if (!$stmt = sqlsrv_prepare($DBH, $sql, $params) ) {
        echo "Statement could not be prepared.\n";
    }

    if ( !$query = sqlsrv_execute( $stmt ) ) {
        echo "Statement could not be executed.\n";
    }

    $result = array();
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ) {
        $result[] = $row;
    }

    print json_encode($result);

Resulting output only displays results from the first query: enter image description here

I'm new at this; is it possible to get results for more than one table in this or a related way?

Upvotes: 2

Views: 1710

Answers (1)

voodoo417
voodoo417

Reputation: 12101

Change your store procedure body to:

SELECT *
FROM tblProjects 
LEFT JOIN  tblPartners 
ON ( tblProjects.ProjectID = tblPartners.ProjectID  )
WHERE tblProjects.ProjectID = @prjID

Upvotes: 1

Related Questions