Problems joining two tables properly

Question

I have these two tables above. The first table contains different drugs. The second table contains verified combinations of any two drugs from the first table.

By default there is a reference drug where drugID = $_GET['ref'] in the URL. Lets just say default.php?ref=3 for vaccine.

I want to list all drugs from the drugs table, except where drugID = $_GET['ref'] = 3. This will show all drugs except vaccine. This is done like this:

    $ref = htmlspecialchars($_GET['ref']);

$sql = "SELECT * FROM drugs WHERE drugID != {$ref} ORDER BY drug ASC";
$result = $conn->query($sql);

    if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

    echo $row["drug"];

    }
}

For every drug in the loop (drugID) I want to check whether there exist a comboID where drugID and $_GET['ref'] are both found in either drug1_ID or drug2_ID. If such a comboID exist, echo "combination found:" . row['comboID'] . row["interaction"]. This should return 4 combinations with vaccine.

I have tried left, right, inner and full join of the tables, but nothing seems to work. How can this be done?

In the following example, I tried to left join:

SELECT *
FROM drugs
LEFT JOIN combination
ON (drugs.ID = combination.drug1_ID AND {$ref} = combination.drug2_ID) OR ({$ref} = combination.drug1_ID AND drugs.ID = combination.drug2_ID);

This just returns every drug, but more than once if combination is found.

Answer 1

Assumptions

• You want to SELECT all drugs from the drugs table except for the reference drug
• You want to join the combination table so as to show combinations of drugs with the reference drug.

Output should look something like:

drugs.drugID | drugs.drug      | combination.comboID | combination.interaction
-------------+-----------------+---------------------+-------------------------
1            | Paracetamol     | 1                   | ok
2            | Benzodiazepine  | 2                   | ok
4            | Calcium blocker | 3                   | ok
5            | Sodium blocker  | 4                   | ok
6            | Cold meds       | NULL                | NULL
7            | Pain meds       | NULL                | NULL
8            | Grape juice     | NULL                | NULL
9            | Cannabis        | NULL                | NULL
10           | Salt            | NULL                | NULL

SQL Query

SELECT drugs.drugID, drugs.drug, combination.comboID, combination.interaction
FROM drugs
    LEFT JOIN combination                                                -- Left join so we select all rows from FIRST table
        ON drugs.drugID IN (combination.drug1_ID, combination.drug2_ID)  -- Only join where drug from first table is one of the drugs in the combination
            AND ? IN (combination.drug1_ID, combination.drug2_ID)        -- Only join where the reference drug is also in the combination
WHERE drugs.drugID != ?                                                  -- Exclude reference drug from first table

PHP - mysqli

$reference_drug = $_GET["ref"] ?? NULL;

if($reference_drug){
    $sql = "
        SELECT drugs.drugID, drugs.drug, combination.comboID, combination.interaction
        FROM drugs
        LEFT JOIN combination 
            ON drugs.drugID IN (combination.drug1_ID, combination.drug2_ID)
                AND ? IN (combination.drug1_ID, combination.drug2_ID)
        WHERE drugs.drugID != ?
    ";
    $query = $conn->prepare($sql);
    $query->bind_param("ii", $reference_drug, $reference_drug);
    $query->execute();
    $query->store_result();
    $query->bind_result($drug_id, $drug_name, $combination_id, $combination_status);
    while($query->fetch()){
        echo "$drug_id, $drug_name, $combination_id, $combination_status\n";
    }

}
else{
    echo "Error: No reference drug supplied.";
}

PHP - PDO

Don't forget that any user input should be treated as untrusted_ and therefore shouldn't be put directly into a query.

$db_host = "127.0.0.1";
$db_user = "some_user";
$db_pass = "db_password";
$db_name = "db_name";

$this->pdo = new pdo(
    "mysql:host={$db_host};dbname={$db_name}",
    $db_user,
    $db_pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => FALSE
    ]
);

$reference_drug = $_GET["ref"] ?? NULL;

if($reference_drug){
    $sql = "
        SELECT drugs.drugID, drugs.drug, combination.comboID, combination.interaction
        FROM drugs
        LEFT JOIN combination 
            ON drugs.drugID IN (combination.drug1_ID, combination.drug2_ID)
                AND ? IN (combination.drug1_ID, combination.drug2_ID)
        WHERE drugs.drugID != ?
    ";
    $query = $pdo->prepare($sql);
    $query->execute([$reference_drug, $reference_drug]);
    while($row = $query->fetch(PDO::FETCH_ASSOC)){
        echo $row["drugID"];
    }

}
else{
    echo "Error: No reference drug supplied.";
}