无法通过PHP和MySQL获得接受的请求

Question

I'm developing an application for Android where user can send some challenge request to the other user and after that challenge request is accepted i'm updating status in that table for particular row and now i should be able to retrieve those accepted requests. I'm retrieving successfully pending challenge requests, but accepted challenge requests i can't retrieve. Actually i'm getting all rows from that table which is not good.

This is how my table challenges looks:

id

user_one_id reference to the table users with user_id from there

user_two_id reference to the table users with user_id from there

status

This is how i'm retrieving pending requests:

SELECT u.name, c.user_one_id, c.user_two_id, c.status, c.created_at, 
c.isRead, c.id FROM users u, challenges c 
WHERE c.user_two_id = ? AND c.status = 0 AND u.id = c.user_one_id 
ORDER BY c.created_at ASC

and this is how i tried to retrieve accepted requests, but not getting good results:

SELECT u.name, c.user_one_id, c.user_two_id, c.status, c.created_at, c.isRead, c.id FROM users u, challenges c 
WHERE c.user_one_id = ? AND 
    c.user_two_id = ? AND c.status = 1 AND u.id = c.user_one_id OR u.id = c.user_two_id 
ORDER BY c.created_at ASC

One note: It should be consider that sometimes the user that is going to be logged in could be the user with the id one or id two from table challenges because he could be maybe the one who is sending the request or someone who is receiving request.

UPDATE:

MySQL:

public function acceptedChallenges($user_one_id, $user_two_id) {
    $stmt = $this->conn->prepare("SELECT u.name, c.user_one_id, c.user_two_id, c.status, c.created_at, c.isRead, c.id FROM users u, challenges c 
                                WHERE c.user_one_id = ? AND 
                                        c.user_two_id = ? AND c.status = 1 AND (u.id = c.user_one_id OR u.id = c.user_two_id)");
    $stmt->bind_param("ii", $user_one_id, $user_two_id);
    if ($stmt->execute()) {
        $request = $stmt->get_result();
        $stmt->close();
        return $request;
    } else {
        return NULL;
    }
}

Below i'm calling function:

$app->post('/accepted_challenges', 'authenticate', function() use ($app) {

verifyRequiredParams(array('user_one_id', 'user_two_id'));

$response = array();
$db = new DbHandler();

$user_one_id = $app->request->post('user_one_id');
$user_two_id = $app->request->post('user_two_id');

$requests = $db->acceptedChallenges($user_one_id, $user_two_id);

if ($requests != NULL) {

    $response["error"] = false;
    $response["requests"] = array();

    while ($request = $requests->fetch_assoc()) {
        $tmp = array();
        $tmp["name"] = $request["name"];
        $tmp["id"] = $request["id"];
        $tmp["user_one_id"] = $request["user_one_id"];
        $tmp["user_two_id"] = $request["user_two_id"];
        $tmp["created_at"] = $request["created_at"];
        $tmp["isRead"] = $request["isRead"];

        array_push($response["requests"], $tmp);
    }
} else {
    $response["error"] = true;
    $response["message"] = "No pending requests.";
}

echoRespnse(200, $response);
});

Answer 1

This should return the result the way you need

SELECT u.name, c.user_one_id, c.user_two_id, c.status, c.created_at, c.isRead, c.id FROM users u, challenges c 
WHERE c.user_one_id = ? AND 
    c.user_two_id = ? AND c.status = 1 AND (u.id = c.user_one_id OR u.id = c.user_two_id )
ORDER BY c.created_at ASC

UPDATES

SELECT u.name, c.user_one_id, c.user_two_id, c.status, c.isRead, c.id FROM users u, challenges c 
WHERE u.id = ? AND c.status = 1 AND (c.user_one_id=? OR c.user_two_id=? )

? must be the id you are posting