php - 仅当数据存在于另一个 PHP 中时如何在表中插入数据

Question

我想知道如何仅当其中一个参数也存在于另一个表中时才能将一行插入表中。

我有两个表，Goals [goal_id, username...] Activities[username, goal_id, activity_id..]。

具有 1-many carnality，因此新事件必须具有目标 ID。我已经设置了一个 REST 服务，问题是当前用户可以插入一个连接到不属于他们的目标的事件，所以我正在寻找一种方法来 INSERT 只有 <提供的 em>goal_id 在 user_goal 表中属于它们。

if (isset($_POST['activity'])) {
    $title = $_POST['title'];
    $description = $_POST['description'];
    $connected_goal = $_POST['connected_goal'];
    if (!isset($_POST['title']) || !isset($_POST['description']) || !isset($_POST['connected_goal'])) {
        echo "Missing fields in the request! New activities MUST contain; title, description AND connected_goal";
    }
    else {
        if ($stmt = $mysqli->prepare("insert into user_activity(username,title,description,goal_id) values(?,?,?,?)")) {
            $stmt->bind_param("sssi", $apiusername, $title, $description, $connected_goal);
            $stmt->execute();
            $stmt->close();
            echo "ACTIVITY ACCEPTED";
        }
        else {
            echo "POST Request failed, please check all parameters against documentation.";
        }
    }
}
}

Answer 1

这应该是一个简单的连接语句，用于查找该用户名的记录是否存在于另一个表中。试试下面的查询

SELECT table1.username, table1.goal_id, COUNT(table2.username) as total FROM table1
LEFT JOIN table2 ON table2.username=table1.username
GROUP BY table1.username
HAVING total >=1