Jordan Davis Jordan Davis - 7 months ago 22
PHP Question

calling a stored procedure from php

I'm calling a stored procedure from php

<?php
include('global/db.php');
$id = 1;
$base = conn();
$query = "CALL get_id(:id,@userid)";
$stmt = $base->prepare($query);
$stmt->bindParam(':id',$id);
$stmt->execute();
print_r($stmt->fetch());
?>


the stored procedure looks like this

BEGIN
SET @userid = (SELECT * FROM user WHERE user.id = id);
SELECT @userid;
END


the procedure parameters

IN id int(10), OUT userid VARCHAR(255)


Questions:


  • why does my result return nothing?

  • what data type does my output variable
    @userid
    need to be?


Answer

Your Store procedure has some problems
Since you have an output userid why this @userid
Also you are outputing userid , So there is no need to SELECT it

Another thing is , you can't select the entire row into a single variable, for multiple columns you should use multiple output variables.

BEGIN
SET userid = (SELECT column_name FROM user WHERE user.id = id);
END