Filter left join results

mrt003003 · April 24, 2011

Hi there i have 3 tables joined together and am outputting the results ok. Heres what the results look like:

Fleet Name: Home One

Detected: No

Ship Name: Slave 1 Bobafett

Hull: 3 / 2

Fleet Name: Home Two

Detected: Yes

Ship Name: Imperial Star Destroyer

Hull: 2 / 4

Fleet Name: Home Two

Detected: Yes

Ship Name: Executer

Hull: 10 / 10

The 3 tables join together by the following:

Planet = parsed url parameter

Fleets = fleets on the current planet

Ships = current ships in the fleets

Its all working fine except for one thing, the Fleet Name is repeated for each occurance of a ship. I want to make it so that Fleet Name only occurs once for each fleet so it would look something like:

Fleet Name: Home One

Detected: No

Ship Name: Slave 1 Bobafett

Hull: 3 / 2

Fleet Name: Home Two

Ship Name: Imperial Star Destroyer

Detected: Yes

Hull: 2 / 4

Ship Name: Executer

Detected: Yes

Hull: 10 / 10

Is this possible???

Heres the code in full:

<?php require_once('Connections/swb.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_Planet = "-1";
if (isset($_GET['recordID'])) {
  $colname_Planet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Planet = sprintf("SELECT * FROM planet WHERE PlanetName = %s", GetSQLValueString($colname_Planet, "text"));
$Planet = mysql_query($query_Planet, $swb) or die(mysql_error());
$row_Planet = mysql_fetch_assoc($Planet);
$totalRows_Planet = mysql_num_rows($Planet);


$colname_Fleet = "-1";
if (isset($_GET['recordID'])) {
  $colname_Fleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Fleet = sprintf("SELECT p.PlanetName, p.PlayerName, f.FleetName, f.Detected, s.ShipName, s.HealthA, s.HealthB FROM Planet p
LEFT JOIN Fleet f ON (p.PlanetName = f.PlanetName)
LEFT JOIN Ships s ON (f.FleetName = s.FleetName)
WHERE p.PlanetName = '$colname_Planet'");
$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());
$row_Fleet = mysql_fetch_assoc($Fleet);
$totalRows_Fleet = mysql_num_rows($Fleet);
?>
<head>
<title>Untitled Document</title>
<style type="text/css">
<!--
body {
background-color: #FFFFFF;
}
-->
</style></head>

<body>

<table width="60%" border="0">
  <tr>
    <td width="10%">Planet:</td>
    <td width="14%"><?php echo $row_Planet['PlanetName']; ?></td>
    <td width="7%" rowspan="8"> </td>
    <td colspan="2" rowspan="8">

<?php while ($row_Fleet = mysql_fetch_assoc($Fleet)) {
     
echo '<table width="400" border="0"><tr><td width="200">';
echo 'Fleet Name: '; 		   
		 echo'</td><td>';
		echo $row_Fleet['FleetName']; 
		 echo'</td></tr><tr><td>';
		 echo 'Detected: ';
		 echo'</td><td>';
		 if ($row_Fleet['Detected'] == '1'){
 echo 'Yes';}
 else echo 'No';
 echo'</td></tr><tr><td>';
 echo 'Ship Name: ';
 echo'</td>';
        echo'<td>';
	echo $row_Fleet['ShipName'];
	echo'</td></tr><tr><td>';
	echo 'Hull: ';
	echo'</td><td>';
	echo $row_Fleet['HealthA'];
 echo ' / ';
 echo $row_Fleet['HealthB']; 
 echo '</td></tr><tr><td></td></tr>'; 
    echo'</table>'; } ?> 
  <tr>
    <td>Faction:</td>
    <td><?php echo $row_Planet['PlayerName']; ?></td>
  </tr>
  <tr>
    <td>Infastructure</td>
    <td> </td>
  </tr>
  <tr>
    <td>Class 1: </td>
    <td><?php echo $row_Planet['Class1']; ?></td>
  <tr>
    <td>Class 2: </td>
    <td><?php echo $row_Planet['Class2']; ?></td>
    </tr>
  <tr>
    <td>Class 3: </td>
    <td><?php echo $row_Planet['Class3']; ?></td>
  </tr>
  <tr>
    <td>Class 4: </td>
    <td><?php echo $row_Planet['Class4']; ?></td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td>  <a href="index.php target="main2.php"">Back</a></td>
    <td> </td>
    <td> </td>
    <td width="43%"> </td>
    <td width="26%"> </td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($Planet);
mysql_free_result($Fleet);
?>

Thanks

PFMaBiSmAd · April 24, 2011

See the following pseudo code. You will need to read the comments in it and alter it to suit your needs -

$heading_column = 'FleetName;
$last_heading = null;
while($row = your_fetch_assoc_statement){
// detect a change in the heading value and output the new heading
if($last_heading != $row[$heading_column]){
	// detect if it is not the first heading - close out the previous section
	if($last_heading != null){
		// your code to close the previouse table...
		echo "close section<br />";
	}
	// output the new heading here...
	echo "new section title - {$row[$heading_column]}<br />";

	// save the new heading as the last_heading
	$last_heading = $row[$heading_column];
}
// output the actual data here...
echo "data - {$row['your_data']}<br />";
}
// if there was any output - close out the last section
if($last_heading != null){
// your code to close the previouse table...
echo "close section<br ?>";
}

PFMaBiSmAd · April 25, 2011

BTW - the above code is missing a single-quote on the end of FleetName'; i the first line.

mrt003003 · April 25, 2011

Hi there thank you so much for the help, im really stuck here.

Can u just ask is this suppose to replace the exisiting While () function or to be added with it??

Thanks

PFMaBiSmAd · April 25, 2011

Your existing while(){} loop would be altered to use that logic to start and end each section when a new FleetName heading is detected.

mrt003003 · April 25, 2011

I've tried to modify my code and keep getting errors. Is there any way you could please, please show me hows its suppose to be put together. Sorry i'm new to this.

Thank you

PFMaBiSmAd · April 25, 2011

$heading_column = 'FleetName';
$last_heading = null;
while($row = mysql_fetch_assoc($Fleet)){
// detect a change in the heading value and output the new heading
if($last_heading != $row[$heading_column]){
	// detect if it is not the first heading - close out the previous section
	if($last_heading != null){
		// your code to close the previous table...
		echo "</table>\n";
	}
	// output the new heading here...
	echo "<table>\n";
	echo "<tr><th>Fleet Name: </th><th>{$row['FleetName']}</th></tr>\n";

	// save the new heading as the last_heading
	$last_heading = $row[$heading_column];
}
// output the actual data here...
echo "<tr><td>Ship Name: </td><td>{$row['ShipName']}</td></tr>\n";
echo "<tr><td>Detected: </td><td>";
if ($row['Detected'] == '1') echo 'Yes'; else echo 'No';
echo "</td></tr>\n";
echo "<tr><td>Hull: </td><td>{$row['HealthA']} / {$row['HealthB']}</td></tr>\n";
echo "<tr><td></td></tr>\n";
}
// if there was any output - close out the last section
if($last_heading != null){
// your code to close the previous table...
echo "</table>\n";
}

Also, I see in your code an extra $row_Fleet = mysql_fetch_assoc($Fleet); statement that is fetching and discarding the first row from your result set. You would probably want to remove that line from your code so that the first row of data doesn't turn up as missing.

mrt003003 · April 25, 2011

It almost works thank you very much!

If i remove that line (indeed the entire section):

	<?php while ($row_Fleet = mysql_fetch_assoc($Fleet)) {
echo '<table width="400" border="0"><tr><td width="200">';
echo 'Fleet Name: '; 		   
		 echo'</td><td>';
		echo $row_Fleet['FleetName']; 
		 echo'</td></tr><tr><td>';
		 echo 'Detected: ';
		 echo'</td><td>';
		 if ($row_Fleet['Detected'] == '1'){
 echo 'Yes';}
 else echo 'No';
 echo'</td></tr><tr><td>';
 echo 'Ship Name: ';
 echo'</td>';
        echo'<td>';
	echo $row_Fleet['ShipName'];
	echo'</td></tr><tr><td>';
	echo 'Hull: ';
	echo'</td><td>';
	echo $row_Fleet['HealthA'];
 echo ' / ';
 echo $row_Fleet['HealthB']; 
 echo '</td></tr><tr><td></td></tr>'; 
    echo'</table>';  ]?>

Then one of the ship records is missing for the fleet. However, if i keep it in then the additional ship records is displayed but not under either the Fleet Home One or Home Two heading but on its own underneath.

So close i can feel it. What would you suggest?

Thank you PFMaBiSmAd.

PFMaBiSmAd · April 26, 2011

^^^ I have no idea what that means, but you would need to post your current code if you need help with something it is doing.

Also, posting the output you are getting would help.

mrt003003 · April 27, 2011

Hi there thank you so much for not giving up on me...

I used your edited code you gave me...Removed the extra $row_Fleet = mysql_fetch_assoc($Fleet); statement and some redundant code. It all works fine except that the first Ship record is never outputted, all the others are fine. Heres the full code:

<?php require_once('Connections/swb.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_Planet = "-1";
if (isset($_GET['recordID'])) {
  $colname_Planet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Planet = sprintf("SELECT * FROM planet WHERE PlanetName = %s", GetSQLValueString($colname_Planet, "text"));
$Planet = mysql_query($query_Planet, $swb) or die(mysql_error());
$row_Planet = mysql_fetch_assoc($Planet);
$totalRows_Planet = mysql_num_rows($Planet);


$colname_Fleet = "-1";
if (isset($_GET['recordID'])) {
  $colname_Fleet = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_Fleet = sprintf("SELECT p.PlanetName, p.PlayerName, f.FleetName, f.Detected, s.ShipName, s.HealthA, s.HealthB FROM Planet p
LEFT JOIN Fleet f ON (p.PlanetName = f.PlanetName)
LEFT JOIN Ships s ON (f.FleetName = s.FleetName)
WHERE p.PlanetName = '$colname_Planet'");
$Fleet = mysql_query($query_Fleet, $swb) or die(mysql_error());
$row_Fleet = mysql_fetch_assoc($Fleet);
$totalRows_Fleet = mysql_num_rows($Fleet);
?>





<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
body {
background-color: #FFFFFF;
}
-->
</style></head>

<body>

<p>
<table width="200" border="0" align="center">
    <tr>
      <td>Planet:</td>
      <td><?php echo $row_Planet['PlanetName']; ?></td>
    </tr>
    <tr>
      <td>Faction:</td>
      <td><?php echo $row_Planet['PlayerName']; ?></td>
    </tr>
    <tr>
      <td>Infastructure</td>
      <td> </td>
    </tr>
    <tr>
      <td>Class 1: </td>
      <td><?php echo $row_Planet['Class1']; ?></td>
    </tr>
    <tr>
      <td>Class 2: </td>
      <td><?php echo $row_Planet['Class2']; ?></td>
    </tr>
    <tr>
      <td>Class 3: </td>
      <td><?php echo $row_Planet['Class3']; ?></td>
    </tr>
    <tr>
      <td>Class 4: </td>
      <td><?php echo $row_Planet['Class4']; ?></td>
    </tr>
    <tr>
      <td></td>
      <td><a href="index.php" target="main2.php">Back</a></td>
    </tr>
    <tr>
      <td> </td>
      <td> </td>
    </tr>
</table>

<?php 
$heading_column = 'FleetName';
$last_heading = null;
while($row = mysql_fetch_assoc($Fleet)){
// detect a change in the heading value and output the new heading
if($last_heading != $row[$heading_column]){
	// detect if it is not the first heading - close out the previous section
	if($last_heading != null){
		// your code to close the previous table...
		echo "</table>\n";
	}
	// output the new heading here...
	echo "<table width='400' border='1' align='center'>\n";
	echo "<tr><th width='200'>Fleet Name: </th><th width='200'>{$row['FleetName']}</th></tr>\n";

	// save the new heading as the last_heading
	$last_heading = $row[$heading_column];
}
// output the actual data here...
echo "<tr><td>Ship Name: </td><td>{$row['ShipName']}</td></tr>\n";
echo "<tr><td>Detected: </td><td>";
if ($row['Detected'] == '1') echo 'Yes'; else echo 'No';
echo "</td></tr>\n";
echo "<tr><td>Hull: </td><td>{$row['HealthA']} / {$row['HealthB']}</td></tr>\n";
echo'<br>';


}
// if there was any output - close out the last section
if($last_heading != null){
// your code to close the previous table...
echo "</table>\n";

echo '<table width="400" border="0"><tr><td width="200">';
echo 'Fleet Name: '; 		   
		 echo'</td><td>';
		echo $row_Fleet['FleetName']; 
		 echo'</td></tr><tr><td>';
		 echo 'Detected: ';
		 echo'</td><td>';
		 if ($row_Fleet['Detected'] == '1'){
 echo 'Yes';}
 else echo 'No';
 echo'</td></tr><tr><td>';
 echo 'Ship Name: ';
 echo'</td>';
        echo'<td>';
	echo $row_Fleet['ShipName'];
	echo'</td></tr><tr><td>';
	echo 'Hull: ';
	echo'</td><td>';
	echo $row_Fleet['HealthA'];
 echo ' / ';
 echo $row_Fleet['HealthB']; 
 echo '</td></tr><tr><td></td></tr>'; 
    echo'</table>'; } ?> 
</p>




     

<p> </p>
<p> </p>
<p> </p>
    <p> </p>
    <p>
     
    </p>
    <p> </p>



</body>
</html>
<?php
mysql_free_result($Planet);
mysql_free_result($Fleet);
?>

Heres what the output would look like:

Fleet Name: Home One

Detected: No

Ship Name: Slave 1 Bobafett

Hull: 3 / 2

Fleet Name: Home Two

Ship Name: Imperial Star Destroyer

Detected: Yes

Hull: 2 / 4

Not outputted: Ship Name: Executer

Not outputted: Detected: Yes

Not outputted: Hull: 10 / 10

The above record is not outputted and it is the first that is called in the database.

I hope i've explained my self well enough.

Thank you

PFMaBiSmAd · April 27, 2011

There's still a line of code (right after the line with the mysql_query() statement) that is fetching and discarding the first row from the result set -

$row_Fleet = mysql_fetch_assoc($Fleet);

mrt003003 · April 27, 2011

Yes of course! Sorry i missed that one.

Thank you so much for the help

mrt003003 · April 27, 2011

On another page if i had only the shipID and from that i wanted to determine the fleet and planet that ship was in would i be able use a rearranged version of the join?? Ive had a go and got:

$colname_test = "-1";
if (isset($_GET['recordID'])) {
  $colname_test = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
}
mysql_select_db($database_swb, $swb);
$query_test = sprintf("SELECT s.ShipID, s.ShipName, s.HealthA, s.HealthB, f.FleetName, f.Detected, f.PlanetName, p.PlanetName, p.PlayerName FROM Ships s
LEFT JOIN Planet f ON (p.PlanetName = f.PlanetName)
LEFT JOIN Fleet s ON (s.FleetName = f.FleetName)
WHERE s.ShipID = '$colname_Ship'");
$test = mysql_query($query_test, $swb) or die(mysql_error());
$totalRows_test = mysql_num_rows($test);

I keep getting an error: Not unique table/alias: 's'

The reason i ask is because I have made the ships hyperlinked to an editng page where they can be edited individually. I wanted a BACK hyperlink that would got back to the planet.php page but also parse the PlanetName as a url parameter.

Sign In

Filter left join results

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

Join the conversation

Important Information