PHP PDO and Stored Function

Question

I have just started looking into php pdo and have connected to mysql database and ran a simple SELECT statement. I have a stored function I created before using pdo, do I actually need to use stored functions/procedures while using PDO?

BEGIN 

  DECLARE new_username VARCHAR(32); 

    SELECT `username` 
      INTO new_username 
      FROM `users` 
     WHERE `userID` = ID; 

RETURN COALESCE(new_username, 'Invalid ID'); 

END

Is there any point in using the above function, if I'm doing this using PDO? The function will be expanded for other selects etc. I'm also having a problem calling the function using PDO.

include ('connection.php');

$userID = 0;
$stmt = $db->prepare("SELECT username(:user_id)");
$stmt->bindParam(':user_id', $userID, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_OBJ);
echo $result->new_username;

Any advice?

CogitoErgoSum · Accepted Answer · 2011-01-14 23:30:53Z

2

For a Stored Procedure you need to alter your syntax slightly. Note the Length must be included.

<?php
$userId = "0"; //This deeclares it a String FYI not an Int in technical terms...
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $userId, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000)

// call the stored procedure
$stmt->execute();

print "procedure returned $return_value\n";
?>

Refer to http://php.net/manual/en/pdostatement.bindparam.php for more details.

In response to if its worth using...that depends. If you create a PHP function i.e getUserName($id) then no its not worth it, you can use the PHP Function and alter that as you go along...Assuming you are developing this for a single PHP Application or with a reusable PHP class.

If you want to let people perhaps use an API to run it etc without tampering or if you intend for this query to be utilized across multiple applications (e.g. PHP, an ASP one also perhaps a Desktop app etc etc) then using MySQL as the place of residence is better.

Personally I prefer PHP Functions in case you have to port databases etc and the ease in which to add more complex logic to the query result etc. MySQL Procedures and Functions to me are constraining when considering migration...

Added Note - Procedures and Functions are different. You've used both names. In this case I am assuming from the code its a Procedure but be sure to understand which you are using and why. They are meant for different things.

answered Jan 14, 2011 at 23:30

CogitoErgoSum

2,9275 gold badges34 silver badges46 bronze badges

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

4 Comments

RobertPitt Over a year ago

PDO::PARAM_INT should make "1" become 1 by using type casting fyi.

Elliott Over a year ago

opps I forgot to change my userID sorry it was meant to be an Int, the code above is a function, as its returning the username. I have of yet used procedures yet but I put that in my question for when I actually do use them. Thanks will give that a try.

Elliott Over a year ago

PHP prints "procedure returned " but nothing after that? Thanks

Serge Velikan Over a year ago

and how to get stored function result?

Collectives™ on Stack Overflow

PHP PDO and Stored Function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related