while in while

[geudrik]

So, I can't quite figure out why this doesn't work, but it doesn't.

 

What I'm trying to do is pull data from 3 different tables from the same while loop... here's the code. 

Note: everything else works. If I take the inner two while's out, it works fine.  As of now, I get a 500 Internal error...

Can someone explain to me why this doesn't work?

 

<?php

if(isset($_POST['systemsearch'])) {
$system = $_POST['system'];
$sql4 = "SELECT regionID, constellationID, solarSystemName, security FROM mapSolarSystems WHERE solarSystemName = '$system' ORDER BY solarSystemName";
$result4 = mysql_query($sql4);

$systemout = "<table width=\"400\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">";
while($row4 = mysql_fetch_assoc($result4))
{
	$system = $row4['solarSystemName'];
	$secsts = $row4['security'];
	$consid = $row4['constellationID'];
	$regid  = $row4['regionID'];

	$systemout .= "
		   <tr>
    			<td>$system</td>
   				<td>$secsts</td>";

			$get_cons_sql = "SELECT constellationName FROM mapConstellations WHERE constellationID = '$consid'";
			$get_reg_sql  = "SELECT regionName        FROM mapRegions        WHERE regionID        = '$regid'";

			while($get_cons_row = mysql_fetch_assoc(mysql_query($get_cons_sql) or die(mysql_error())))
			{
				$systemout .= "<td>".$get_cons_row['constellationName']."</td>";
			}

			while($get_reg_row = mysql_fetch_assoc(mysql_query($get_reg_sql) or die(mysql_error())))
			{
				$systemout .= "<td>".$get_reg_row['regionName']."</td>";
			}

  			  
		  
	$systemout .= "</tr>";
}
$systemout .= "</table>";

//Chunk to get Constellation and Region information



}

?>

 

Edit: Having added the mysql_error()'s into the inner two while's, I now get...

Warning: [b]mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/b] in /home/geudrik/xxxxx.net/tools.php/secadvisor.php on line 111

Warning: [b]mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource[/b] in /home/geudrik/xxxxx.net/tools.php/secadvisor.php on line 116

[geudrik]

Bumps

[kickstart]

Hi

 

I suspect it might be trying to do an endless loop. Each while statement will be evaluated each time it tries to loop, and by evaluating them it will execute the sql again.

 

Split the lines up:-

 

$_cons_rs = mysql_query($get_cons_sql) or die(mysql_error());
while($get_cons_row = mysql_fetch_assoc($_cons_rs))
{
$systemout .= "<td>".$get_cons_row['constellationName']."</td>";
}

$_reg_rs = mysql_query($get_reg_sql) or die(mysql_error());
while($get_reg_row = mysql_fetch_assoc($_reg_rs))
{
$systemout .= "<td>".$get_reg_row['regionName']."</td>";
}

 

All the best

 

Keith

[geudrik]

The issue with this method is that the table won't be displayed correctly.  reg_rs and cons_rs are each a column cell, with closing tr tag being written in at the end by the parent while.  the two sub While's are just children of the innitial while.  Does this make sense?  Look at how the HTML is being spit out from my original code and that's the issue I'm now having. 

 

Ideas? Suggestions?

[kickstart]

Hi

 

Afraid I cannot see the issue you have. Other than taking out what looks like an endless loop I think it gives you what you want.

 

Can you give an example of what you expect to be output

 

However it might be best to just use a JOIN in you original SQL and do it all in one loop:-

 

<?php

if(isset($_POST['systemsearch'])) {
$system = mysql_real_escape_string($_POST['system']);
$sql4 = "SELECT solarSystemName, security,  constellationName, regionName 
FROM mapSolarSystems a 
LEFT OUTER JOIN mapConstellations b ON a.constellationID = b.constellationID
LEFT OUTER JOIN regionName c ON a.regionID = c.regionID
WHERE solarSystemName = '$system' ORDER BY solarSystemName";
$result4 = mysql_query($sql4);

$systemout = "<table width=\"400\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">";
while($row4 = mysql_fetch_assoc($result4))
{
	$system = $row4['solarSystemName'];
	$secsts = $row4['security'];
	$consname = $row4['constellationName'];
	$regname  = $row4['regionName'];

	$systemout .= "<tr><td>$system</td><td>$secsts</td><td>$consname</td><td>$regname</td></tr>";
}
$systemout .= "</table>";

//Chunk to get Constellation and Region information
}

?>

 

All the best

 

Keith

[Adam]

Aah, as kickstart just pointed out your perhaps best re-writing the code so you don't need several loops within a loop. I can't really test this so I'm not sure if it'll work with your table structure but, take a look at what data is returned from this:

 

SELECT mss.regionID, mss.constellationID, mss.solarSystemName, mss.security, mc.constellationName, mr.regionName
FROM mapSolarSystems mss, mapConstellations mc, mapRegions mr
WHERE solarSystemName = '$system'
AND mc.constellationID = mss.constellationID
AND mr.regionID = mss.regionID
ORDER BY solarSystemName;

 

 

[geudrik]

MrAdam - you're brilliant! Works like a charm.

 

www.spaazz.net/tools.php/secadvisor.php  -> seach for Jita or Kisogo or S-U8A4

 

This sql makes little sense to me, I'm not familiar to the methods you used in modifying the query.  Can you explain just what you did to pull this off, because I KNOW that I have no tables (or rows) prefixed with mss. or mc. or mr.

[kickstart]

Hi

 

What we have both given you are table joins. Just we have done them in slightly different ways.

 

For the code I suggested:-

 

SELECT solarSystemName, security,  constellationName, regionName

FROM mapSolarSystems a

LEFT OUTER JOIN mapConstellations b ON a.constellationID = b.constellationID

LEFT OUTER JOIN regionName c ON a.regionID = c.regionID

WHERE solarSystemName = '$system' ORDER BY solarSystemName

 

A JOIN links the rows from 2 tables. The ON clause specifies which columns you use to link up the 2 tables. In a conventional JOIN (INNER JOIN) you will only get anything out if there are matching rows on both tables. With an OUTER JOIN you essentially have one table on which the rows will always be returned, and another table which there may be date for.

 

What the above code is doing is returning the details from mapSolarSystems where the solar system name is as you specified. If there is a matching constellation it brings that back as well, if not the constellation field is blank.  If there is a matching region it brings that back as well, if not the region field is blank.

 

If there were multiple constellations with the same constellationID then the code would bring back multiple lines, one for each possible constellationID.

 

All the best

 

Keith