Pulling My Hair Out - Pulling Data from two tables but not wanting dual results?

[JTapp]

Sorry for the immature code and language.. I'm a beginner.

 

Situation - I've got two tables let's just say Companies and Employees.  Some Companies have one employee others have several.  I'm needing to format the webpage so the company information is at the top and there is sort of a "subform" (forgive the MS Access talk) at the bottom of the page listing the employees and their contact information.

 

Problem - When there is just one employee everything looks great.

But when there are more 2 or more employees, the upper half of my webpage which is the company information repeats the information according to how many employees there are.

 

Here is the code (to view it in action go to: http://www.la-mason.com/search15.html and search for 'Lodge Name' = "Abbeville":

 

//query details table begins

$query = mysql_query("SELECT tblLodges.strLodgeName, tblLodges.intLodgeNumber, tblLodges.strDistrictName, tblLodges.strLodgeWEB, tblLodges.strLodgeCounty, tblLodges.dtChartered, tblLodges.strLodgeAddress, tblLodges.strLodgeAddress2, tblLodges.strLodgeLocationCity, tblLodges.strLodgeLocationState, tblLodges.strLodgeLocationZip, tblLodges.strLodgeEmail, tblLodges.strLodgePhone, tblLodges.strLodgeFax,  tblLodges.strDrivingDirectons, tblLodges.dtMeetingTime, tblLodges.dtMealTime, tblLodges.strFloorSchool, tblLodges.strLodgeNews, tblOfficers.strFirstName, tblOfficers.strLastName FROM tblLodges LEFT JOIN tblOfficers ON tblLodges.lngLodgeID = tblOfficers.lngLodgeID WHERE $metode LIKE '%$search%' LIMIT 0, 50");

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["strLodgeAddress"];

$variable8=$row["strLodgeAddress3"];

$variable9=$row["strLodgeLocationCity"];

$variable10=$row["strLodgeLocationState"];

$variable11=$row["strLodgeLocationZip"];

$variable12=$row["strLodgeEmail"];

$variable13=$row["strLodgePhone"];

$variable14=$row["strLodgeFax"];

$variable15=$row["strDrivingDirectons"];

$variable16=$row["dtMeetingTime"];

$variable17=$row["dtMealTime"];

$variable18=$row["strFloorSchool"];

$variable19=$row["strLodgeNews"];

//table layout for results

 

print ("<tr>");

echo '<p><h3>' . $variable1 . '</h3></p><p>' . $variable2 . '</p>';

echo '<p>' . $variable3 . '</p>' . $variable4 . '</p>';

echo '<p>' . $variable5 . '</p>' . $variable6 . '</p>';

echo '<p>' . $variable7 . '</p>' . $variable8 . '</p>' . $variable9 . '</p>' . $variable10 . '</p>' . $variable11 . '</p>';

echo '<p>' . $variable12 . '</p>' . $variable13 . '</p>' . $variable14 . '</p>';

echo '<p>' . $variable15 . '</p>';

echo '<p>' . $variable16 . '</p>' . $variable17 . '</p>' . $variable18 . '</p>';

echo '<p>' . $variable19 . '</p>';

 

print ("</tr>");

}

 

?>                              </tr>

                            </table>

                            <hr width=75% align=center size=4>

                            <p><strong>Roster of Lodge Officers </strong></p>

 

                            <p>

 

<?php

//query details table begins

$query = mysql_query("SELECT tblLodges.strLodgeName, tblLodges.intLodgeNumber, tblLodges.strDistrictName, tblLodges.strLodgeLocationCity, tblLodges.strLodgeLocationZip, tblLodges.strLodgeCounty, tblOfficers.strFirstName, tblOfficers.strLastName, tblOfficers.BusinessPhone, tblOfficers.PersEmail FROM tblLodges LEFT JOIN tblOfficers ON tblLodges.lngLodgeID = tblOfficers.lngLodgeID WHERE $metode LIKE '%$search%' LIMIT 0, 50");

 

$results=mysql_query($query);

echo "<table border='1'>

  <tr>

<th>Officer First</th>

<th>Officer last</th>

<th>Officer Email</th>

<th>Officer Phone</th>

 

</tr>";

