As far as I can see, both are coming from the same query. SELECT a.strLodgeName, a.intLodgeNumber, a.strDistrictName, a.strLodgeWEB, a.strLodgeCounty, a.dtChartered, a.strLodgeMailingAddress, a.strLodgeMailingAddress2, a.strLodgeMailingCity, a.strLodgeMailingStateCode, a.strLodgeMailingPostCode, a.strLodgeEmail, a.strLodgePhone, a.strLodgeFax, a.strDrivingDirectons, a.dtMeetingTime, a.dtMealTime, a.strFloorSchool, a.strLodgeNews, b.strOfficerTitle, b.strFirstName, b.strLastName, b.BusinessPhone, b.PersEmail FROM tblLodges a LEFT JOIN tblOfficers b ON a.lngLodgeID = b.lngLodgeID WHERE a.intLodgeNumber=$id GROUP BY a.strLodgeName LIMIT 50. Check if your query is returning the rows you want to use. As far as I know, when you are using the resultset in the first while, the pointer will be pointing to the last record. So, I dont think in the second while loop, the recordset will remain the same.
Well, execute the query again before the second while. ie., $query=mysql_query("your query");
and use the while loop.
Need another set of eyes to my gutted code
First of all, please forgive my immature code.. I am a beginner and this is my first one.
Could some body take a look at this and tell me why the data in my table is not populating?
Thanks in advance for your time.
The problem I am having is all variables are populating, with the exception of variables 20-24. Variables 1-19 are coming from a table called tblLodges and variables 20-24 are coming from a table called tblOfficers
$id = $_GET['id'];
$query = mysql_query("SELECT a.strLodgeName, a.intLodgeNumber, a.strDistrictName, a.strLodgeWEB, a.strLodgeCounty, a.dtChartered, a.strLodgeMailingAddress, a.strLodgeMailingAddress2, a.strLodgeMailingCity, a.strLodgeMailingStateCode, a.strLodgeMailingPostCode, a.strLodgeEmail, a.strLodgePhone, a.strLodgeFax, a.strDrivingDirectons, a.dtMeetingTime, a.dtMealTime, a.strFloorSchool, a.strLodgeNews, b.strOfficerTitle, b.strFirstName, b.strLastName, b.BusinessPhone, b.PersEmail FROM tblLodges a LEFT JOIN tblOfficers b ON a.lngLodgeID = b.lngLodgeID WHERE a.intLodgeNumber=$id GROUP BY a.strLodgeName LIMIT 50")or die(mysql_error());
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["strLodgeName"];
$variable2=$row["intLodgeNumber"];
$variable3=$row["strDistrictName"];
$variable4=$row["strLodgeWEB"];
$variable5=$row["strLodgeCounty"];
$variable6=$row["dtChartered"];
$variable7=$row["strLodgeMailingAddress"];
$variable8=$row["strLodgeMailingAddress2"];
$variable9=$row["strLodgeMailingCity"];
$variable10=$row["strLodgeMailingStateCode"];
$variable11=$row["strLodgeMailingPostCode"];
$variable12=$row["strLodgeEmail"];
$variable13=$row["strLodgePhone"];
$variable14=$row["strLodgeFax"];
$variable15=$row["strDrivingDirectons"];
$variable16=$row["dtMeetingTime"];
$variable17=$row["dtMealTime"];
$variable18=$row["strFloorSchool"];
$variable19=$row["strLodgeNews"];
$variable20=$row["strOfficerTitle"];
$variable21=$row["strFirstName"];
$variable22=$row["strLastName"];
$variable23=$row["BusinessPhone"];
$variable24=$row["PersEmail"];
//layout for Lodge Information results
echo ("<tr>");
echo "<center>\n";
echo "<p>GRAND LODGE OF LOUISIANA - LODGE LOCATOR RESULTS\n</p>";
echo "Lodge Name:$variable1</p>";
echo "<p><b>Lodge Number:</b> $variable2</p>";
echo "<p><b>District Name:</b> $variable3</p>";
echo "<a href=\"$variable4\">Click Here To Go To The Lodge Website</a>";
echo "<p><b>Lodge County:</b> $variable5</p>";
echo "<p><b>Lodge Chartered On:</b> $variable6</p>";
echo "<p><b>Lodge Address:</b> $variable7, $variable8</p>";
echo '<p>' . $variable9 . $variable10 . $variable11 . '</p>';
echo "Click Here To Email The Lodge";
echo "<p>Lodge Phone Number: $variable13, Lodge FAX Number: $variable14</p>";
echo "<p>Lodge Driving Directions: $variable15</p>";
echo "<p>Lodge Lodge Meeting Time: $variable16</p>";
echo "<p>Lodge Lodge Meal Time: $variable17</p>";
echo "<p>Lodge Floor School: $variable18</p>";
echo "<p>Lodge News: $variable19</p>";
echo "<img src='{$row['link']}'>";
echo "</center>\n";
echo ("</tr>");
}
//Layout for Officers Table Results
echo "<center>\n";
echo "<H2>Roster of Lodge Officers</H2>\n";
echo "<table border='1'>
<tr>
<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)){
$variable20=$row["strOfficerTitle"];
$variable21=$row["strFirstName"];
$variable22=$row["strLastName"];
$variable23=$row["PersEmail"];
$variable24=$row["BusinessPhone"];
//table layout for results
print ("<tr>");
echo "<tr align=\"center\" bgcolor=\"#EFEFEF\">\n";
echo "<td class=\"td_id\">$variable20</td>\n";
echo "<td class=\"td_id\">$variable21</td>\n";
echo "<td class=\"td_id\">$variable22</td>\n";
echo "<td class=\"td_id\">$variable23</td>\n";
echo "<td class=\"td_id\">$variable24</td>\n";
print ("</tr>");
}
}
?>The difference in the variables is 1-19 are coming from a table called tblLodges and they are working fine, but variables 20-24 are coming from a table called tblOfficers and nothing is working....
Wow - I'm sorry.. I'm not understanding your suggestion (newbie). I can tell you yes, both are coming from the same query... and the query is returning the rows I want, but it is missing some of the data. Starting at line 71 the code is supposed to pull data from a different table (tblOfficers).
I'm wondering if I just need an additional query that finds the unique ID number from the first query. If so, I'm screwed because I have no idea how to do that.
Okay.. I ll make it simple for you.. Here is an example of what you are doing.
<?php
mysql_connect("localhost",'root');
mysql_select_db("test");
$q="select * from table1";
$res=mysql_query($q); //result set of the query
while($row=mysql_fetch_array($res)){ //loop and get all the records for the above query
echo "first loop".$row[0]."";
} //by the end of this query, the internal pointer will be pointing towards the last record.
while($row=mysql_fetch_array($res)){ //loop for the same resultset.
echo "second loop".$row[1]."";
} //prints nothing since the pointer is already pointing at the last record in the previous loop.
?>
To make that work, you need to execute your query again. Like this.
<?php
mysql_connect("localhost",'root');
mysql_select_db("test");
$q="select * from table1";
$res=mysql_query($q);
while($row=mysql_fetch_array($res)){
echo "first loop".$row[0]."";
}
$q="select * from table1";
$res=mysql_query($q);
while($row=mysql_fetch_array($res)){
echo "second loop".$row[1]."";
}
?>Notice the difference. Well, Try this code.
<?php
$id = $_GET['id'];
$query = mysql_query("SELECT a.strLodgeName, a.intLodgeNumber, a.strDistrictName, a.strLodgeWEB, a.strLodgeCounty, a.dtChartered, a.strLodgeMailingAddress, a.strLodgeMailingAddress2, a.strLodgeMailingCity, a.strLodgeMailingStateCode, a.strLodgeMailingPostCode, a.strLodgeEmail, a.strLodgePhone, a.strLodgeFax, a.strDrivingDirectons, a.dtMeetingTime, a.dtMealTime, a.strFloorSchool, a.strLodgeNews, b.strOfficerTitle, b.strFirstName, b.strLastName, b.BusinessPhone, b.PersEmail FROM tblLodges a LEFT JOIN tblOfficers b ON a.lngLodgeID = b.lngLodgeID WHERE a.intLodgeNumber=$id GROUP BY a.strLodgeName LIMIT 50")or die(mysql_error());
while ($row = @mysql_fetch_array($query)) {
$variable1=$row["strLodgeName"];
$variable2=$row["intLodgeNumber"];
$variable3=$row["strDistrictName"];
$variable4=$row["strLodgeWEB"];
$variable5=$row["strLodgeCounty"];
$variable6=$row["dtChartered"];
$variable7=$row["strLodgeMailingAddress"];
$variable8=$row["strLodgeMailingAddress2"];
$variable9=$row["strLodgeMailingCity"];
$variable10=$row["strLodgeMailingStateCode"];
$variable11=$row["strLodgeMailingPostCode"];
$variable12=$row["strLodgeEmail"];
$variable13=$row["strLodgePhone"];
$variable14=$row["strLodgeFax"];
$variable15=$row["strDrivingDirectons"];
$variable16=$row["dtMeetingTime"];
$variable17=$row["dtMealTime"];
$variable18=$row["strFloorSchool"];
$variable19=$row["strLodgeNews"];
$variable20=$row["strOfficerTitle"];
$variable21=$row["strFirstName"];
$variable22=$row["strLastName"];
$variable23=$row["BusinessPhone"];
$variable24=$row["PersEmail"];
//layout for Lodge Information results
echo ("<tr>");
echo "<center>\n";
echo "<p>GRAND LODGE OF LOUISIANA - LODGE LOCATOR RESULTS\n</p>";
echo "Lodge Name:$variable1</p>";
echo "<p><b>Lodge Number:</b> $variable2</p>";
echo "<p><b>District Name:</b> $variable3</p>";
echo "<a href=\"$variable4\">Click Here To Go To The Lodge Website</a>";
echo "<p><b>Lodge County:</b> $variable5</p>";
echo "<p><b>Lodge Chartered On:</b> $variable6</p>";
echo "<p><b>Lodge Address:</b> $variable7, $variable8</p>";
echo '<p>' . $variable9 . $variable10 . $variable11 . '</p>';
echo "Click Here To Email The Lodge";
echo "<p>Lodge Phone Number: $variable13, Lodge FAX Number: $variable14</p>";
echo "<p>Lodge Driving Directions: $variable15</p>";
echo "<p>Lodge Lodge Meeting Time: $variable16</p>";
echo "<p>Lodge Lodge Meal Time: $variable17</p>";
echo "<p>Lodge Floor School: $variable18</p>";
echo "<p>Lodge News: $variable19</p>";
echo "<img src='{$row['link']}'>";
echo "</center>\n";
echo ("</tr>");
}
//Layout for Officers Table Results
echo "<center>\n";
echo "<H2>Roster of Lodge Officers</H2>\n";
echo "<table border='1'>
<tr>
<th>Officer Title</th>
<th>Officer First</th>
<th>Officer Last</th>
<th>Officer Email</th>
<th>Officer Phone</th>
</tr>";
$query = mysql_query("SELECT a.strLodgeName, a.intLodgeNumber, a.strDistrictName, a.strLodgeWEB, a.strLodgeCounty, a.dtChartered, a.strLodgeMailingAddress, a.strLodgeMailingAddress2, a.strLodgeMailingCity, a.strLodgeMailingStateCode, a.strLodgeMailingPostCode, a.strLodgeEmail, a.strLodgePhone, a.strLodgeFax, a.strDrivingDirectons, a.dtMeetingTime, a.dtMealTime, a.strFloorSchool, a.strLodgeNews, b.strOfficerTitle, b.strFirstName, b.strLastName, b.BusinessPhone, b.PersEmail FROM tblLodges a LEFT JOIN tblOfficers b ON a.lngLodgeID = b.lngLodgeID WHERE a.intLodgeNumber=$id GROUP BY a.strLodgeName LIMIT 50")or die(mysql_error());
if (mysql_num_rows($query)) {
while ($row = mysql_fetch_array($query)){
$variable20=$row["strOfficerTitle"];
$variable21=$row["strFirstName"];
$variable22=$row["strLastName"];
$variable23=$row["PersEmail"];
$variable24=$row["BusinessPhone"];
//table layout for results
print ("<tr>");
echo "<tr align=\"center\" bgcolor=\"#EFEFEF\">\n";
echo "<td class=\"td_id\">$variable20</td>\n";
echo "<td class=\"td_id\">$variable21</td>\n";
echo "<td class=\"td_id\">$variable22</td>\n";
echo "<td class=\"td_id\">$variable23</td>\n";
echo "<td class=\"td_id\">$variable24</td>\n";
print ("</tr>");
}
}
?>Cheers,
Naveen
Naveen,
Thank you very much. That seems to be quite close to the resolution. I'm getting some of the data in my table.
However, I am getting one row of data and for the test run that I did I should have two... is there a limit or something that is going on here?
Maybe your query is wrong ? Did you execute it in phpmyadmin/mysql ? Did you get the output which you desired ? I don't know what your query does. Umm.. Execute it first and change it ! limit 50 will show only 50 records! You are grouping the records by name. Maybe thats why you are getting only 1 record ?
I took out the WHERE and GROUP BY clause and was able to get a return of multiple rows. Problem now is they are not unique to the field "lngLodgeID"- Here is what I now have:
$query = mysql_query("SELECT tblLodges.strLodgeName, tblLodges.intLodgeNumber, tblLodges.strDistrictName, tblLodges.strLodgeMailingCity, tblLodges.strLodgeMailingPostCode, tblLodges.strLodgeCounty, tblOfficers.strOfficerTitle, tblOfficers.strFirstName, tblOfficers.strLastName, tblOfficers.BusinessPhone, tblOfficers.PersEmail FROM tblLodges LEFT JOIN tblOfficers ON tblLodges.lngLodgeID = tblOfficers.lngLodgeID LIMIT 0, 50")or die(mysql_error());
I don't know if you care, but here is the history on how I got to where I am..
no and yes.
For my test run, there should be two results in the table. They both have the 'lngLodgeID' value of '3236'. They are indeed returned in the table. However, an additional result is being returned with a 'lngLodgeID' of '2117' - I don't know where its coming from/how its getting there.. its driving me crazzy!
To see what I'm talking about, go to:
http://www.la-mason.com/3search.html
Search by 'name' and put in "Abbeville".
On the next page, click the "View" button.
Now look at the table at the bottom of the page.. see how it has three rows of data?
Tellme what it prints if you execute only this part of your query.
SELECT a.strLodgeName, a.intLodgeNumber, a.strDistrictName, a.strLodgeWEB, a.strLodgeCounty, a.dtChartered, a.strLodgeMailingAddress, a.strLodgeMailingAddress2, a.strLodgeMailingCity, a.strLodgeMailingStateCode, a.strLodgeMailingPostCode, a.strLodgeEmail, a.strLodgePhone, a.strLodgeFax, a.strDrivingDirectons, a.dtMeetingTime, a.dtMealTime, a.strFloorSchool, a.strLodgeNews, b.strOfficerTitle, b.strFirstName, b.strLastName, b.BusinessPhone, b.PersEmail FROM tblLodges a LEFT JOIN tblOfficers b ON a.lngLodgeID = b.lngLodgeIDThen, to the above query, add your condition, WHERE a.intLodgeNumber=$id. Then check the result.
P.S. I m off to work. Be back in an hr.
Cheers,
Naveen
That's what I was thinking, too.
I keep playing around with it, but that line is a recommendation from that other thread and I don't know understand it. Thanks so much for your time
SOLVED!
I added
WHERE tblLodges.intLodgeNumber
to the end of the query
I would have never thought to try that field, but it is a common denominator between the tables.
Whew! I'm off to bed! Thanks so much Naveen!
