wisper Posted June 30, 2008 Share Posted June 30, 2008 Ok I am building a page that pulls all the stuff from the database about ships and puts them into there departments. Now there are assigned ships and unassigned ships. Which is where I am having my problems. The dept section to follow displays fine. <?php /**System Version: 2.5.0Last Modified: 2007-05-01 1221 EST**//* access check */if( in_array( "r_ships", $sessionAccess ) ) {/* define the page class and set vars */$pageClass = "logistics";$subMenuClass = "reports";$display = $_GET['disp'];/* pull in the main navigation */if( isset( $sessionCrewid ) ) { include_once( 'skins/' . $sessionDisplaySkin . '/menu.php' );} else { include_once( 'skins/' . $skin . '/menu.php' );}/* if there is no GET variable, set it to the players manifest */if( !$display ) { $display = "ship";}if( $display == "ship" ) { $departments = "SELECT * FROM sms_departments WHERE deptDisplay = 'y' "; $departments.= "AND deptType = 'playing' ORDER BY deptOrder ASC";} elseif( $display == "past" ) { $departments = "SELECT * FROM sms_departments WHERE deptDisplay = 'y' "; $departments.= "AND deptType = 'playing' ORDER BY deptOrder ASC";}$deptResults = mysql_query( $departments );?><div class="body"> <span class="fontTitle"> <? if( $display == "ship" ) { echo "Defence Force Ships"; } elseif( $display == "past" ) { echo "Decommssioned Ships"; } ?> </span><br /><br /> <!-- manifest navigation table --> <div align="center"> <span class="fontSmall"> <a href="<?=$webLocation;?>logistics.php?page=reports⊂=ships&disp=ship">Defence Force Ships</a> · <a href="<?=$webLocation;?>logistics.php?page=reports⊂=ships&disp=past">Decommssioned Ships</a> </span> </div> <table> <? /* pull the data out of the department query */ while ( $dept = mysql_fetch_array( $deptResults ) ) { extract( $dept, EXTR_OVERWRITE ); ?> <tr> <td colspan="4" height="15"></td> </tr> <tr> <td colspan="4"> <font class="fontMedium" color="#<?=$deptColor;?>"> <b><? printText( $deptName ); ?></b> </font> </td> </tr> This is the section I am having an issue with. In here s.shipPilotid = c.crewid now if the ship is unassigned s.shipPilotid is set to zero, which is not a c.crewid. Now short of adding a crewid of zero; which I don't want to do because it will mess with other pages; I decided to add a IF statement which I kinda of got working using another method but it completly screwed everything up with accounting for ships. It double printed them with the owner of the vessel in the spot as well. As it is right now it prints out all the information for the vessel if the shipPilotid is > 0, but doesn't print anything if it = 0. You can forget about the "past" portion for the moment. What am I missing here that is screwing everything up? <? if( $display == "ship" ) { $manifest = "SELECT c.crewid, c.firstName, c.lastName, c.rankid, r.rankShortName, s.shipid, s.shipname, s.squadronSize, s.deptid, s.shipclass, "; $manifest.= "s.shipPilotid FROM sms_ships as s, sms_crew AS c, sms_ranks AS r "; $manifest.= "WHERE s.deptid = '$dept[deptid]' AND s.shipStatus = 'active' AND c.rankid = r.rankid "; $manifest.= "AND s.shipPilotid = c.crewid ORDER BY s.shipType ASC"; $manifestResults = mysql_query( $manifest ); } elseif( $display == "past" ) { $manifest = "SELECT c.crewid, c.firstName, c.lastName, c.rankid, r.rankShortName, s.shipid, s.shipname, s.squadronSize, s.deptid, s.shipclass, "; $manifest.= "s.shipPilotid FROM sms_ships as s, sms_crew AS c, sms_ranks AS r "; $manifest.= "WHERE s.deptid = '$dept[deptid]' AND s.shipStatus = 'inactive' AND c.rankid = r.rankid "; $manifest.= "AND s.shipPilotid = c.crewid ORDER BY s.shipType ASC"; $manifestResults = mysql_query( $manifest ); } while ( $manifestList = mysql_fetch_assoc($manifestResults) ) { extract( $manifestList, EXTR_OVERWRITE ); if ( $display == "ship" || "past" ) { if ( $manifestList['shipPilotid'] == 0 ) { ?> <tr> <td valign="middle" style="padding-left: 1em;"><b><? printText( $shipname ); ?> <? printText( $squadronSize ); ?></b></td> <td width="10%" valign="middle" align="right"></td> <td valign="middle" align="middle"><? printText( $shipclass ); ?></td> <td valign="middle"> <? echo "<font class='fontSmall' color='yellow'>"; printText( "[Not Assigned]" ); echo "</font>"; ?> </td> </tr> <tr colspn='4'> </tr> <? } else {?> <tr> <td valign="middle" style="padding-left: 1em;"><b><? printText( $shipname ); ?> <? printText( $squadronSize ); ?></b></td> <td width="10%" valign="middle" align="right"></td> <td valign="middle" align="middle"><? printText( $shipclass ); ?></td> <td valign="middle"> <? echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font>"; } ?> </td> </tr> <tr colspn='4'> </tr> <? } } } ?> </table></div><? } ?> Link to comment Share on other sites More sharing options...
Jesdisciple Posted June 30, 2008 Share Posted June 30, 2008 Is this the 'if' that's giving you problems? if ( $display == "ship" || "past" ) Because "past" will always be true; if you mean this then you need to type it - there is no shorthand for it: if ( $display == "ship" || $display == "past" ) Link to comment Share on other sites More sharing options...
wisper Posted June 30, 2008 Author Share Posted June 30, 2008 No this is the 'IF' I mean if ( $manifestList['shipPilotid'] == 0 ) { the other one works fine. It is how its intended to work. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 30, 2008 Share Posted June 30, 2008 if ( $display == "ship" || "past" ) You realize that will always be true, right? It's the exact same thing as this:if (true)Is that how you intended it to work? If you don't believe me, set $display to anything and see what happens.Are you sure the database is returning results? After you run this:$manifestResults = mysql_query( $manifest );Run this to make sure that it is actually returning what you expect:print_r($manifestResults);If you don't have a crewid of 0 then, using the SQL join you're using (inner join), it's not going to return results. You would need to use a left join or right join. Link to comment Share on other sites More sharing options...
wisper Posted June 30, 2008 Author Share Posted June 30, 2008 if I add the print_r i get these Resource id #27 Resource id #28 Resource id #29 Resource id #30 Resource id #31 Resource id #32 Resource id #33 Resource id #34 Resource id #35Have no clue what that means. I know it returns 0 because when i do this it works fine <? if( $display == "ship" ) { $manifest = "SELECT c.firstName, c.lastName, c.rankid, r.rankShortName, s.shipid, s.shipname, s.squadronSize, s.deptid, s.shipclass, "; $manifest.= "s.shipPilotid FROM sms_ships as s, sms_crew AS c, sms_ranks AS r "; $manifest.= "WHERE s.deptid = '$dept[deptid]' AND s.shipStatus = 'active' AND c.rankid = r.rankid "; $manifest.= "AND (s.shipPilotid = c.crewid OR s.shipCommanderid = c.crewid) ORDER BY s.shipType ASC "; $manifestResults = mysql_query( $manifest ); } elseif( $display == "past" ) { $manifest = "SELECT c.crewid, c.firstName, c.lastName, c.rankid, r.rankShortName, s.shipid, s.shipname, s.squadronSize, s.deptid, s.shipclass, "; $manifest.= "s.shipPilotid FROM sms_ships as s, sms_crew AS c, sms_ranks AS r "; $manifest.= "WHERE s.deptid = '$dept[deptid]' AND s.shipStatus = 'inactive' AND c.rankid = r.rankid "; $manifest.= "AND s.shipPilotid = c.crewid ORDER BY s.shipType ASC"; $manifestResults = mysql_query( $manifest ); } while ( $manifestList = mysql_fetch_assoc($manifestResults) ) { extract( $manifestList, EXTR_OVERWRITE ); if ( $display == "ship" || $display == "past" ) { if ( $manifestList['shipPilotid'] == 0 ) { ?> <tr> <td valign="middle" style="padding-left: 1em;"><b><? printText( $shipname ); ?> <? printText( $squadronSize ); ?></b></td> <td width="10%" valign="middle" align="right"></td> <td valign="middle" align="middle"><? printText( $shipclass ); ?></td> <td valign="middle"> <? echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font><br />"; echo "<font class='fontSmall' color='yellow'>"; printText( "[Not Assigned]" ); echo "</font>"; ?> </td> </tr> <tr colspn='4'> </tr> <? } else {?> <tr> <td valign="middle" style="padding-left: 1em;"><b><? printText( $shipname ); ?> <? printText( $squadronSize ); ?></b></td> <td width="10%" valign="middle" align="right"></td> <td valign="middle" align="middle"><? printText( $shipclass ); ?></td> <td valign="middle"> <? echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font>"; } ?> </td> </tr> <tr colspn='4'> </tr> <? } } } ?> </table> The only problem I have is it prints out a entry for the Pilot and the Commander separately. If I could figure out how to get the Pilot and Commander on the same entry I would be happy. Link to comment Share on other sites More sharing options...
justsomeguy Posted June 30, 2008 Share Posted June 30, 2008 That would be a very complex query involving a few joins. It's probably best to get the results like they are and keep track of the last ship that you printed out. If the current record is for the same ship then you print it on the same line as the last one, or else you start a new line. Link to comment Share on other sites More sharing options...
wisper Posted July 1, 2008 Author Share Posted July 1, 2008 No longer needed!!! Link to comment Share on other sites More sharing options...
wisper Posted July 1, 2008 Author Share Posted July 1, 2008 ok this is what I have now: <? if( $display == "ship" ) { $manifest = "SELECT c.firstName, c.lastName, c.rankid, r.rankShortName, s.shipid, s.shipname, s.squadronSize, s.deptid, s.shipclass, "; $manifest.= "s.shipPilotid, s.shipCommanderid FROM sms_ships as s, sms_crew AS c, sms_ranks AS r "; $manifest.= "WHERE s.deptid = '$dept[deptid]' AND s.shipStatus = 'active' AND c.rankid = r.rankid "; $manifest.= "AND (s.shipCommanderid = c.crewid OR s.shipPilotid = c.crewid) ORDER BY s.shipid ASC "; $manifestResults = mysql_query( $manifest ); } elseif( $display == "past" ) { $manifest = "SELECT c.crewid, c.firstName, c.lastName, c.rankid, r.rankShortName, s.shipid, s.shipname, s.squadronSize, s.deptid, s.shipclass, "; $manifest.= "s.shipPilotid FROM sms_ships as s, sms_crew AS c, sms_ranks AS r "; $manifest.= "WHERE s.deptid = '$dept[deptid]' AND s.shipStatus = 'inactive' AND c.rankid = r.rankid "; $manifest.= "AND s.shipPilotid = c.crewid ORDER BY s.shipType ASC"; $manifestResults = mysql_query( $manifest ); } while ( $manifestList = mysql_fetch_assoc($manifestResults) ) { extract( $manifestList, EXTR_OVERWRITE ); ?> <tr> <td valign="middle" style="padding-left: 1em;"><b><? printText( $shipname ); ?> <? printText( $squadronSize ); ?></b></td> <td width="10%" valign="middle" align="right"></td> <td valign="middle" align="middle"><? printText( $shipclass ); ?></td> <td valign="middle"> <? if ( $manifestList['shipCommanderid'] == $manifestList['shipPilotid'] ) { echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font>"; } elseif ( $manifestList['shipPilotid'] == 0 ) { echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font><br />"; echo "<font class='fontSmall' color='yellow'>"; printText( "[Not Assigned]" ); echo "</font>"; } else { echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font><br />"; echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font>"; } ?> </td> </tr> <tr colspn='4'> </tr> <? } } ?> </table> and it gives me something that looks like this:Now my question is how can I merge the two records in red to make one with the info circled in green in that positon. Link to comment Share on other sites More sharing options...
wisper Posted July 1, 2008 Author Share Posted July 1, 2008 I got it to put the names in the right spots but I still get the double entry (which is circled in red). I have color coded them so you can tell which if statement is which. Green is the if ( $manifestList['shipCommanderid'] == $manifestList['shipPilotid'] ) { orange is } elseif ( $manifestList['shipPilotid'] == 0 ) { and red is the } elseif ( $manifestList['shipCommanderid'] > 0 || $manifestList['shipPilotid'] > 0 ) { here is the code I am using. It should be noted that this is just the actual ship portion and not the Departments or the rest of the page. <? if( $display == "ship" ) { $manifest = "SELECT c.firstName, c.lastName, c.rankid, r.rankShortName, s.shipid, s.shipname, s.squadronSize, s.deptid, s.shipclass, "; $manifest.= "s.shipPilotid, s.shipCommanderid FROM sms_ships as s, sms_crew AS c, sms_ranks AS r "; $manifest.= "WHERE s.deptid = '$dept[deptid]' AND s.shipStatus = 'active' AND c.rankid = r.rankid "; $manifest.= "AND (s.shipCommanderid = c.crewid OR s.shipPilotid = c.crewid) ORDER BY s.shipid ASC "; $manifestResults = mysql_query( $manifest ); } elseif( $display == "past" ) { $manifest = "SELECT c.firstName, c.lastName, c.rankid, r.rankShortName, s.shipid, s.shipname, s.squadronSize, s.deptid, s.shipclass, "; $manifest.= "s.shipPilotid, s.shipCommanderid FROM sms_ships as s, sms_crew AS c, sms_ranks AS r "; $manifest.= "WHERE s.deptid = '$dept[deptid]' AND s.shipStatus = 'inactive' AND c.rankid = r.rankid "; $manifest.= "AND (s.shipCommanderid = c.crewid OR s.shipPilotid = c.crewid) ORDER BY s.shipid ASC "; $manifestResults = mysql_query( $manifest ); } while ( $manifestList = mysql_fetch_assoc($manifestResults) ) { extract( $manifestList, EXTR_OVERWRITE ); ?> <tr> <td valign="middle" style="padding-left: 1em;"><b><? printText( $shipname ); ?> <? printText( $squadronSize ); ?></b></td> <td width="10%" valign="middle" align="right"></td> <td valign="middle" align="middle"><? printText( $shipclass ); ?></td> <td valign="middle" align="middle"> <? if ( $manifestList['shipCommanderid'] == $manifestList['shipPilotid'] ) { echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font>"; } elseif ( $manifestList['shipPilotid'] == 0 ) { echo "<font class='fontSmall'>"; printText( $rankShortName . " " . $firstName . " " . $lastName ); echo "</font><br />"; echo "<font class='fontSmall' color='yellow'>"; printText( "[Not Assigned]" ); echo "</font>"; } elseif ( $manifestList['shipCommanderid'] > 0 || $manifestList['shipPilotid'] > 0 ) { $crew = "SELECT crew.crewid, crew.firstName, crew.lastName, crew.rankid, rankid "; $crew.= "FROM sms_crew AS crew "; $crew.= "WHERE (crewType = 'active' OR crewType = 'awol') AND crew.crewid = '$manifestList[shipCommanderid]' ORDER BY "; $crew.= "crew.rankid ASC"; $crewResult = mysql_query( $crew ); $rankArray = "SELECT rankid FROM sms_ranks ORDER BY rankid ASC"; $rankArrayResult = mysql_query( $rankArray ); while( $myrow = mysql_fetch_array( $rankArrayResult ) ) { $rankList[] = $myrow['rankid']; } while( $player = mysql_fetch_assoc( $crewResult ) ) { extract( $player, EXTR_OVERWRITE ); if( in_array( $player['rankid'], $rankList ) ) { $crewRank = "SELECT rankShortName FROM sms_ranks WHERE rankid = '$player[rankid]'"; $crewRankResult = mysql_query( $crewRank ); } while( $playerRank = mysql_fetch_assoc( $crewRankResult ) ) { extract( $playerRank, EXTR_OVERWRITE ); echo "<font class='fontSmall'>"; echo printText( $playerRank['rankShortName'] . " " . $firstName . " " . $lastName ) . ""; } } echo "<br />"; $crew = "SELECT crew.crewid, crew.firstName, crew.lastName, crew.rankid, rankid "; $crew.= "FROM sms_crew AS crew "; $crew.= "WHERE (crewType = 'active' OR crewType = 'awol') AND crew.crewid = '$manifestList[shipPilotid]' ORDER BY "; $crew.= "crew.rankid ASC"; $crewResult = mysql_query( $crew ); $rankArray = "SELECT rankid FROM sms_ranks ORDER BY rankid ASC"; $rankArrayResult = mysql_query( $rankArray ); while( $myrow = mysql_fetch_array( $rankArrayResult ) ) { $rankList[] = $myrow['rankid']; } while( $player = mysql_fetch_assoc( $crewResult ) ) { extract( $player, EXTR_OVERWRITE ); if( in_array( $player['rankid'], $rankList ) ) { $crewRank = "SELECT rankShortName FROM sms_ranks WHERE rankid = '$player[rankid]'"; $crewRankResult = mysql_query( $crewRank ); } while( $playerRank = mysql_fetch_assoc( $crewRankResult ) ) { extract( $playerRank, EXTR_OVERWRITE ); echo printText( $playerRank['rankShortName'] . " " . $firstName . " " . $lastName ); echo "</font>"; } } } ?> </td> </tr> <tr colspn='4'> </tr> <? } } ?> </table> Can anyone help me fix this doubling problem? Link to comment Share on other sites More sharing options...
justsomeguy Posted July 1, 2008 Share Posted July 1, 2008 It's still the same solution. Keep track of the last ship you printed and if the current ship is the same one then either print it on the same line, or skip it, or whatever you're trying to do. $last_id = 0;$result = mysql_query(...);while ($row = mysql_fetch_assoc($result)){ if ($last_id == $row['id']) // same ship { } else // different ship { } $last_id = $row['id'];} Link to comment Share on other sites More sharing options...
wisper Posted July 1, 2008 Author Share Posted July 1, 2008 I don't understand any of that. I only want one entry per ship. Can you explain your code a little better for me. I have no clue what you want me to do with it or where to put it. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 1, 2008 Share Posted July 1, 2008 You're getting the shipid with each record. Have a variable where you store the last ID that you saw (that's $last_id). Start it at 0, which should match no ID, and set it to the current shipid at the end of the loop after you've printed the record. Each time you get a new record, before you print anything, compare the record's ship ID with $last_id and see if they are the same. If they are, then you can skip that record instead of printing it. You only want 1 row for each shipid, right? Link to comment Share on other sites More sharing options...
wisper Posted July 1, 2008 Author Share Posted July 1, 2008 Right thats all I want. So your saying I should put it at the end of the loop before it closes? If thats what your saying then I will try it. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 1, 2008 Share Posted July 1, 2008 The code I posted above is just an example loop. You start the variable at 0 before the loop, set it to the current ID at the end of the loop, and then you can put an if statement inside the loop to check it. Just make sure you assign the variable the current ID outside of the if statement (so that the variable always gets set regardless of if you print the record or not). Link to comment Share on other sites More sharing options...
wisper Posted July 2, 2008 Author Share Posted July 2, 2008 Thanks but I found a better way to do it. I changed ORDER BY to GROUP BY. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.