How to build this query?

[Chris]

Each player has 4 army slots. Each slot is described by unit type (tX) and unit quantity (qX). There are 10 unit types. There are no restrictions on slot types (so a player can have all slots filled with unit type 1 for example).

Table consists of: t1,q1,t2,q2,t3,q3,t4,q4

How to make a query to count how many units of each type are in the game total (it does not need to be very fast since it is a statistical tool, but it would be nice if it wasn't crawling slow)?

[dbest]

Can you not create a new table with columns as such: type, quantity (unit type id, unit quantity id)

If the table can be recreated, then you could run a simple query:
SELECT type, SUM(quantity) FROM table_name GROUP BY type

If the table cannot be modified, then I will need to think a bit more in detail..

[Chris]

The table can not be modified.

[dbest]

Should have known it wouldnt have been so easy.

I suppose its beyond my knowledge.

[saljutin]

why to make it hard and think & complicate so much?
why not only select it and then use while sentance like:

Code: [Select]

$units = array(0,0,0,0,0,0,0,0,0,0,0);
$info = mysql_query("SELECT `t1`,`q1`,`t2`,`q2`,`t3`,`q3`,`t4`,`q4` FROM `table`");
while ($data = mysql_fetch_array($info)) {
for ($i=0; $i<8; $i+=2) {
$units[$data[$i]] += $data[$i+1];
}
}

for ($j=1; $j<count($units); $j++) {
echo "Unit ".$j.": ".$units[$j]."<br />";
}
1. we make array units that would store sum of every units ... so for 10 units we need 11 0, because of array[0] is not an unit
2. we do query with while sentance
3. we use FOR loop to go through these units, but it increments by 2, so we can use that double array
    $i<8 this number 8 is number of our "units"x2, so if tX,qX => x=10 then this number is 20
4. we add sum of units to that array
4. and at the end we use some for loop to echo all units that are in game

hope it helps...sometimes is better to select all table then to do calculations in big table, especially if this is only for you to see (so only 1 user will run this query from time to time), but you can always add some timing function to determine how long does this code "work"

[Chris]

Thanks saljutin, I was thinking about something similar but my version was at least 4 times longer


Another question, the question above was simplified, in reality I have 2 slots that are predefined (slot 0 always hold unit type 1 and slot 1 holds unit type 2 (these do not have tX field), the rest of the slots are customised by player). I wonder if I could add it without changing the code too much.

Something like this:

Code: [Select]

$info = mysql_query("SELECT 1,`q0`,2,`q1`,`t2`,`q2`,`t3`,`q3`,`t4`,`q4` FROM `table`");
while ($data = mysql_fetch_array($info)) {
for ($i=0; $i<12; $i+=2) {
$units[$data[$i]] += $data[$i+1];
}
}Haven't tested the code above, but I think it won't work?
In short, looking for a way to return a constant from query (i think there is a trivial solution to this, but I don't know it myself .

[saljutin]

I dont actually get it...
is that SLOT0 = 1
and slot1 = 2
in table or not?
can you copy paste the whole table structure?
one solution is that you start for loop later on, and use:
$units[1] += $data[0];
$units[2] += $data[1];
before for loop

[Chris]

Table structure: q1,q2,t3,q3,t4,q4 (no t1, t2 since these are considered constant)

Yes, I know I could make a dirty hack before for loop, but I also hoped to learn something new regarding SQL (after finishing several games it is the time to learn the basics at last, isn't it? ) So, I wonder if it could be done on SQL side.

[saljutin]

AH thats table structure

$info = mysql_query("SELECT '1',`q0`,'2',`q1`,`t2`,`q2`,`t3`,`q3`,`t4`,`q4` FROM `table`");

this should work...try it if it doesn't then try

$info = mysql_query("SELECT '1' AS `t0`,`q0`,'2' AS `t1`,`q1`,`t2`,`q2`,`t3`,`q3`,`t4`,`q4` FROM `table`");

[Chris]

Code: [Select]

$info = mysql_query("SELECT '1' AS `t0`,`q0`,'2' AS `t1`,`q1`,`t2`,`q2`,`t3`,`q3`,`t4`,`q4` FROM `table`"); This worked

And all this so I can know that Commandos are better than Space Rangers and needs to be nerfed

[Nox]

Don't know how seriously you meant that, but ...careful
Frequency of usage doesn't immediately imply being better. I don't know how complex your game is, but...

It's often the way that people don't use some unit and then a skilled and resourceful player comes and use that unit so well that the rest of players start using it as well. Many imbalances will be discovered as the time goes by, combinations and usages of unit neither devs nor players thought about etc.

Speaking mainly based on experience from dawn of war 2, which I play since beta, but I think these ideas can apply everywhere
The race that seemed the least powerful, heroes that weren't popular at the beginning proved totaly broken and overpowered after some time, when people learned to used them or just stopped overlooking them

You're probably aware of that, just felt the need, 'repetition is the mother of wisdom' as the saying goes...

[Chris]

Don't know how seriously you meant that, but ...careful
Frequency of usage doesn't immediately imply being better. I don't know how complex your game is, but...

It's often the way that people don't use some unit and then a skilled and resourceful player comes and use that unit so well that the rest of players start using it as well. Many imbalances will be discovered as the time goes by, combinations and usages of unit neither devs nor players thought about etc.

Speaking mainly based on experience from dawn of war 2, which I play since beta, but I think these ideas can apply everywhere
The race that seemed the least powerful, heroes that weren't popular at the beginning proved totaly broken and overpowered after some time, when people learned to used them or just stopped overlooking them

You're probably aware of that, just felt the need, 'repetition is the mother of wisdom' as the saying goes...

This game is too simple for this to happen. There are just 4-7 attributes and units are quite similar. In addition, being unbalanced is not a big deal since change of units is reasonably cheap. But thanks for reminding