Help with SELECT COUNT array

[Draygon]

Im new to php programming but am slowly learning (have had some assistance with that from members here before  ) but I am having problem wrapping my head around some code I need to write. Sorry for the long post but I want to make sure I make it clear what Im trying to do.

 

The background on this is that it is for a avalability system for units in a persistent mod for a game called Company of Heroes. This limits the amount (number) of each certain unit you can have in your company. Its a simple math equation that looks like this.

 

(Total number of Units) / (total number of players) = average number of units in each company (we'll call this $companyUnits) -1

 

$availabilty = $companyUnits - 1;

 

As an example if I have 453 shermans (total number of all shermans used) and I have 123 players (total number of players) then 453/123 = 3.6 ($companyUnits rounded down)

 

so then:

$availability = $companyUnits(3) - 1 (which would set $availability to 2).

 

In my database I have a table called Units, in this table each unit has a unique ID. I have another table called squads which uses the unique ID of the units to keep track of the units in each company. What I want to do is go through and get the total amount of each type of unit used throughout everyones companies so that I can adjust the availability numbers. There are currently 125 unique units.

 

I know I can do:

 

PHP Code:

SELECT COUNT FROM squads WHERE unit_id=<id>; 

 

 

 

I know that will get me the total count for the unit that I put the ID in for. The question I have is can I create an array that will go through and do all of the units in one statement rather than doing 125 different statements to get the values. After I have the numbers for each I then need to have it SET the column for that units availability in another table called Availability; this table has the same unique ID (unit_ID) as the units table.

 

Thanks for any help.

 

[sasa]

yes

use GRUP BY unit_id in your SQL

[RussellReal]

sasa beat me to it

 

SELECT COUNT(unit_id) FROM `squads` GROUP BY `unit_type`

[sasa]

sasa beat me to it

 

SELECT COUNT(unit_id) FROM `squads` GROUP BY `unit_type`

you want to select unit_type too in this query

[Draygon]

Thanks for the help guys, I do have some unit types but there are only about 6 unit types, but I need to know the exact total number of all units (each individual unit) used throughout everyones companies.

Total number of riflemen, shermans, knights cross holders, etc...

 

So thats why I was thinking of using the SELECT COUNT statement, however I dont want to have to do 125 different statements to get the same results when I know that there is a way to do it in one statement, maybe the statement you guys suggested is what I need, and Im just not understanding, as I said in the first post I am new to php had this pretty much tossed in my lap to work on.

[sasa]

post your database (tables) structure

[Draygon]

Ok here is my squads table:

 

Field Type  Null  Default  Comments

squad_id                 int(11) No   

unit_id                 int(11) Yes  NULL 

vet                         decimal(11,4)Yes  NULL 

platoon_id                int(11) Yes  NULL 

section_id                int(11) Yes  NULL 

upgrades                 text Yes  NULL

 

Here is my units table:

Field      Type      Null  Default  Comments

id               int(11) No   

internalName  varchar(30) Yes  NULL 

CONSTNAME text Yes  NULL 

population       int(11) Yes  NULL 

mp               int(11) Yes  NULL 

mu               int(11) Yes  NULL 

fuel               int(11) Yes  NULL 

max               int(11) Yes  NULL 

company_max    int(11) Yes  NULL 

resupply       int(11) Yes  NULL 

turnlength         int(11) Yes  NULL 

type          text Yes  NULL 

restrictions_id int(11) Yes  NULL 

Vet1 text Yes  NULL 

Vet2 text Yes  NULL 

Vet3 text Yes  NULL 

Vet4 text Yes  NULL 

Vet5 text Yes  NULL 

slots          int(11) No  0 

call_modifier text Yes  NULL 

is_halftrack int(11) No  0 

unit_replace int(11) No  0

 

 

[kkubek]

How about

 

select unit_id,count(*)

from squads

group by unit_id

[Draygon]

Thanks for the help so far everyone. I have the actual query done that I need to count all of the units throughout all companies.  I got the query done that counting the number of actual players we have playing right now too.  So now I just need to figure out how to put them into arrays and then do the simple math equation.

 

Here are the queries that Ive got:

Unit count code:

SELECT COUNT(*) AS unit_count, unit_id FROM squads GROUP BY unit_id ORDER by unit_id ASC;

Player count code:

SELECT COUNT(*) FROM players;

 

so Im pretty sure that this is how the result should go:

$result = mysql_query(SELECT COUNT(*) AS unit_count, unit_id FROM squads GROUP BY unit_id ORDER by unit_id ASC);

I can then put that result into an array doing this right ?

$unitcount = mysql_fetch_assoc($result);

that will allow me to then only use the unit count by doing $row['unit_count'] to be able to use it.

 

So then I can get the player count like such:

$playercount = mysql_query(SELECT COUNT(*) FROM players);

 

 

 

So then with all of that I could then do this to do the math right?

$resupply = (($row['unit_id'])/($playercount))-1;

 

Assuming that I have all of that correct (pretty sure there are syntax errors in there) I need to now work on the code to update a table with this data.  For each unit I need to post into the correct place in the table (table is named Availability) this table has the columns that I need to populate in that are resupply (which is calculated and stored as $resupply) and the unit that it corresponds to (unit_id).

 

Appreciate any corrections to my above code and any help offered to work with the updating of the tables.

[Draygon]

Oops I made a mistake on the math and didnt put the right variables in there, so this should be the correct math.

$resupply = (($unitcount['unit_count'])/($playercount))-1;

 

[Draygon]

Ok piecing it all together here is what I have so far.  I know there are syntax errors and if someone can point them out that would be great.

 

Assuming this works I need to now figure out how to go through and make excempt about 7 Unit IDs that this wont applie to, and then print it out to a web page for the dev team to see.

 

<?php
//Connect to the database and select the right database
  $warcp = mysql_connect('p50mysql269.secureserver.net', 'omgdb2', 'oHJ2oju3OJM+l');
  @mysql_select_db('omgdb2', $warcp);
//Query the database to get the total count for all the units used in the entire mod, also put the results 
//of the query into a array  
  $result = mysql_query("SELECT COUNT(*) AS unit_count, unit_id FROM squads GROUP BY unit_id ORDER BY unit_id ASC");
  $unitCount = mysql_fetch_assoc($result);
//Query the database to get the total number of players playing the mod currently
  $playerCount = mysql_query("SELECT COUNT(*) FROM players");
  $playerCounts = mysql_fetch_assoc($playerCount);
//do the math to calculate each units new resupply per game number.  This is the number that will be updated
//in the database.  
  $resupply = (($unitCount['unit_count'])/($playerCounts))-1;
  
//Run a query of the current numbers in the Availability table, so we can compare these to the newly calculated numbers
$cResupply = mysql_query("SELECT resupply AS resupply, unit_id FROM Availability GROUP BY unit_id ORDER BY unit_id ASC");
//put the results into an array
$currentResupply = mysql_fetch_assoc($cResupply);
//Run an if statement that will compare the the old numbers with the new numbers to see if we need to update or not
    if ($currentResupply['resupply'] != $resupply) {
       mysql_query("UPDATE Availability SET resupply = '$resupply'");
?>

 

Sorry for all the posts just trying to get the best help I can while I work on this.