How would I make this more efficient?

Question

<?php
include "../get/header.php";
$Username = SecurePost($_GET['Username']);
$gU = mysqli_fetch_object($getUser = mysqli_query($connection, "SELECT * FROM Users WHERE Username='".$Username."'"));
$UserExist = mysqli_num_rows($getUser = mysqli_query($connection, "SELECT * FROM Users WHERE Username='".$Username."'"));
$getUsers = mysqli_query($connection, "SELECT * FROM Users");
$numUsers = mysqli_num_rows($getUsers); 
$reqKills = mysqli_num_rows($allKills = mysqli_query($connection, "SELECT * FROM Kills WHERE Killer='".$gU->Username."'")); 
mysqli_query($connection, "UPDATE Users SET Kills='".$reqKills."' WHERE Username='".$gU->Username."'");
$pID = file_get_contents('http://rproxy.pw/rapi/GetIdByUsername/'.$gU->Username.'');
$Rank = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=GetGroupRole&playerid='.$pID.'&groupid=947351');

$getTemp = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='983872'");
$numTemp = mysqli_num_rows($getTemp); 

$Tact = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='976897'");
$numTac = mysqli_num_rows($Tact); 

$Sci = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='986292'");
$numSci = mysqli_num_rows($Sci); 

$MAC = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='1045203'");
$NumMAC = mysqli_num_rows($MAC); 

$Ops = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='1159559'");
$numOps = mysqli_num_rows($Ops); 

$Eng = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='990439'");
$numEng = mysqli_num_rows($Eng); 

$Diplo = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='992455'");
$numDip = mysqli_num_rows($Diplo); 

$Intel = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='974799'");
$numIntel = mysqli_num_rows($Intel); 

$realIA = mysqli_query($connection, "SELECT * FROM Branchs WHERE Username='".$Username."' AND GroupID='2640969'");
$numIA = mysqli_num_rows($realIA); 
#GroupAssign#
$Temporal = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=983872');
$Tactical = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=976897');
$Science = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=986292');
$MACO = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=1045203');
$Operations = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=1159559');
$Engineering = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=990439');
$IA = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=2640969');
$Diplomatic = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=992455');
$Intelligence = file_get_contents('http://www.roblox.com/Game/LuaWebService/HandleSocialRequest.ashx?method=IsInGroup&playerid='.$pID.'&groupid=974799');

if ($Temporal == true AND $numTemp == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','Temporal Investigations','Temporal','983872')");
}
if ($Tactical == true AND $numTac == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','Tactical Command','Tactical','976897')");
}
if ($Science == true AND $numSci == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','Applied Sciences','Science','986292')");
}
if ($MACO == true AND $NumMAC == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','MACO','MACO','1045203')");
}
if ($Operations == true AND $numOps == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','Operations','Operations','1159559')");
}
if ($Engineering == true AND $numEng == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','Engineering','Engineering','990439')");
}
if ($IA == true AND $numIA == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','Internal Affairs','IA','2640969')");
}
if ($Diplomatic == true AND $numDip == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','Diplomatic Corps','Diplomatic','992455')");
}
if ($Intelligence == true AND $numIntel == "0") {
mysqli_query($connection, "INSERT INTO Branchs (Username, Name, Image, GroupID) VALUES('".$gU->Username."','Starfleet Intelligence','Intelligence','974799')");
}
?>

As you can see its very inefficient, and only works for the first Temporal query for some reason. Is there any way I can simplify this to be more efficient and make the values work for each query? I haven't received any errors.

one query ..`AND GROUP ID IN (983872,976897 ...)...` – Sep 25 '15 at 01:07 — , Sep 25 '15 at 01:07

score 0 · Answer 1 · edited May 23 '17 at 12:06

This query will insert a row into Branchs for each group that doesn't exist for $Username.

INSERT INTO Branchs (Username, Name, Image, GroupID)
SELECT '{$gU->Username}', groups.Name, groups.Image, groups.GroupID
FROM (
    SELECT '983872' AS GroupID, 'Temporal Investigations' AS Name, 'Temporal' AS Image
    UNION
    SELECT '976892', 'Tactical Command', 'Tactical'
    UNION
    SELECT '986292', 'Applied Sciences', 'Science'
    ...
    ) AS groups
LEFT JOIN Branchs ON Branches.GroupID = groups.GroupID AND Username = '$Username'
WHERE Branchs.GroupID IS NULL

See Return row only if value doesn't exist for how the LEFT JOIN works to return rows in the UNION subquery that don't have matches in Branchs.

How would I make this more efficient?

1 Answers1