Question

我正使用选中的查询从 TUsers 中获取三个值。

Mysqli 语句执行错误 : 结果由多个行组成。

我使用DistintsROW来避免重复,我使用Zend框架来调用程序。

这是代码:

程序:

         CREATE DEFINER=`root`@`` PROCEDURE `spfetchloginid`(in securityans   varchar(50),out email varchar(50),out loginidout varchar(50),out useridout varchar(50))
         BEGIN

         SELECT DISTINCTROW Email,login_id,user_id into email,loginidout,useridout  FROM DB.TUsers where SecurityAns=securityans ;

         END

主计长在ZendFramework中呼吁程序:

            $db=Zend_Db_Table::getDefaultAdapter();
        $spParams = array(1, NewValue );
        $stmt = $db->query("CALL   spfetchloginid( $securityans ,@email,@loginidout,@useridout)");

        print_r($stmt->fetchAll());
        $stmt->closeCursor();   

        $stmtresult10=$db->query("select @email");
        $email_to=$stmtresult10->fetch();
        $stmtresult10->closeCursor();
        $Emails=$email_to["@email"];
         echo $Emails;  

        $stmtresult11=$db->query("select @loginidout");
        $loginid=$stmtresult11->fetch();
        $stmtresult11->closeCursor();

        $loginids=$loginid["@loginidout"];
        echo $loginids; 

        $stmtresult12=$db->query("select @useridout");
        $userid=$stmtresult12->fetch();
        $stmtresult12->closeCursor();

        $userids=$userid["@useridout"]; 

        echo $userids;

请告诉我在使用Zend和My Sql 来通知程序时有什么好的建议。

Answer 1

http://dev.mysql.com/doc/refman//5.5/en/select.html" rel=“nofollow”>fine 手册:

DISTINCT 指定从结果集中删除重复行。 [.] DISTINCTROW 是 DISTINCT 的同义词。

因此,我这样说:

SELECT DISTINCTROW Email, login_id, user_id
...
where SecurityAns = securityans

仅从结果集中删除重复内容, 但如果您有多个多行的同一种 SecurityAns , 而且它们都是 securityans , 那么您的查询将返回多行。如果 SecurityAns 是“ 你最喜欢的颜色是什么” 或“ 你母亲的婚前姓氏是什么” 等标准问题的答案, 那么您应该期待很多重复内容, 所以 SecurityAns 肯定不足以保证独特性。

您需要添加更多条款来保证独到的结果。或者,您可以添加 LIMIT 1, 但这只是一个绷带在真正的问题上。

你可能还遇到一个问题你的地方条款:

where SecurityAns = securityans

/ code 列名称,而不是 securityans 参数。尝试使用不同的参数名称。例如,我用这样的程序看到你的重复问题:

create procedure p(in id int, out result int)
begin
    select id into result from t where id = id;
end

但不在此版本中 :

create procedure p(in find_id int, out result int)
begin
    select id into result from t where id = find_id;
end

Answer 2

Supposing that all records have the same value in column SecurityAns but different values for all other columns (usually each user has it s own email address, right?), no matter you use distinct or distinct row, your query will retrieve always more then one row. So saying this, how do you want to assign multiple values to out variables? In and out parameters in Mysql procedures are atomic variables that can only be given one and ONE ONLY value. If you want to query a table for more than one row and do something with the retrieved data, I suggest you to do that using PHP and forget MySql stored procedures.

友情链接