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 $resupplyRate) -1

$resupply = $resuplyRate- 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:
$resupply = $resupplyRate(3) - 1 (which would set $resupply 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 (table is called Availability); this table has the same unique ID (unit_ID) as the units table, but would be setting the field Resupply to be used in adjusting the resupply rate after each game.
Im thinking of having this run as a cron job every wednesday night at midnight, or possibly 2 times a week. This way our resupply numbers for every unit that uses this calculation will dynamically change based on the number of units being used throughout the entire mod.

Thanks for any help.

[McKaulick]

Hello Draygon,

You can do something like this:

select count(*) as cnt_squads, unit_id from squads GROUP BY unit_id

You will for result something like this:

cnt_squads        unit_id
    100                 1
     30                  2
     20                  4

Then just loop the array and do whatever you need to do with the data.

Let me know if this could work.

[Draygon]

McKaulick, thanks for your post.  I have taken it and created a script (not complete yet as you will see) to try and do what I need done.  I think I have a good idea of how to do it, I am sure I have syntax errors in the code below.  I now need to figure out how to exclude about 8 units that this code should not affect.  Not even possitive that I am actually going through the data pulled by the query correctly.  Any further help is greatly appreciated.


<?php
//Connect to the database and select the right database
  $warcp = mysql_connect('server', 'database', 'password');
  @mysql_select_db('database', $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'");
       echo "The new updated resupply numbers for units that changed are:'$unitCount[unit_id]'$resupply";
    } elseif($currentResupply['resupply'] == $resupply) {
       echo "The units that did not change are: '$currentResupply[unit_id]'";
    }
       
?>

[McKaulick]

Many way to do that,

Shorter would be 

SELECT COUNT(*) AS unit_count, unit_id FROM squads WHERE unit_id in (1,2,3, etc) )GROUP BY unit_id ORDER BY unit_id ASC

[Draygon]

That would exclude those units in the (1,2,3, etc...) ?

How does the rest of my script look, syntax and functional wise?

Appreciate this help.

[McKaulick]

No the IDs in the parenthesis are the one you need.

Also, I would put my database connection script on an external file. You might want to create a class to manage all your database query. Google Search PHP mysql class or something, there might be some script available online.

Good luck.

Patrice

[Draygon]

Oh yes, the db connection I was going to include in another script and just do a include statement.

As far as the rest of the script goes, is the syntax ok?  Specifically the if/elseif statements?

[Draygon]

I also found an error with my math calculation I was calling an array but not specifying what in that array to use, here is the corrected version.

$resupply = (($unitCount['unit_count'])/($playerCounts['COUNT(*)']))-1;