如果数据库结果存在，则执行此操作，否则，执行该操作

Question

我正在检查数据库以查看是否存在数据匹配。如果有，则输出一些 HTML，如果没有，则输出不同的 HTML。

由于某种原因，此查询不起作用。数据库中的数据确实提供了匹配项，但未输出第一个 HTML。

我

有一种感觉，这与我使用"FALSE"有关......我应该以不同的方式做这件事吗？

<?php
// Check current favourite status
$sql = "SELECT * FROM tbl_favourites WHERE user_id = :who AND favourite_id = :usernum AND favourite_active = '1' LIMIT 1";
$q   = $conn->prepare($sql); // the default way of PDO to manage errors is quite the same as `or die()` so no need for that
$q->bindValue(':who',$who,PDO::PARAM_INT);
$q->bindValue(':usernum',$usernum,PDO::PARAM_INT);
$q->execute();
if($r = $q->fetch(PDO::FETCH_ASSOC)!== false)
{
    echo "<div id="favourite" class="favouriteOptionActive" onclick="loadXMLDoc('indexFavourite')">Selected as favourite</div>";
}       
else
{
    echo "<div id="favourite" class="favouriteOption" onclick="loadXMLDoc('indexFavourite')">Add me as a favourite</div>";
}
?>

Answer 1

与其

做SELECT *不如做SELECT COUNT(*)，因为你实际上并没有使用任何查询的数据。

$sql = "SELECT COUNT(*) FROM tbl_favourites WHERE user_id = :who AND favourite_id = :usernum AND favourite_active = '1' LIMIT 1";
$q   = $conn->prepare($sql);
$q->bindValue(':who',$who,PDO::PARAM_INT);
$q->bindValue(':usernum',$usernum,PDO::PARAM_INT);
$q->execute();
$found = $q->fetchColumn();
if ($found > 0) {
    // Do stuff
}
else {
    // Do other stuff
}

另请注意，您的评论the default way of PDO to manage errors is quite the same as or die() so no need for that是错误的。PDO处理错误的默认方式是静默。如果需要其他两种错误模式中的任何一种，则需要自行设置它们。

Answer 2

<?php
$sql = "SELECT 1 FROM tbl_favourites WHERE user_id = ? AND favourite_id = ? 
        AND favourite_active = 1 LIMIT 1";
$q   = $conn->prepare($sql); 
$q->execute([$who, $usernum]);
$exists = $q->fetchColumn();
?>
<div id="favourite" class="favouriteOption<? if ($exists): ?>Active<? endif ?>" onclick="loadXMLDoc('indexFavourite')">
<? if ($exists): ?>
    Selected as favourite
<? else: ?> 
    Add me as a favourite
<? endif ?>
</div>