Failing Miserably With Creating My LEFT JOIN..

JTapp · April 15, 2008

I don't know.. maybe it's not even a LEFT JOIN that I need to do... Can somebody take a look ?

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 another chart to my results page and its data has to come from two different DB tables based on the same primary field name (all three tables have the same primary field - which is intDistrictID). I keep getting crazy error messages from MySql telling me to check and see if my version accepts the code.

I thought it would be simple..

tblDistricts - has two fields I need added to my results page called "strDistrictName" and "strDistrictWebsite"

DistrictOfficers - has three fields I need returned on the same page called "strOfficerTitle", "strFirstName" and "strLastName"

Below is my cleaned up (at least I tried) form code:

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

<p align="center">
<input type="hidden" name="metode" value="tblLodges.intDistrictID"/>
</select>
<p> </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"> </p></td>
</tr>
</table>
</form>

Below is my cleaned up results.php code (you will probably disagree about it being clean):

$query = mysql_query("SELECT strLodgeName, intLodgeNumber, intDistrictID, strLodgeMailingCity, strLodgeMailingPostCode FROM tblLodges WHERE TRIM(LEADING '0' FROM $metode) = '$search' GROUP BY strLodgeName LIMIT 50");
while ($row = @mysql_fetch_array($query))

{
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');
}?>

//query details table begins

$query = mysql_query("SELECT tblDistricts.intDistrictID, tblDistricts.strDistrictWebsite, tblDistricts.strDistrictName, DistrictOfficers.intDistrictID, DistrictOfficers.strOfficerTitle, DistrictOfficers.strFirstName, DistrictOfficers.strLastName, DistrictOfficers.OfficePhone, DistrictOfficers.Email FROM tblDistricts LEFT JOIN DistrictOfficers ON tbleDistricts.intDistrictID = DistrictOfficers.intDistrictID WHERE tblDistricts.strDistrictName=$id")or die(mysql_error());

echo "<center>\n";

echo "<H2>Roster of District Officers</H2>\n";

echo "<table border='1'>

<tr>

<th>District Number</th>

<th>Officer Title</th>

<th>Officer First</th>

<th>Officer Last</th>

<th>Officer Email</th>

<th>Officer Phone</th>

</tr>";

