CODEWITHSUNDEEP
phpmysqlsqlmysqli
Sophie Mackeral
Sophie Mackeral

Reputation: 917

Inner/Left join with two different where clauses

i'm in the process of joining two tables together under two different conditions. For primary example, lets say I have the following nested query:

$Query = $DB->prepare("SELECT ID, Name FROM modifications
WHERE TYPE =1 & WFAbility = '0'");
$Query->execute();
$Query->bind_result($Mod_ID,$Mod_Name);

and this query: 

$Query= $DB->prepare("SELECT `ModID` from `wfabilities` WHERE `WFID`=?");
$Query->bind_param();
$Query->execute();
$Query->bind_result();
while ($Query->fetch()){  }

Basically, I want to select all the elements where type is equal to one and Ability is equal to 0, this is to be selected from the modifications table.

I further need to select all the IDs from wfabilities, but transform them into the names located in modifications where WFID is equal to the results from another query.

Here is my current semi-working code.

$Get_ID = $DB->prepare("SELECT ID FROM warframes WHERE Name=?");
        $Get_ID->bind_param('s',$_GET['Frame']);
        $Get_ID->execute();
        $Get_ID->bind_result($FrameID);
        $Get_ID->fetch();
        $Get_ID->close();
            echo $FrameID;
            $WF_Abilties = $DB->prepare("SELECT ModID FROM `wfabilities` WHERE WFID=?");
            $WF_Abilties->bind_param('i',$FrameID);
            $WF_Abilties->execute();
            $WF_Abilties->bind_result($ModID);
                $Mod_IDArr = array();
            while ($WF_Abilties->fetch()){
                $Mod_IDArr[] = $ModID;
            }
            print_r($Mod_IDArr);
                $Ability_Name = array();
                foreach ($Mod_IDArr AS $AbilityMods){
                    $WF_AbName = $DB->prepare("SELECT `Name` FROM `modifications` WHERE ID=?");
                    $WF_AbName->bind_param('i',$AbilityMods);
                    $WF_AbName->execute();
                    $WF_AbName->bind_result($Mod_Name);
                    $WF_AbName->fetch();
                        $Ability_Name[] = $Mod_Name;
                }
            print_r($Ability_Name);

Upvotes: 1

Views: 314

Answers (3)

Dimitris Bouzikas
Dimitris Bouzikas

Reputation: 4530

This is the query:

"SELECT A.ID, A.Name,B.ModID,C.Name
FROM modifications as A 
LEFT JOIN wfabilities as B ON A.ID = B.WFID 
LEFT JOIN warframes as C ON C.ID = B.WFID 
WHERE A.TYPE =1 AND A.WFAbility = '0' AND C.Name = ?"

Upvotes: 0

Damien Black
Damien Black

Reputation: 5647

To do this, you need to join your tables, I'm not quite sure what you are trying to do so you might have to give me more info, but here is my guess.

SELECT ID, Name, ModID 
FROM modifications
JOIN wfabilities 
  ON WFID = ID
WHERE TYPE = '1' 
  AND WFAbility = '0'

In this version I am connecting the tables when WFID is equal if ID. You will have to tell me exactly what is supposed to be hooking to what in your requirements.

To learn more about joins and what they do, check this page out: MySQL Join

Edit:

After looking at your larger structure, I can see that you can do this:

SELECT modifications.Name FROM modifications
JOIN wfabilities on wfabilities.ModID = modifications.ID
JOIN warframes on warframes.ID = wfabilities.WFID
WHERE warframes.Name = 'the name you want'

This query will get you an array of the ability_names from the warframes name.

Upvotes: 1

Adarsh Shah
Adarsh Shah

Reputation: 6775

See below:

SELECT ModID, 
     ID,
     Name
FROM modifications M    
LEFT JOIN wfabilities WF
    ON WF.ModID = M.ID
WHERE TYPE =1 & WFAbility = '0'

Upvotes: 1

Related Questions