//

 

while ($row = mysql_fetch_array($query))

{

$variable1=$row["strFirstName"];

$variable2=$row["strLastName"];

$variable3=$row["PersEmail"];

$variable4=$row["BusinessPhone"];

//table layout for results

 

print ("<tr>");

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

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

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

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

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

 

print ("</tr>");

}

?>

 

Thanks in advance for checking out my problem.

[wildteen88]

Try adding GROUP BY tbl.lodgename field here to the end of your query, should stop the duplicates.

[JTapp]

Thanks for your Post!!

Sorry for the ignorance.. but could you tell me exactly where to put it?

[spfoonnewb]

Any particular reason why you have the variables like that?

 

<?php
$query = mysql_query("SELECT tblLodges.strLodgeName, tblLodges.intLodgeNumber, tblLodges.strDistrictName, tblLodges.strLodgeWEB, tblLodges.strLodgeCounty, tblLodges.dtChartered, tblLodges.strLodgeAddress, tblLodges.strLodgeAddress2, tblLodges.strLodgeLocationCity, tblLodges.strLodgeLocationState, tblLodges.strLodgeLocationZip, tblLodges.strLodgeEmail, tblLodges.strLodgePhone, tblLodges.strLodgeFax,  tblLodges.strDrivingDirectons, tblLodges.dtMeetingTime, tblLodges.dtMealTime, tblLodges.strFloorSchool, tblLodges.strLodgeNews, tblOfficers.strFirstName, tblOfficers.strLastName FROM tblLodges LEFT JOIN tblOfficers ON tblLodges.lngLodgeID = tblOfficers.lngLodgeID WHERE $metode LIKE '%$search%' GROUP BY tblLodges.strLodgeName LIMIT 0, 50");
if (mysql_num_rows($query) > 0) {
while ($row = mysql_fetch_array($query)) {
	print ("<tr>");
	echo '<p><h3>' . $row["strLodgeName"] . '</h3></p><p>' . $row["intLodgeNumber"] . '</p>';
	echo '<p>' . $row["strDistrictName"] . '</p>' . $row["strLodgeWEB"] . '</p>';
	echo '<p>' . $row["strLodgeCounty"] . '</p>' . $row["dtChartered"] . '</p>';
	echo '<p>' . $row["strLodgeAddress"] . '</p>' . $row["strLodgeAddress3"] . '</p>' . $row["strLodgeLocationCity"] . '</p>' . $row["strLodgeLocationState"] . '</p>' . $row["strLodgeLocationZip"] . '</p>';
	echo '<p>' . $row["strLodgeEmail"] . '</p>' . $row["strLodgePhone"] . '</p>' . $row["strLodgeFax"] . '</p>';
	echo '<p>' . $row["strDrivingDirectons"] . '</p>';
	echo '<p>' . $row["dtMeetingTime"] . '</p>' . $row["dtMealTime"] . '</p>' . $row["strFloorSchool"] . '</p>';
	echo '<p>' . $row["strLodgeNews"] . '</p>';
	print ("</tr>");
}
}
else {
echo "No data found for the input provided....";
}
?>

[JTapp]

Indeed.  I have no idea what I'm doing and copied it from another post.

 

Your stomach might turn if I tell you the truth... but here goes.

There are 20 variables in the table - I've listed 19 variables instead of using SELECT * because I don't know how to format them otherwise... I need to be able to center the results in the page and format their sizes and spaces, etc.  After countless hours of searching, I couldn't find a tutorial that talks abut formating your data (I found one that talks about tables, hence my little subform).

 

Anyway.. thanks so much for your help.  Your suggestion worked nicely.

Here's a PHP Freaks question for you.. how do I end my post as "resolved" if I am the last person to reply to the posting?

 

Thanks again and have a great evening.

[spfoonnewb]

I see, I was just wondering really, just seemed a bit redundant and created extra lines.

 

I would have to look at the SQL table to help out with any with the SELECT statement.. but it works for now.

 

You also should be using mysql_real_escape_string() on the users input variable and/or cleaning it from other things it shouldn't contain to prevent SQL injection.

 

I don't remember where the topic solved button is, but only you can see it and I think it's on the bottom or top left..