php - PHP 查询的逻辑顺序

Question

我刚刚为页面的基本轮廓编写了一些代码。它适用于我运行的游戏，但查看代码，我觉得我以错误的顺序编写了它，并且我执行的查询比我需要的要多。

    $query = mysql_query("SELECT * FROM bodyguards WHERE username='$u'");    if($bg = mysql_fetch_assoc($query)) { // if you have a bg        if($bg[status] == "active") {            echo "your bodyguard is $bg[bodyguard], kick him?";        } else {            echo "invite a bg?";        }    } else {        $query = mysql_query("SELECT * FROM bodyguards WHERE bodyguard='$u' AND status='active'");        if($bg = mysql_fetch_assoc($query)) { // if you are a bg            echo "you are the bodyguard of $bg[username]";        } else {            //otherwise check if anyone has invited you            $query = mysql_query("SELECT * FROM bodyguards WHERE bodyguard='$u' AND status='invited'");            while($temp = mysql_fetch_assoc($query)) {                echo "$temp[username] has invited you to be their bodyguard, accept or decline?";            }        }    }

我只使用一个数据库表。如果玩家 1 有一个已确认的保镖(玩家 2)，该行将如下所示:

用户名=>玩家1，保镖=>玩家2，状态 => 事件。

这里有人会以不同的方式编写代码吗？

Answer 1

我会在一个查询中完成它:

$query = mysql_query("SELECT * FROM bodyguards WHERE username='$u' OR (bodyguard='$u' AND (status='active' OR status='invited')");

$bg = mysql_fetch_assoc($query);

if ($bg) { // check for empty result first, to prevent E_NOTICE
    if ($bg['username'] == $u) {
        if($bg['status'] == "active") {
            echo "your bodyguard is {$bg['bodyguard']}, kick him?";
        } else {
            echo "invite a bg?";
        }
    } else if ($bg['bodyguard'] == $u) {
        if ($bg['status'] == "active") {
            echo "you are the bodyguard of {$bg['username']}";
        } else if ($bg['status'] == "invited") {
            echo "{$bg['username']} has invited you to be their bodyguard, accept or decline?";
        }
    }
}

注意:如果数据是由用户提供的，请确保使用 mysql_real_escape_string 转义 $u。另外，请确保引用数组索引以防止 PHP 通知(即:使用 $bg['username'] 而不是 $bg[username])。