使用其他表php的列表从一个表中获取数据

Question

I have following tables

myListTable

playerId listId type
 50       10     0
 51       10     0

players

id  x  xx etc
50  x  xx etc
51  x  xx etc

I want to run a query where i provide listId and type and it will get the lists of all players related to that listId and type

try {
    $conn = $this->GetDBConnection();
    $type = 0; // 0 REQUEST BY PLAYERS
    $statement = $conn->prepare('SELECT p.* FROM myListTable c, players p WHERE (c.listId = :listId ) AND (c.type = :type) AND ( p.id = c.playerId) ');
    $statement->bindParam(':listId', $listId, PDO::PARAM_INT);
    $statement->bindParam(':type', $type, PDO::PARAM_INT);
    $statement->execute();
    if(!($row = $statement->fetchAll(PDO::FETCH_ASSOC))) {
        return false;
    }
    $conn = null;
} catch(PDOException $e) {
    throw $e;
}

This is just returning me false. What do i have to do to fix this query?

Answer 1

One of the mistake is a typo in your prepare query:

$statement = $conn->prepare('SELECT p.* 
    FROM myListTable c, players p 
    WHERE (c.listId = :c.listId ) 
        AND (c.type = :type) 
        AND ( p.id = c.playerId) ');

The :c.listId should just be :listId.

The second one is, type is a reserved word in MySQL. You need backticks to escape it inside your query.

Another is, since you're only selecting values from the players table; a JOIN is preferred.

The query should be:

SELECT p.*
FROM players p
JOIN myListTable c
    ON (p.id = c.playerId)
WHERE (c.listId = :c.listId)
    AND (c.`type` = :type)