Return MySQL stored procedure result set using php

Question

I'm a new to php and am having trouble getting a result set from a stored procedure, I'd like the answer in a procedural format if possible.

The stored procedure is as follows:

DROP PROCEDURE IF EXISTS getJobs;
DELIMITER $$
CREATE PROCEDURE getJobs(IN inputName VARCHAR(50))
BEGIN 
SELECT JobNo FROM jobs WHERE PersonnelName= inputName;
END $$
DELIMITER ;

In MySQL Workbench this returns 30539

I've come up with the following php code.

<?php
//imports the connection
require "conn.php";

$name = "chrisf";

$call = mysqli_prepare($conn, 'CALL getJobs(?)');
mysqli_stmt_bind_param($call, 's', $name);
mysqli_stmt_execute($call);
$result = mysqli_use_result($conn);

//debug to check the result
echo '<pre>'; print_r($result); echo '</pre>';
//loop the result set and echo
while ($row = mysqli_fetch_array($result)){   
//the command I expect to output the result
  echo "Entry" . $row[0] . "<br>"; 
//debug to check the result
 echo "Entry" .  $row . "<br>"; 
}

$conn->close();
?>

When run this provides the following output:

mysqli_result Object
(
[current_field] => 0
[field_count] => 1
[lengths] => 
[num_rows] => 0
[type] => 1
)

 Entry
 EntryArray

As the mysqli_result Object disappears if I use an input which returns no results, this seems to be close but it doesn't actually contain the data.

Thanks in advance.

Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and ideally should not be used in new code. — tadman, Feb 19 '19 at 20:15
Note: Defining stored procedures for trivial queries like this tends to over-complicate your applications. Why not just run the query directly? — tadman, Feb 19 '19 at 20:15
Have you checked for mysql errors? Try something like `var_dump(mysqli_error($conn));` — Thomas Leu, Feb 19 '19 at 20:35
What happens if you just prepare and execute the query directly i.e. `$call = mysqli_prepare($conn, 'SELECT JobNo FROM jobs WHERE PersonnelName= ?');` — Nick, Feb 19 '19 at 20:36
@tadman I was planning to move on to object orientation and PDO once I had a firm understanding of procedural. The stored procedure is just a learning tool, I've got PHP to echo a stored procedure result set with no input parameters and echo an output parameter from a stored procedure with or without input parameters, echoing a result set using when using an input parameter seems to be the final permutation, but is currently beyond me. — GMar, Feb 20 '19 at 20:14
@Thomas Leu The MySQL executes correctly using MySQL Workbench, I'm sure the issue is the PHP — GMar, Feb 20 '19 at 20:16
Don't get me wrong, learning is fine, but the procedural style is an anachronism and won't teach you anything but bad, old habits. PDO is a much cleaner database layer in general, and supports things like named placeholders that help considerably when writing queries. — tadman, Feb 20 '19 at 21:19

score -1 · Accepted Answer · edited Jun 13 '21 at 11:08

It doesn't seem very elegant, but I've come up with the following, which satisfies me enough to move on:

<?php
//imports the connection
require "conn.php";

//builds the MySQL stored procedure all
$name = "chrisf";
$input = "CALL getEngineersJob('".$name."')";

//executes the store procedure
$result = mysqli_query($conn,$input);

//loop through the output and echo
while ($row = mysqli_fetch_array($result)){   
   echo $row[0] . "<br>"; 
}

Return MySQL stored procedure result set using php

1 Answers1

Linked