Read data from Stored Procedure using PHP

The PHP DB libraries include a function ***_next_result that allows you to iterate over multiple result sets returned from a stored procedure.

Your options (if you are using MSSQL)
PDOStatement::nextRowSet()
sqlsrv_next_result()
odbc_next_result()

Are you using MS SQL and if so which library are you using to access it?

Hi Julian,

How if there are two grids to store two data from SP for each  ?

Thank you.

This is a very open ended question. What you are talking about involves many moving parts which if not described make suggesting a solution difficult.

Please describe your setup in detail so that there is enough information to formulate an answer.

Things to include
Where are your grids, how are you implementing them?
What do your server side scripts look like - we know PHP but is there an API, do your scripts render the page, are you using AJAX?

Assuming you have two grids and you are refreshing by AJAX and you are refreshing at the same time then your AJAX request calls a script that bundles the results into an object that you can then access independently for the two grids. In other words you return an object (or array) with the two resultsets and then in your AJAX complete you bind each of the grids to the correct result set.

I mean how to make it 2 blocks of 2 data for fill 2 grids each.

Block 1 for first select:
Looping
   
end looping

blokc 2 for second select :
Looping
   
end looping

I still confuse how to use command next result.

Thank you.

Hi Julian,

Thank you very much for your help.

Hi Julian,

What if I use PDO ?

Thank you.

PDO is my personal preference and the process is the same - just with the PDO functions instead of sqlsrv
[Disclaimer: this is untested code and provided to illustrate the process]

// Modify for your requirements
$db = new PDO("sqlsrv:server=address;Database=database", "user", "password");
$stmt = $db->query("sp_stored_procedure");
// Do some sanity checks here
$result = new stdClass;
if ($stmt) {
  $result->result1 = $stmt->fetchAll(PDO::FETCH_OBJ);
}
if ($stmt->nextRowset()) 
   $result->'result2 = $stmt->fetchAll(PDO::FETCH_OBJ);
}
die(json_encode($result));

Select allOpen in new window

You might want to include additional information in your return to indicate errors if one or both result sets are not available.
In the above case the returned object will not have a property set for a result set that was not available. In the client I would do

$.post('myscript.php',data, function(resp) {
  if (resp.result1) {
    // populate grid 1
  } else {
    // handle error
  }
  if (resp.result2) {
    // populate grid 2
  } else {
    // handle error
  }
}, 'JSON');

Select allOpen in new window

Great. Thank you very much.