php Zend Framework - 在 select 子句中计算行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2953196/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Zend Framework - counting rows in select clause?
提问by moogeek
I'm investigating Zend Framework and currently stucked in counting resulting rows of sql query... Every method I try (from documentation and some blogposts and tutorials) returns an error (like Call to undefined function) or simply gives the incorrect value.
我正在研究 Zend 框架，目前一直在计算 sql 查询的结果行......我尝试的每种方法（来自文档和一些博客文章和教程）都会返回错误（如Call to undefined function）或只是给出不正确的值。
I've tried this:
我试过这个：
$checkquery = $db->select()
->from('users', 'COUNT(*)')
->where('login = ?', $login)
->where('password = ?', $password)
->query();
$checkrequest=fetchRow($checkquery)->num;
...then this one:
...然后这个：
$checkquery = $db->select()
->from('users', '*')
->where('login = ?', $login)
->where('password = ?', $password)
->query();
$checkrequest=count($checkquery->fetchAll());
and even:
乃至：
$checkquery = $db->select()
->from('users', '*')
->where('login = ?', $login)
->where('password = ?', $password)
->query();
$checkrequest=$checkquery->fetchAll()->num;
Also rowCount()and count(fetchRow())and count(fetchAll()->toArray()). But always I got an error message or duplicate inserts in db in further insert function. So what is the correct way to do the resulting row calculation in select clause in Zend Framework 1.9 (I use this one) ?
还rowCount()和count(fetchRow())和count(fetchAll()->toArray())。但我总是在进一步的插入函数中收到错误消息或重复插入数据库。那么在 Zend Framework 1.9（我使用这个）的 select 子句中进行结果行计算的正确方法是什么？
回答by Bill Karwin
The usage you're trying to do is as follows:
您尝试使用的用法如下：
$checkquery = $db->select()
->from("users", array("num"=>"COUNT(*)"))
->where("login = ?", $login)
->where("password = ?", $password);
$checkrequest = $db->fetchRow($checkquery);
echo $checkrequest["num"];
I have a couple of other tips:
我还有一些其他提示：
- Your query doesn't distinguish between login not found and incorrect password.
- Your passwords may be stored in plain text, which is a security risk. You should use a one-way hash function and salting.
- 您的查询不区分未找到登录名和错误密码。
- 您的密码可能以纯文本形式存储，这存在安全风险。您应该使用单向哈希函数和 salting。
I would restructure the query like this:
我会像这样重构查询：
$checkquery = $db->select()
->from("users", array("pwd_is_correct"=>
$db->quoteInto("(password = SHA1(CONCAT(salt, ?)))", $password)))
->where("login = ?", $login);
$checkrequest = $db->fetchRow($checkquery);
if ($checkrequest === false) {
echo "no such login\n";
} else if ($checkrequest["pwd_is_correct"] > 0) {
echo "login and password are correct\n";
} else {
echo "login found but password is incorrect\n";
}
You don't have to report the different cases to the user -- in fact it's best security practice notto tell them which of the login or password is incorrect. But you might want to know in your own code so you can lock out an account that's receiving a lot of failed passwords.
您不必向用户报告不同的情况——事实上，最好的安全做法是不要告诉他们哪个登录名或密码不正确。但是您可能想在自己的代码中知道，这样您就可以锁定收到大量失败密码的帐户。
SHA1()is not as good as SHA2()which is available in MySQL 5.5and later.
SHA1()是不如SHA2()它可以在MySQL 5.5和更高版本。
