Jump to content

Problems with displaying Count


wisper
 Share

Recommended Posts

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>

Link to comment
Share on other sites

you're not using a concactenation operator in your strings.

<?	/* 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>

Not telling you how to do whatever it is you're doing, but perhaps thinking about redoing your DB structure would be a good idea, because from what im seeing you're doing things that are very reptitive and really don't need to be happening.

Link to comment
Share on other sites

Not telling you how to do whatever it is you're doing, but perhaps thinking about redoing your DB structure would be a good idea, because from what im seeing you're doing things that are very reptitive and really don't need to be happening.
What do you mean by repeative? I am new to all this plus this is someone elses DB structure.
Link to comment
Share on other sites

Well in MySQL the entire goal is to get rid of repetative natures in your database. What if you want to be able to more than 2 ranks(illogical, as i dont think you'd ever need more than 2 ranks, but you never know), or why have to check 2 position id's? That's not necessary.My structure, from what I can see of your table, would be

CREATE TABLE sms_crew(	crew_id int unique not null auto_increment,	crew_firstname varchar(40) not null,	crew_lastname varchar(50) not null,	crew_rank_id int not null,	crew_position_id int not null	crew_active tinyint not null default 1);CREATE TABLE ranks(	rank_id int unique not null auto_increment,	rank_title varchar(50) not null);CREATE TABLE positions(	position_id int unqiue not null auto_increment,	position_name varchar(50) not null);INSERT INTO ranks(rank_title) VALUES ('Non-Comissioned'),('Junior'),('Senior'),('Flag');/*	non-comissioned will have an ID of 1,	junior officers will have an ID of 2,	senior officers will have an ID of 3,	Flag officers will have an ID of 4*///I don't know what the positions are for, since all the positions just reference 30 as a value...//I'm a junior officer, so i'll use a rankd_id of 2, and a flag officer with rank of 4INSERT INTO sms_crew(crew_firstname,crew_lastname,crew_rank_id,crew_position_id,crew_active) VALUES ('Jhecht','Falcon',2,(insert what the position id is supposed to be),1),('Jhecht','Falcon',4,(insert position id),1)//The crew_active column is either supposed to be 1 for active, or 0 for inactive.

I'll leave you to figure out the SELECT DISTINCT count() syntax. If you don't like the DB i made, then you can just leave it alone.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Well then you could simply insert those ranks into the db, and if you learn enough about SQL you can realize that you can give more than one rank/title through the db set up i gave you.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...