Member Avatar for veledrom

Hi,

Stored proc code below works fine in MySQL command prompt. It returns John because it is in table. However, i can't return or read the Out parametter in PHP. I mean i cant retrieve John. I did ask this in PHP forum but no answer.

CREATE PROCEDURE `findname`(IN name_in varchar(15), OUT name_out varchar(15))
BEGIN
	SELECT name INTO name_out FROM table WHERE name=name_in;
END;

CALL findname('John', @out);
SELECT @out;
$query = "CALL findname('John', @err);";
$run = mysql_query("SELECT @err");

What do i write to check if result is returns John or not?

Thanks

  • 2 Contributors
  • 3 Replies
  • 88 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by varmadba

Dani AI

Generated

Short answer: the OUT value comes back as a session (user) variable on the same connection, and PHP must both execute the CALL and then read that variable on the same connection after clearing any result sets the server returned. is correct that user variables are connection-specific; that is the usual cause when the CLI shows the value but PHP does not.

A reliable pattern (PDO) — prepare and execute the CALL on one PDO connection, free the CALL's results, then SELECT the user variable:

$pdo = new PDO(..., [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$stmt = $pdo->prepare("CALL findname(:in, @outvar)");
$stmt->bindValue(':in', $name);
$stmt->execute();
$stmt->closeCursor();   // important: free stored-proc result sets
$out = $pdo->query("SELECT @outvar AS val")->fetch(PDO::FETCH_ASSOC);
echo $out['val'];

If you use mysqli, the important step is flushing any extra result sets before issuing the follow-up SELECT. Failure to do this makes the SELECT return nothing or cause an error:

$mysqli = new mysqli(...);
$mysqli->query("CALL findname('John', @o)");
while ($mysqli->more_results()) $mysqli->next_result();   // flush
$row = $mysqli->query("SELECT @o AS v")->fetch_assoc();
echo $row['v'];

Troubleshooting checklist:

  • Confirm the CALL actually ran on the same connection (common mistake: build the CALL string but never execute it).
  • Free/flush stored-proc result sets (PDO::closeCursor or mysqli->next_result()) before SELECTing the user var.
  • Check error messages ($pdo exceptions or $mysqli->error).
  • As a simpler alternative, have the procedure return the value in a SELECT result set (then fetch it directly).

For background on session variables and stored-proc behavior see the MySQL docs on user variables and PHP's guidance for stored procedures: User-Defined Variables and mysqli stored-procedures quickstart.

Recommended Answers

All 3 Replies

Member Avatar for varmadba

Why cant you run a single query

SELECT name FROM table WHERE name=name_in;

Some thing like this....

$sql = "SELECT name FROM table WHERE nam='$name_out'";
$result = mysql_query($sql, $db);
$row = mysql_fetch_row($result);
$name_from_db=$row[0];

if($name_out = $$name_from_db)
{
echo "It's John";
exit;
}

I am not expert in Php though

Member Avatar for veledrom

Because, i want to learn how to read OUT parameter.

Member Avatar for varmadba

Just My taught

@out variable means you are creating a temporary variable
Temporary variables are connection specific

make sure these both are executed on one connection

$query = "CALL findname('John', @err);";
$run = mysql_query("SELECT @err");
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.