if (mysql_num_rows($query)) {

while ($row = mysql_fetch_array($query)) {

$variable1=$row["intDistrictID"];

$variable2=$row["strOfficerTitle"];

$variable3=$row["strFirstName"];

$variable4=$row["strLastName"];

$variable5=$row["Email"];

$variable6=$row["OfficePhone"];

//table layout for results

print("<tr>");

echo "<tr align=\"center\" bgcolor=\"#EFEFEF\">\n";

echo "<td class=\"td_id\">$variable1</td>\n";

echo "<td class=\"td_id\">$variable2</td>\n";

echo "<td class=\"td_id\">$variable3</td>\n";

echo "<td class=\"td_id\">$variable4</td>\n";

echo "<td class=\"td_id\">$variable5</td>\n";

echo "<td class=\"td_id\">$variable6</td>\n";

print("</tr>");

}

?> <p> </p>

</center>

<hr>

<br />

<tbody>

<tr>

<strong>District Lodges </strong>

</center></td>

</tr>

<tr>

<td width="18%" height="18" align="center" bgcolor="FFFF99"> <div align="center">Lodge ID Number

</div></td>

<td width="16%" height="18" align="center" bgcolor="FFFF99"> <div align="center">Lodge Name

</div></td>

<td width="26%" align="center" bgcolor="FFFF99"> <div align="center">Click

Button To View Lodge Details </div></td>

<td width="21%" height="18" align="center" bgcolor="FFFF99"> <div align="center">City

</div></td>

<td width="19%" height="18" align="center" bgcolor="FFFF99"> <div align="center">District

Number

</div></td>

</tr>

<?php

$query = mysql_query("SELECT strLodgeName, intLodgeNumber, intDistrictID, strLodgeMailingCity, strLodgeMailingPostCode FROM tblLodges WHERE TRIM(LEADING '0' FROM $metode) = '$search' GROUP BY strLodgeName LIMIT 50");

while ($row = @mysql_fetch_array($query))

{

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');

}?>

craygo · April 15, 2008

make sure you have your query's return error other wise you will never get to the bottom of it.

Also what is the exact error code you are getting. "Crazy error message" doesn't help us much

try separating your query's so you can troubleshoot.

$query = "SELECT strLodgeName, intLodgeNumber, intDistrictID, strLodgeMailingCity, strLodgeMailingPostCode FROM tblLodges WHERE TRIM(LEADING '0' FROM $metode) = '$search' GROUP BY strLodgeName LIMIT 50";
$result = mysql_query($query) or die(mysql_error());
while ($row = @mysql_fetch_array($query)){

now if you need to you can echo $query to see what is being passed to mysql.

Ray

JTapp · April 15, 2008

Well, now I'm not getting any error message at all.. I'm just getting a blank page.

You asked for it... here is my (beginner's) attempt at my LEFT JOIN -

<?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 = mysql_query("SELECT a.strLodgeName, a.intLodgeNumber, a.intDistrictID, a.strLodgeMailingCity, a.strLodgeMailingPostCode, b.strOfficerTitle, b.strDistrictName, b.strDistrictWebSite, c.strOfficerTitle, c.strFirstName, c.strLastName FROM tblLodges intDistrictID LEFT JOIN tblDistricts intDistrictID ON tblDistricts.intDistrictID = DistrictOfficers.lngLodgeID WHERE DistrictOfficers.intDistrictID=$id GROUP BY a.strLodgeName LIMIT 50");
$result = mysql_query($query) or die(mysql_error());
while ($row = @mysql_fetch_array($query)){

{
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');
}?>

craygo · April 15, 2008

problem is your querying it twice. remove the first mysql_query

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

Ray

JTapp · April 15, 2008

I removed the dual query, but I'm still getting a blank page with no error messages...

craygo · April 15, 2008

try this instead. Hope I got the tables right

$query =  "SELECT a.strLodgeName,
                  a.intLodgeNumber,
                  a.intDistrictID,
                  a.strLodgeMailingCity,
                  a.strLodgeMailingPostCode,
                  b.strOfficerTitle,
                  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.intLodgeNumber = 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($query)){

Ray

JTapp · April 15, 2008

I think you got the table names right - it should be:

a = tblLodges

b = tblDistricts

c = DistrictOfficers

I plugged it in and I'm getting the same result as before - no error messages but no data either..

I also just triple checked the spelling on my table names and fields....

craygo · April 15, 2008

Is it correct how the tables are linked?

can you post your table structure and how the tables are linked.

Ray

JTapp · April 15, 2008

Here is my code for the search page:

<form name="form1" method ="post" action="http://www.glflamason.org/NEWSITE/NEW2/lodgelocator/6results.php" target="_blank">
<p class="style1">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#000000">
<p> </p>

<p align="center">
<input type="hidden" name="metode" value="tblLodges.intDistrictID"/>
</select>
<p> </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"> </p></td>
</tr>
</table>
</form>

Here is my code for the results page (keep in mind that 1.) I'm a beginner at all of this and 2.) it has be gutted in the process of trying to get some results returned... thanks! :

<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> </p>
                              <p> </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.strOfficerTitle,
                  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.intLodgeNumber = 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($query)){


{
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>

craygo · April 15, 2008

I don't want to know your code I want to know how your database is structured. If you have phpmyadmin export your 3 tables and post it here.

JTapp · April 15, 2008

Here you are.. hopefully this is what you are looking for..

[attachment deleted by admin]

craygo · April 15, 2008

Looking at your structure my query is wrong

$query =  "SELECT a.strLodgeName,
                  a.intLodgeNumber,
                  a.intDistrictID,
                  a.strLodgeMailingCity,
                  a.strLodgeMailingPostCode,
                  b.strOfficerTitle,
                  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($query)){

Ray

JTapp · April 15, 2008

Ray, I'm sorry to report that didn't change anything. :-[

craygo · April 15, 2008

something I did notice is that strOfficerTitle doesn't exist in the districts table Also ,damn it all, you have to change this line

while ($row = @mysql_fetch_array($result)){

$result is the query not $query

$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)){

Ray

JTapp · April 15, 2008

Crap. I had hoped those two things would do it. But, I'm still not getting anything........

craygo · April 15, 2008

do you have phpmyadmin?? If you do can you do an export of your tables so I can recreate it here. Everything looks fine so I am not sure unless I can play around with the query here.

Ray

JTapp · April 15, 2008

Hang on.. having difficulty posting the file to phpFreaks..

I can't figure out what type of file to export to from phpMyAdmin..

phpFreaks won't take a MySQL or a CSV attachment....

JTapp · April 15, 2008

Did you get my last post? PhpFreaks is giving me error messages like "you have already submitted this post" when I haven't

craygo · April 15, 2008

save it as a sql file then rename it to a txt file. i will rename it here.

JTapp · April 15, 2008

trying to post attachment... it just won't let me...

I keep getting:

An Error Has Occurred!

You already submitted this post! You might have accidently double clicked, or tried to refresh.

JTapp · April 15, 2008

test

[attachment deleted by admin]

Sign In

Failing Miserably With Creating My LEFT JOIN..

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information