检查空结果（php、pdo、mysql）

Checking for empty result (php, pdo, mysql)

提问by canoebrain

Please, can anyone tell me what I'm doing wrong here? I'm simply retrieving results from a table then adding them to an array. Everything works as expected until I check for an empty result...

拜托，谁能告诉我我在这里做错了什么？我只是从表中检索结果，然后将它们添加到数组中。一切都按预期工作，直到我检查结果为空...

This gets the match, adds it to my array and echoes the result as expected:

这将获取匹配项，将其添加到我的数组中并按预期回显结果：

$today = date('Y-m-d', strtotime('now'));

$sth = $db->prepare("SELECT id_email FROM db WHERE hardcopy = '1' AND hardcopy_date <= :today AND hardcopy_sent = '0' ORDER BY id_email ASC");

$sth->bindParam(':today',$today, PDO::PARAM_STR);

if(!$sth->execute()) {
    $db = null ;
    exit();
}

while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $this->id_email[] = $row['id_email'] ;
    echo $row['id_email'] ;
}

$db = null ;
return true ;

When I try to check for an empty result, my code returns 'empty', but no longer yields the matching result:

当我尝试检查空结果时，我的代码返回“空”，但不再产生匹配结果：

$today = date('Y-m-d', strtotime('now'));

$sth = $db->prepare("SELECT id_email FROM db WHERE hardcopy = '1' AND hardcopy_date <= :today AND hardcopy_sent = '0' ORDER BY id_email ASC");

$sth->bindParam(':today',$today, PDO::PARAM_STR);

if(!$sth->execute()) {
    $db = null ;
    exit();
}

if ($sth->fetchColumn()) {
    echo 'not empty';
    while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
        $this->id_email[] = $row['id_email'] ;
        echo $row['id_email'] ;
    }
    $db = null ;
    return true ;
}
echo 'empty';
$db = null ;
return false ;

As always, any help is appreciated. Thanks!

与往常一样，任何帮助表示赞赏。谢谢！

回答by Marc B

You're throwing away a result row when you do $sth->fetchColumn(). That's not how you check if there's any results. you do

当你这样做时，你会扔掉一个结果行$sth->fetchColumn()。这不是你检查是否有任何结果的方式。你做

if ($sth->rowCount() > 0) {
  ... got results ...
} else {
   echo 'nothing';
}

relevant docs here: http://php.net/manual/en/pdostatement.rowcount.php

相关文档在这里：http: //php.net/manual/en/pdostatement.rowcount.php

回答by josh123a123

If you have the option of using fetchAll() then if there are no rows returned it will just be and empty array.

如果您可以选择使用 fetchAll() ，那么如果没有返回行，它将只是空数组。

count($sql->fetchAll(PDO::FETCH_ASSOC))

will return the number of rows returned.

将返回返回的行数。

回答by stubsthewizard

Even though this is an old thread, I thought I would weigh in as I had to deal with this lately.

尽管这是一个旧线程，但我认为我会权衡一下，因为我最近不得不处理这个问题。

You should not use rowCount for SELECT statements as it is not portable. I use the isset function to test if a select statement worked:

不应将 rowCount 用于 SELECT 语句，因为它不可移植。我使用 isset 函数来测试 select 语句是否有效：

$today = date('Y-m-d', strtotime('now'));

$sth = $db->prepare("SELECT id_email FROM db WHERE hardcopy = '1' AND hardcopy_date <= :today AND hardcopy_sent = '0' ORDER BY id_email ASC");

//I would usually put this all in a try/catch block, but kept it the same for continuity
if(!$sth->execute(array(':today'=>$today))) 
{
    $db = null ;
    exit();
}

$result = $sth->fetch(PDO::FETCH_OBJ)

if(!isset($result->id_email))
{
    echo "empty";
}
else
{
    echo "not empty, value is $result->id_email";
}

$db = null;

Of course this is only for a single result, as you might have when looping over a dataset.

当然，这仅适用于单个结果，就像您在遍历数据集时可能遇到的那样。

回答by Mahmoud Ali

