[SOLVED] Is this bad coding?

[deadlyp99]

So, I remember once reading that nesting sql queries was bad, and I can see that it could be. But I'm not quite sure how I can make my code better, but it is already getting messy at the start of a long day of coding.

 

I am going to post my table structure, along with the rows being using in the particular query (for data example).

 

Now, I've been coding for a couple years, and I truly do want to improve in my efficiancy and code legibility. However I'm already starting to see this is going to turn into a big confusing mess if I don't get help making it better.

 

Thanks in advance for any replies, and have a good day

 

 $row `UsersGeneral`
ID 	Username 	PassHash 	   Email 	  LandLordID  	RaceType 	Active 	LastLogin 	LastIP 	ActivateID 	RegisterIP
17 	nocare 	    xxxxx 	[email protected] 	17 	      2 	      1 	     date 	xxx.xxx.xxx.xxx 	xxxxx 	xxx.xxx.xxx.xxx

$row2 `LandLord`
LandLordID	SquareID 	Capitol
17 	         9033 	    1

 

$row3 `MapSquares`
SquareID 	X 	Y 	LandLordID  	MapType 	OasisType 	CityID
  9033 	  39 	-7 	    17 	         1          	0 	  1

 

		$name = $_SESSION['name']; // <-- nocare
		$User = "SELECT * FROM `UsersGeneral` WHERE Username='$name' LIMIT 1";
		$UserQuery = mysql_query($User) or die(mysql_error());
		while ($row = mysql_fetch_array($UserQuery))
		{
			$LandLordID = $row['LandLordID'];
    				$UserCapitol = "SELECT * FROM `LandLord` WHERE LandLordID='$LandLordID' LIMIT 1";
    				$UserCapitolQuery = mysql_query($UserCapitol) or die(mysql_error());
    				while ($row2 = mysql_fetch_array($UserCapitolQuery))
    				{
				$CapitolSquare = "SELECT * FROM `MapSquares` WHERE LandLordID='$LandLordID' LIMIT 1";
				$CapitolSquareQuery = mysql_query($CapitolSquare) or die(mysql_error());
				while ($row3 = mysql_fetch_array($CapitolSquareQuery))
				{
					echo "Username: ".$row['Username']." ID: ".$row['ID']." LandLordID: ".$row['LandLordID']." SquareID: ".$row2['SquareID']." Capitol: ".$row2['Capitol']." X: ".$row3['X']." Y: ".$row3['Y']." MapType: ".$row3['MapType']." CityID:".$row3['CityID'];
				}
    				}
		}
		mysql_close($conn);

 

Output: Username: nocare ID: 17 LandLordID: 17 SquareID: 9033 Capitol: 1 X: 39 Y: -7 MapType: 1 CityID:1

[taquitosensei]

if landordid in the landlord table is the primary key you don't need the limit 1. Also if the landlord can only have 1 capitalsquare    you can do a unique key on the landlordid and capitalsquareid.    Then you can do a join

SELECT * UsersGeneral JOIN LandLord on UsersGeneral.LandLordID=LandLord.LandLordID JOIN  MapSquares on LandLord.LandLordID=CapitalSquares.LandLordID WHERE UsersGeneral.Username='$name'

 

[deadlyp99]

Thank you for replying taquitosensei, I just about quadrupled my code since I posted, and while it doesn't effect the mysql, its going to be a little while before I get a chance to implement this query to test it out.

 

So if I am undererstanding correctly, I'd go about using this like:

 

$sql = "SELECT * UsersGeneral JOIN LandLord on UsersGeneral.LandLordID=LandLord.LandLordID JOIN  MapSquares on LandLord.LandLordID=CapitalSquares.LandLordID WHERE UsersGeneral.Username='$name'";
$result = mysql_query($User) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{ 
      //Do stuff by Column name
}

[taquitosensei]

close. I forgot the FROM.  But that should be close.

[deadlyp99]

Wonderful wonderful

I got it working. Thanks to you I shortened my code a lot

Thanks a lot

 

From:

		$name = $_SESSION['name'];
		$User = "SELECT * FROM `UsersGeneral` WHERE Username='$name' LIMIT 1";
		$UserQuery = mysql_query($User) or die(mysql_error());
		while ($row = mysql_fetch_array($UserQuery))
		{
			$LandLordID = $row['LandLordID'];
    				$UserCapitol = "SELECT * FROM `LandLord` WHERE LandLordID='$LandLordID' LIMIT 1";
    				$UserCapitolQuery = mysql_query($UserCapitol) or die(mysql_error());
    				while ($row2 = mysql_fetch_array($UserCapitolQuery))
    				{
				$CapitolSquare = "SELECT * FROM `MapSquares` WHERE LandLordID='$LandLordID' LIMIT 1";
				$CapitolSquareQuery = mysql_query($CapitolSquare) or die(mysql_error());
				while ($row3 = mysql_fetch_array($CapitolSquareQuery))
				{
                                        }
                                 }
                           }

 

To:

 

$name = $_SESSION['name'];
		$sql = " SELECT * FROM UsersGeneral JOIN LandLord ON UsersGeneral.LandLordID = LandLord.LandLordID JOIN MapSquares ON LandLord.LandLordID = MapSquares.LandLordID WHERE UsersGeneral.Username = '$name'";
		$result = mysql_query($sql) or die(mysql_error());
		while ($row = mysql_fetch_array($result))
		{
                         }

[taquitosensei]

Sweet. Now you'll want to read up on LEFT and RIGHT Joins. It will save you headaches if you have to do much more in the way of joins.

[deadlyp99]

Yeah I was reading up about it, not quite getting it yet, but it'll click once I start finding ways to use them