John Davidson

Message:

I have a games database and I need to create pairs of similar games in another table. So, let's say, game ID 5 and ID 12 need to be paired, which should be like this:

---------------------------

| PairID | First | Second |

---------------------------

|   1    |   5   |   12   |

---------------------------

So what I need basically is to join pairs and games tables and select and display data for all IDs of each pair depending on which game is being browsed. That is, if it's game ID 5 then ID 12 should be displayed and vice versa. Seems pretty trivial at first, except I discovered there is no neareast elegant solution to this, where the major problem is the order of IDs in the pairs table.

First of all, with this scheme I'm forced to use two separate joins of the 'games' table and select both games' data regardless like this:

SELECT simpairs.id, simpairs.first, simpairs.second,

        games_one.id AS first_id, games_two.id AS sec_id,

        games_one.title AS first_title, games_two.title AS sec_title,

        games_one.year AS first_year, games_two.year AS sec_year

        FROM simpairs

        LEFT JOIN games AS games_one ON simpairs.first = games_one.id

        LEFT JOIN games AS games_two ON simpairs.second = games_two.id

        WHERE simpairs.first = <id> OR simpairs.second = <id>

Secondly, I need the script to pick out and display the correct data like this:

$id = $_GET['id'];

while($row = mysqli_fetch_assoc($res)) {

    if($row['first_id'] != $id) {

        $game_id = $row['first_id'];

        $title = $row['first_title'];

        $year = $row['first_year'];

    } else {

        $game_id = $row['sec_id'];

        $title = $row['sec_title'];

        $year = $row['sec_year'];

    }

...

}

Ok, so this is already pretty messy. Still, the biggest issue is sorting the output alphabetically. Obviously, it can't be easily done on the SQL side unless the query is somehow rebuilt for which there is no apparent route as it must include an equivalent for PHP condition, or the pairs table should be modified to contain a mirrored pair for each new entry like this:

---------------------------

| PairID | First | Second |

---------------------------

|   1    |   5   |   12   |

---------------------------

|   2    |  12   |   5    |

---------------------------

This would work of course and I would only need 1 join instead of 2, which would also make sorting easier directly on the SQL side, yet this also doesn't seem very elegant as I would have to perform two inserts for each new pair instead of just 1 plus clutter the database with mirrored entries making it exactly 2 times larger. I mean, if this is the right way then I guess I'm fine with that.

Sorting data on the script side is no less daunting. Typically, arrays are used for the occasion, except this time around I don't see how it can be achieved effectively. I will need to store ID, game title and year of release somehow and then sort everything based on titles alone. Meaning, I should be probably using 2 dimensional arrays where titles act as parent keys for sub-arrays containing IDs and years, then I should somehow sort only the parent keys without affecting the sub-arrays and I really don't know how to do that nor if it's worth it at all. All in all, seems like unnecessary strain to both the database and the script.

So what would be the optimal solution here?

---

My Answer:
One possible solution could be to modify the SQL query to include a CASE statement that checks if the first ID in the pair is equal to the ID being browsed. If it is, then display the second ID and its corresponding data, and vice versa. This way, you can eliminate the need for the PHP conditional statement to determine which ID to display.

Here's an example of how the modified SQL query could look like:

sql
SELECT simpairs.id, simpairs.first, simpairs.second,
CASE
WHEN simpairs.first = THEN games_two.id
ELSE games_one.id
END AS game_id,
CASE
WHEN simpairs.first = THEN games_two.title
ELSE games_one.title
END AS title,
CASE
WHEN simpairs.first = THEN games_two.year
ELSE games_one.year
END AS year
FROM simpairs
LEFT JOIN games AS games_one ON simpairs.first = games_one.id
LEFT JOIN games AS games_two ON simpairs.second = games_two.id
WHERE simpairs.first = OR simpairs.second = ;


This way, you only need one join and the data will be displayed correctly based on the ID being browsed. Sorting can also be done directly in the SQL query if needed.

Overall, this approach should simplify the code and make it more efficient.