$sql = $dbh->prepare("SELECT * from member WHERE member_email = '$username' AND member_password = '$password'");

$sql->execute();

$fetch = $sql->fetch(PDO::FETCH_ASSOC);

// if not empty result
if (is_array($fetch))  {
    $_SESSION["userMember"] = $fetch["username"];
    $_SESSION["password"] = $fetch["password"];
    echo 'yes this member is registered'; 
}else {
    echo 'empty result!';
}

回答by Your Common Sense

what I'm doing wrong here?

我在这里做错了什么？

Almost everything.

几乎所有的东西。

$today = date('Y-m-d'); // no need for strtotime

$sth = $db->prepare("SELECT id_email FROM db WHERE hardcopy = '1' AND hardcopy_date <= :today AND hardcopy_sent = '0' ORDER BY id_email ASC");

$sth->bindParam(':today',$today); // no need for PDO::PARAM_STR

$sth->execute(); // no need for if
$this->id_email = $sth->fetchAll(PDO::FETCH_COLUMN); // no need for while

return count($this->id_email); // no need for the everything else

effectively, you alwayshave your fetched data (in this case in $this->id_emailvariable) to tell whether your query returned anything or not. Read more in my article on PDO.

实际上，您始终拥有获取的数据（在本例中为$this->id_email变量）来判断您的查询是否返回任何内容。在我关于 PDO 的文章中阅读更多内容。

回答by lango

One more approach to consider:

要考虑的另一种方法：

When I build an HTML table or other database-dependent content (usually via an AJAX call), I like to check if the SELECT query returned any data before working on any markup. If there is no data, I simply return "No data found..." or something to that effect. If there is data, then go forward, build the headers and loop through the content, etc. Even though I will likely limit my database to MySQL, I prefer to write portable code, so rowCount() is out. Instead, check the the column count. A query that returns no rows also returns no columns.

当我构建 HTML 表或其他依赖于数据库的内容（通常通过 AJAX 调用）时，我喜欢在处理任何标记之前检查 SELECT 查询是否返回任何数据。如果没有数据，我只需返回“未找到数据...”或类似的内容。如果有数据，则继续，构建标题并循环内容等。尽管我可能会将我的数据库限制为 MySQL，但我更喜欢编写可移植的代码，因此 rowCount() 已被淘汰。相反，请检查列数。不返回行的查询也不返回列。

$stmt->execute();
$cols = $stmt->columnCount(); // no columns == no result set
if ($cols > 0) {
    // non-repetitive markup code here
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

回答by canoebrain

Thanks to Marc B's help, here's what worked for me (note: Marc's rowCount() suggestion could work too, but I wasn't comfortable with the possibility of it not working on a different DB or if something changed in mine... also, his select count(*) suggestion would work too, but, I figured because I'd end up getting the data if it existed anyways, so I went this way)

感谢 Marc B 的帮助，这对我有用（注意：Marc 的 rowCount() 建议也可以工作，但我对它不能在不同的数据库上工作或如果我的东西发生变化的可能性感到不舒服......也，他的 select count(*) 建议也可以，但是，我想是因为如果数据存在，我最终会得到数据，所以我就这样走了）

$today = date('Y-m-d', strtotime('now'));

$sth = $db->prepare("SELECT id_email FROM db WHERE hardcopy = '1' AND hardcopy_date <= :today AND hardcopy_sent = '0' ORDER BY id_email ASC");

$sth->bindParam(':today',$today, PDO::PARAM_STR);

if(!$sth->execute()) {
    $db = null ;
    exit();
}

while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $this->id_email[] = $row['id_email'] ;
    echo $row['id_email'] ;
}
$db = null ;

if (count($this->id_email) > 0) {
    echo 'not empty';
    return true ;
}
echo 'empty';
return false ;

回答by hamish

I only found one way that worked...

我只找到了一种有效的方法...

$quote = $pdomodel->executeQuery("SELECT * FROM MyTable");

//if (!is_array($quote)) {  didn't work
//if (!isset($quote)) {  didn't work

if (count($quote) == 0) {   //yep the count worked.
    echo 'Record does not exist.';
    die;
}