Problems with displaying Count

[wisper]

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>

[jhecht]

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.

[wisper]

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.

[jhecht]

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.

[wisper]

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.

[jhecht]

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.