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

Question

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?

voodoo417 · Accepted Answer · 2014-01-23 18:41:40Z

1

Change your store procedure body to:

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

edited Jan 23, 2014 at 18:41

answered Jan 23, 2014 at 18:34

voodoo417

12.2k3 gold badges39 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Roy Over a year ago

I'm looking to select fields from a number of related tables. I figured I might have to use joins, but I was hoping to avoid them because I'll wind up passing quite a bit of repetitive data between the client-side and database. Would it be more appropriate to live with all the repetitive data, or rather to create a separate stored procedure for each related table and just make multiple database calls?

voodoo417 Over a year ago

@Roy Sorry, i dont understand you now. I see 2 tables are related by ProjectID. "number of related table" - what is means?

Roy Over a year ago

tblProjects is my "Main" table, but I have about 10 other tables that have a ProjectID foreign key to link the tables (tblSpecies, tblHabitats, tblFocalAreas, etc.). Let's say, for example, I wanted to get all of the information for a given project. I'd need to do a LEFT JOIN on ~10 tables in the same query, and the result would be a HUGE piece of JSON to send back to the application.

voodoo417 Over a year ago

what a problem if response will be huge? Or very huge?

Roy Over a year ago

I was expecting it to slow down the application, but that's something I'll just have to test. Thanks!

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related