I've been messing around with this for three days now.. can you look at my code?
I'm not getting any error messages, but I'm not getting any data either.

I currently have a working form that successfully returns the results I want from one table in my DB. However, I now have to add more data to my results page and the data has to come from two different tables based on the same primary field name (all three tables have the same primary field - which is intDistrictID).

I thought it would be simple..

Here is my search code:

<form name="form1" method ="post" action="lodgelocator/results.php" target="_blank">
<p class="style1">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#000000">
<p>&nbsp;</p>

<p align="center">
<input type="hidden" name="metode" value="tblLodges.intDistrictID"/>
</select>
<p>&nbsp;</p>
<span class="style9">Enter District Number:</span> 
<input name="search" type="text" class="form" size="5">
</p>
<input type="submit" value="Click Here To View District Details" name="Go">
</p>
<p align="center">&nbsp;</p></td>
</tr>
</table>
</form>

Here is my php results code:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>

<table border="0" cellpadding="0" cellspacing="0" width="100%">
                              <tbody>

                                <tr>
                                  <td width="100%"><h1 align="center"><strong> District Details   </strong><br />
                                  </h1></td>
                                </tr>
                              </tbody>
</table>

                            <center>
                            </center>

                            <center>
                              <p>&nbsp;</p>
                              <p>&nbsp;</p>
                              <p><br />
                                <br />
                              </p>
                              <table border="1" cellpadding="4" cellspacing="0" width="86%">
                                <tbody>
                                  <tr>
                                    <td colspan="5" bgcolor="FFFF99" height="18"><center>
                                        <strong>List Of Distrct Lodges </strong>
                                    </center></td>
                                  </tr>
                                  <tr>
                                    <td width="10%" height="18" align="center" bgcolor="FFFF99"><center>
                                      Lodge ID Number
                                    </center></td>
                                    <td width="30%" height="18" align="center" bgcolor="FFFF99"><center>
                                      Lodge Name
                                    </center></td>
                                    <td width="24%" align="center" bgcolor="FFFF99"> Click  
                                    Button To View Lodge Details </td>
                                    <td width="26%" height="18" align="center" bgcolor="FFFF99"><center>
                                      City
                                    </center></td>
                                    <td width="10%" height="18" align="center" bgcolor="FFFF99"><center>
                                      District 
                                    Number
                                    </center></td>
                                  </tr>
                                  




<?php
$username = "username";
$password = "password";
$hostname = "localhost"; 

$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

$selected = mysql_select_db("db",$dbhandle)
or die("Could not select db");

$query =  "SELECT a.strLodgeName,
                  a.intLodgeNumber,
                  a.intDistrictID,
                  a.strLodgeMailingCity,
                  a.strLodgeMailingPostCode,
                  b.strDistrictName,
                  b.strDistrictWebSite,
                  c.strOfficerTitle,
                  c.strFirstName,
                  c.strLastName
                  FROM tblDistricts AS b
                  LEFT JOIN tblLodges AS a ON b.intDistrictID = a.intDistrictID
                  LEFT JOIN DistrictOfficers AS c ON b.intDistrictID = c.intDistrictID
                  WHERE c.intDistrictID='$id' GROUP BY a.strLodgeName LIMIT 50";
$result = mysql_query($query) or die(mysql_error());
while ($row = @mysql_fetch_array($result)){

{
echo "<tr bgcolor=\"#dddddd\"><td><center>";
echo $row["strOfficerTitle"];
echo "</center></td><td><center>";
echo $row["strDistrictName"];
echo "</center></td><td><center>";
echo $row["strDistrictWebSite"];
echo "</center></td><td><center>";
echo $row["strOfficerTitle"];
echo "</center></td><td><center>";
echo $row["strFirstName"];
echo "</center></td><td><center>";
echo $row["strLastName"];
}



{
echo "<tr bgcolor=\"#dddddd\"><td><center>";
echo $row["intLodgeNumber"];
echo "</center></td><td><center>";
echo $row["strLodgeName"];
echo "</center></td><td><center><span class=\"style2\">";
echo "<input name=\"submit\" type=\"button\" value=\"Lodge Details\" onclick=\"javascript:window.location='5view.php?id=";
echo $row["intLodgeNumber"];
echo "'\" /></center></td>";
echo "</center></td><td><center>";
echo $row["strLodgeMailingCity"];
echo "</center></td><td><center>";
echo ltrim($row["intDistrictID"], '0');
}?>
                                </tbody>
                              </table>
                              <br />
                            </center>


</body>
</html>

Have you tried switching your code

FROM:

FROM tblDistricts AS b
LEFT JOIN tblLodges AS a ON b.intDistrictID = a.intDistrictID

TO:

FROM tblLodges AS a
LEFT tblDistricts JOIN AS b ON a.intDistrictID = b.intDistrictID

?

What about using a sub query? Try it:

SELECT result.*, c.strOfficerTitle, c.strFirstName, c.strLastName

(SELECT a.strLodgeName, a.intLodgeNumber, a.intDistrictID, a.strLodgeMailingCity, a.strLodgeMailingPostCode, b.strDistrictName, b.strDistrictWebSite
FROM tblDistricts AS b
LEFT JOIN tblLodges AS a ON b.intDistrictID = a.intDistrictID) AS result

LEFT JOIN DistrictOfficers AS c ON result.intDistrictID = c.intDistrictID
WHERE c.intDistrictID='$id' GROUP BY result.strLodgeName LIMIT 50
This article has been dead for over six months. Start a new discussion instead.