PHP PDO和存储的功能 [英] PHP PDO and Stored Function

问题描述

我刚刚开始研究php pdo并已连接到mysql数据库并运行了一个简单的SELECT语句.我在使用pdo之前创建了一个存储函数，实际上在使用PDO时是否需要使用存储函数/过程?

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

如果我正在使用PDO进行操作，那么使用上述功能有什么意义吗?该功能将扩展为其他选择等.我在使用PDO调用该功能时也遇到了问题.

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;

有什么建议吗?

推荐答案

对于存储过程，您需要稍微更改语法.请注意，必须包含长度.

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";
?>

请参考 http://php.net/manual/zh-cn/pdostatement.bindparam. php 了解更多信息.

作为对它是否值得使用的响应……这取决于.如果您创建一个PHP函数，即getUserName($ id)，那么就不值得了，您可以使用PHP函数并在进行过程中对其进行更改...假设您是针对单个PHP应用程序或可重用的PHP开发该函数课.

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.

如果您想让人们使用API​​来运行它而不会被篡改，或者您打算将此查询用于多个应用程序(例如PHP，ASP或桌面应用程序等)，则可以使用MySQL居住的地方更好.

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.

我个人更喜欢PHP函数，以防万一您必须移植数据库等，以及向查询结果中添加更复杂的逻辑的便捷性等.考虑迁移时，MySQL的过程和函数对我来说是有限制的...

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.

这篇关于PHP PDO和存储的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！