Some Assistance Please

[Waizujin]

I am creating this game and I have this code which basically displays all the units in the game with the fields for, Unit Name, Cost, Offense, Defense, Citizens Required to Recruit, Amount already owned, and amount purchasable.
Anyway here is the PHP code and such:

Code: [Select]

<?php
} else {
$grabUnits = $db->execute("SELECT * FROM `militaryUnits` AS mU, `kingdomUnits` AS kU, `kingdoms` AS k WHERE mU.`unitLevel` <= '$KingdomLevel' AND k.`id` = '$_SESSION[userid]' AND kU.`kingdomID` = '$_SESSION[userid]' ORDER BY mU.`unitCost` ASC") or die(mysql_error());
?>
<h3>Kingdom Military</h3>
Here you can manage, recruit, and fire various military units.<br />
<br />
<table width="100%" style="border: 1px solid black;">
<tr>
<th colspan="7">Recruit Military Units</th>
</tr>
<tr>
<th>Military Unit</th><th>Cost</th><th>Offense</th><th>Defense</th><th>Req. Citizens</th><th>Owned</th><th>Purchasable</th>
</tr>
<?php
while($Unit = $grabUnits->fetchrow()) {
$unitsBuyable = $Unit['kingdomFunds'] / $Unit['unitCost'];
?>
<tr style="text-align: center;">
<td><?php echo $Unit['unitName']; ?></td><td><?php echo $Unit['unitCost']; ?></td><td><?php echo $Unit['unitOffense']; ?></td><td><?php echo $Unit['unitDefense']; ?></td><td><?php echo $Unit['unitCitizens']; ?></td><td><?php echo $Unit['amount']; ?></td><td><?php echo $unitsBuyable; ?></td>
</tr>
<tr style="text-align: center;">
<td colspan="7">
<?php echo $Unit['unitDescription']; ?><br />
<form method="post" action="military.php">
<input type="text" size="4" name="<?php echo $Unit['unitName']; ?>" />
<input name="buyUnit" type="submit" value="Buy <?php echo $Unit['unitName']; ?>(s)" />
</form>
<br />
</td>
</tr>
<?php
}
?>

Here is my database structure for `militaryunits` and `kingdomunits`.

Code: [Select]

--
-- Table structure for table `militaryunits`
--

CREATE TABLE `militaryunits` (
  `unitID` int(11) NOT NULL auto_increment,
  `unitLevel` int(11) NOT NULL,
  `unitName` varchar(50) NOT NULL,
  `unitDescription` longtext NOT NULL,
  `unitCost` int(11) NOT NULL,
  `unitOffense` int(11) NOT NULL,
  `unitDefense` int(11) NOT NULL,
  `unitCitizens` int(11) NOT NULL,
  PRIMARY KEY  (`unitID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `militaryunits`
--

INSERT INTO `militaryunits` (`unitID`, `unitLevel`, `unitName`, `unitDescription`, `unitCost`, `unitOffense`, `unitDefense`, `unitCitizens`) VALUES
(1, 1, 'Guard', 'A simple unit that offers a small amount of offense but a bit more in defense.', 100, 1, 2, 1),
(2, 1, 'Pikemen', 'A simple unit, but effected in Offense.', 250, 3, 1, 1);


Code: [Select]

--
-- Table structure for table `kingdomunits`
--

CREATE TABLE `kingdomunits` (
  `id` int(11) NOT NULL auto_increment,
  `kingdomID` int(11) NOT NULL,
  `unitID` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `kingdomunits`
--

INSERT INTO `kingdomunits` (`id`, `kingdomID`, `unitID`, `amount`) VALUES
(1, 1, 1, 4);

Okay, how here is the problem. It display's both units but the amount owned is set to 4 on both. It should only be set to 4 on Guards, not Pikemen.

Does anyone have any idea as to how I can code it to display the amount owned correctly.

I tried using

Code: [Select]

$grabUnits = $db->execute("SELECT * FROM `militaryUnits` AS mU, `kingdomUnits` AS kU, `kingdoms` AS k WHERE mU.`unitLevel` <= '$KingdomLevel' AND k.`id` = '$_SESSION[userid]' AND kU.`kingdomID` = '$_SESSION[userid]' AND mU.`unitID` = kU.`unitID ORDER BY mU.`unitCost` ASC") or die(mysql_error());But that then only display's the Guards, not the Pikemen.

So, any ideas?

Sincerely,
-Marcus

[Zeggy]

I'm guessing `kingdoms` is where you have all your player data, so why include it in the query? You should have all (or most) player data already.

I can't see what the problem is, but I'd recommend having a query that get all of the player's units from kingdomunits, then using JOIN to get the unit data that you need from militaryunits. I don't really understand how your query is built up, and I have a feeling it might be the problem.

[Waizujin]

kingdoms is where I have the kingdom data, players is where I have the player data.

The query is setup to do a lot of things in 1 query thus making the load time much faster and less stress on the query.
The query works fine, I just need it to grab the amount.
And your suggestion is exactly what I am trying to avoid, I want the game to run fast and the more queries I run, the slower it becomes.

SOLVED! I still had to do one more query but I guess it had to be done. Thanks Zeggy!

[Zeggy]

lol, two slow, working queries beats one broken query that loads faster any day

what I meant was, separate the queries until they work the way you want it to, and then combine them. optimization shouldn't be your main priority right now, there's plenty of time for that when your game is complete.

Edit: ah, okay, I'm glad you got it solved
what was the problem?

[saljutin]

well optimization after everything is done can be pain in the a**. I rather optimize at beginning...
back to question:
if you make such complex queries which does not work then you should go to phpmyadmin and try them there...you will see the whole selection and it is easier to solve problem like that

[Zeggy]

But there is always a better way of optimizing

I think optimization should be an ongoing process, not just something you complete once like another game feature.


Edit: I get what you're saying, you don't want to start out with some horrible time-consuming processes, since it will be a monster to optimize later on. Even so, I find that as a PHP coder of many years, I still learn new things every day, and new ways to code the same things. Eventually you'll probably want to re-do a lot of your code.

[saljutin]

sometimes optimization is more time consuming then writing the whole page from scratch...
it depends on number of queries, also optimization in PHP is mainly done on making as low queries as you need, tables should be well constructed and indexed.
also you are correct, if I check my previous projects (the first ones) they are really lousy constructed (not only 1mio queries but PHP too).
Also people use tables to add weapons,units (stats, description,...) but I have found out that is better to use multidimensional arrays. It is faster and once you found out how to effectively use them they rule

Also the biggest optimization project in my history was making a map 9x9 squares local, and 1001x1001 global. first it was like 30 seconds loading time, at the end that time was really low (Script execution 0.03), this new optimized map is selecting 11x11 area and adding borders to user territory as well as everything else

[Zeggy]

Hmm, I've thought about using arrays for storing info that doesn't change much over time - building info, unit stats, etc.

Eventually I decided against that because making any changes means the files will need to be edited manually. I've thought about it some more, and was wondering, what if you used a separate files for each unit?

Eg. weapon_sword.php, weapon_knife.php, etc. And in each file you would only have one array. With this solution you could build an admin panel easily, which can edit the details and add new items. As far as I can tell, there's not much difference, although it will probably be very inefficient if you need to list all units.

Either way, a database is simpler and easier to work with for me. Maybe you could write a short topic on handling static data stored in arrays/files? I think it would be very useful!

[saljutin]

well I have only 1 file which have all arrays needed stored there. so when you modify you just upload it and it is done.
also another solution can be some txt document in which you can store data and then parse it. dont know how fast can this parsing process be...but it would be good because you can have some admin panel where you can change that txt file...