Some Assistance Please

[Ryuujin]

I am creating this text-based 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:

<?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`.

--
-- 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);

--
-- 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

$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

[btherl]

Here's the query reformatted:

 

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

 

That looks correct.. you need to unitID matching line.  So the problem is probably that your conditions are somehow removing the Pikemen.  Do you only have a single line in kU?  If so, then you need a left join to have the Pikemen displayed when your kingdom has no pikemen.  It'll look like this:

 

SELECT * FROM
  `kingdomUnits` AS kU,
  `kingdoms` AS k
  LEFT JOIN `militaryUnits` AS mU ON (mU.`unitID` = kU.`unitID`)
WHERE mU.`unitLevel` <= '$KingdomLevel'
AND k.`id` = '$_SESSION[userid]'
AND kU.`kingdomID` = '$_SESSION[userid]'
ORDER BY mU.`unitCost` ASC

 

This will give you nulls in kU for the row in mU which has no matches.

 

It's a bit odd that you are selecting from kingdoms here.. it makes more sense to me to do 2 queries, one from kingdoms and a second query from kU joined with mU.