Jump to content

wisper

Members
  • Posts

    12
  • Joined

  • Last visited

wisper's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. Well there are a total of 14 ranks (3 NCO, 4 JO, 3 SO, 4 FO). the reason I use two positions is there are two positons some one is capable of having. Though now that I think about it is probably not necessary as not every one has two positions. And the reason I am looking at the same position in each is that over that number I don't want to know about them.
  2. What do you mean by repeative? I am new to all this plus this is someone elses DB structure.
  3. Ok I am trying to count up some objects in my database. More specifically how may NCO, Junior Officers, Senior Officers, and Flag Rank Officers we have. The problem I am having is getting the numbers to display on table. I have checked the SQL queries and they work perfect. Here is the code. Can someone help me figure out whats going on? <? /* do a count for all Non-commissioned Officers */ $nco = "SELECT COUNT( rankid ) FROM sms_crew WHERE crewType = 'active' "; $nco = "AND rankid > 24 AND rankid < 30 AND (positionid < 30 OR positionid2 < 30) "; $ncoResult = mysql_query( $nco ); $countNCO = mysql_fetch_array( $ncoResult ); /* do a count for all Junior Officers */ $jo = "SELECT count( rankid ) FROM sms_crew WHERE crewType = 'active' "; $jo = "AND rankid > 16 AND rankid < 25 AND (positionid < 30 OR positionid2 < 30) "; $joResult = mysql_query( $jo ); $countJO = mysql_fetch_array( $joResult ); /* do a count for all Senior Officers */ $so = "SELECT count( rankid ) FROM sms_crew WHERE crewType = 'active' "; $so = "AND rankid > 8 AND rankid < 17 AND (positionid < 30 OR positionid2 < 30) "; $soResult = mysql_query( $so ); $countSO = mysql_fetch_array( $soResult ); /* do a count for all Flag Officers */ $fo = "SELECT count( rankid ) FROM sms_crew WHERE crewType = 'active' "; $fo = "AND rankid = 1 AND rankid < 9 AND (positionid < 30 OR positionid2 < 30) "; $foResult = mysql_query( $fo ); $countFO = mysql_fetch_array( $foResult ); $activeNCO = $countNCO['0']; $activeJO = $countJO['0']; $activeSO = $countSO['0']; $activeFO = $countFO['0']; $totalActiveOfficers = $activeFO + $activeSO + $activeJO + $activeNCO; ?> <table> <tr> <td colspan="2"><span class="fontNormal"><b>Defence Force Staffing by Rank</b></span></td> </tr> <tr> <td># of NCO's:</td> <td width="10%"></td> <td><?=$activeNCO ?></td> <tr> </tr> <td># of Junior Officers:</td> <td width="10%"></td> <td><?=$activeJO ?></td> <tr> </tr> <td># of Senior Officers:</td> <td width="10%"></td> <td><?=$activeSO ?></td> <tr> </tr> <td># of Flag Officers:</td> <td width="10%"></td> <td><?=$activeFO ?></td> </tr> </tr> <td><b>Total</b></td> <td width="10%"></td> <td><?=$totalActiveOfficers ?></td> </tr> </table>
  4. Thanks but I found a better way to do it. I changed ORDER BY to GROUP BY.
  5. 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.
  6. 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.
  7. 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?
  8. 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.
  9. if I add the print_r i get these Have 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.
  10. No this is the 'IF' I mean if ( $manifestList['shipPilotid'] == 0 ) { the other one works fine. It is how its intended to work.
  11. 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><? } ?>
×
×
  • Create New...