[Resolved] query question, i think???

jazappi

I have a table that is like this

sitename | firewallmodel | wanip | lanip | mask

sometimes the sitename, firewallmodel and wanip will have multiple lanip's and mask's.

What would be the best way to query my db and have it only print the sitename, firewallmodel, and wanip once, then have each lanip and mask underneath it?

this is what i have now, it prints all the info on each line:


$result = mysql_query("SELECT * FROM provision")
    or die ("Invalid query");


print (" <table width=\"680\" border=\"0\" align=\"left\" cellpadding=\"5\" cellspacing=\"1\">");
print ("        <tr class=\"text\">");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong>Site Name </strong></div></td>");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong>Firewall Model and firmware version </strong></div></td>");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong>WAN IP </strong></div></td>");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong>LAN Subnet(s)</strong></div></td>");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong> Mask(s)</strong></div></td>");
print ("        </tr>");
  while($r=mysql_fetch_array($result)) 
	{ 
		$company_id=$r["company_id"];
		$secSitename=$r["secSitename"];
		$secFiremodel=$r["secFiremodel"];
		$secWAN=$r["secWAN"];
		$secLAN=$r["secLAN"];
		$secMask=$r["secMask"];

print ("        <tr class=\"text\">");
print ("          <td bgcolor=\"#F7F7F7\"><div align=\"center\">$secSitename</div></td>");
print ("          <td bgcolor=\"#F7F7F7\">");
print ("              <div align=\"center\">$secFiremodel");
print ("            </div></td>");
print ("          <td bgcolor=\"#F7F7F7\"><div align=\"center\">$secWAN</div></td>");
print ("          <td bgcolor=\"#F7F7F7\"><div align=\"center\">$secLAN</div></td>");
print ("          <td bgcolor=\"#F7F7F7\">");
print ("              <div align=\"center\">$secMask");
print ("            </div></td>");
print ("        </tr>");
}
print ("      </table>

Any suggestions would be great. Thanks for the help.

kingbear

Here's one way --


$res = mysql_query("select distinct sitename, firewallmodel, wanip from theTable");

while($row = mysql_fetch_array($res)) {
$sitename = $row["sitename"];
$firewallmodel = $row["firewallmodel"];
$wanip = $row["wanip"];

$res2 = mysql_query("select distinct lanip, mask from theTable where sitename='$sitename' and firewallmodel='$firewallmodel' and wanip='$wanip'");

echo("sitename: $sitename<br>");
echo("firewallmodel: $firewallmodel<br>");
echo("wanip: $wanip<br>");

while($row2 = mysql_fetch_array($res2)) {
echo($row2['lanip'] . "<br>");
echo($row2['mask'] . "<br>");
}


}

jazappi

Thanks kingbear, that worked perfect. You helped me once again.

jazappi

Okay, thought I had it figured out, I have another question. If I want that in a table, where it displays it horizontally.

For example:

Hopefully that formats right.

jazappi

That didn't show up right basicly I want it to have the additional lanip and masks on a row below the site name and wanip, but I want the first lanip and masks on the same line a s the sitename and wanip.

kingbear


$res = mysql_query("select distinct sitename, firewallmodel, wanip from theTable");

echo("<table>");

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

echo("<tr>");
$sitename = $row["sitename"];
$firewallmodel = $row["firewallmodel"];
$wanip = $row["wanip"];

$res2 = mysql_query("select distinct lanip, mask from theTable where sitename='$sitename' and firewallmodel='$firewallmodel' and wanip='$wanip'");

echo("<td>sitename: $sitename</td>");
echo("<td>firewallmodel: $firewallmodel</td>");
echo("<td>wanip: $wanip</td>");

$first = true;

while($row2 = mysql_fetch_array($res2)) {
if($first) {
$first = false;
}
else {
echo("<tr><td></td><td></td><td></td>");
}

echo("<td>" . $row2['lanip'] . "</td>");
echo("<td>" . $row2['mask'] . "</td>");
echo("</tr>");
}
}

echo("</table>");

jazappi

Thanks once again!! Hopefully I am doing asking questions for awhile.

jazappi

Back again. For some reason its not pulling the right data. The wanip is all the same thing not unique, and its not display the lanip and mask unless there is multiples, sometimes there won't be. Any more suggestions?? Here is the actual code with the right varible names and table names.

//testing with table
$res = mysql_query("select distinct secSitename, secFiremodel, secWAN from provision"); 

echo("<table width=\"680\" border=\"0\" align=\"left\" cellpadding=\"5\" cellspacing=\"1\">"); 
print ("        <tr class=\"text\">");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong>Site Name </strong></div></td>");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong>Firewall Model and firmware version </strong></div></td>");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong>WAN IP </strong></div></td>");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong>LAN Subnet(s)</strong></div></td>");
print ("          <td bgcolor=\"#CCCCCC\"><div align=\"center\"><strong> Mask(s)</strong></div></td>");
print ("        </tr>");

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

echo("<tr>"); 
$secSitename = $row["secSitename"]; 
$secFiremodel = $row["secFiremodel"]; 
$wanip = $row["secWAN"]; 

$res2 = mysql_query("select distinct secLAN, secMask from provision where secSitename='$secSitename' and secFiremodel='$secFiremodel' and secWAN='$secWAN'"); 

echo("<td bgcolor=\"#F7F7F7\">$secSitename</td>"); 
echo("<td bgcolor=\"#F7F7F7\">$secFiremodel</td>"); 
echo("<td bgcolor=\"#F7F7F7\">$secWAN</td>"); 

$first = true; 

while($row2 = mysql_fetch_array($res2)) { 
if($first) { 
$first = false; 
} 
else { 
echo("<tr><td></td><td></td><td></td>"); 
} 

echo("<td bgcolor=\"#F7F7F7\">" . $row2['secLAN'] . "</td>"); 
echo("<td bgcolor=\"#F7F7F7\">" . $row2['secMask'] . "</td>"); 
echo("</tr>"); 
} 
} 

echo("</table>");

jazappi

Fixed it already, had a misnamed varible. Thanks again.