CODEWITHSUNDEEP
phpmysql
C. Finke
C. Finke

Reputation: 129

PDO Select query with unknown columns?

Im not sure how to better describe my question in the topic...

I have a table that looks like this:

    $sql = "CREATE TABLE games (
            id INT(5) AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR (255),
            player_1_id INT (3),
            player_1_status BOOLEAN,
            player_2_id INT (3),
            player_2_status BOOLEAN,
            player_3_id INT (3),
            player_3_status BOOLEAN
    )"

I need a query like:

$stmt = $this->connection->prepare("
    SELECT id, name, turn
    FROM games
    WHERE player_1_id = :value1
    OR  player_2_id = :value1
    OR  player_3_id = :value1
");

But in addition, i want to select the column "player_x_status", depending on which "player_x_id" was found to equal :value1. Im really dont know how i could better describe my problem, lacking terms here.

Can someone advice ?

Upvotes: 1

Views: 87

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157839

Every time you create a field named something_N you can tell for sure that you are doing it wrong.

What you have to do is another table to store all the players with statuses, like

  • game_id
  • player_id
  • status

and you'll be able to select statuses for any players without problem.

This is how relational databases work.

Upvotes: 5

Related Questions