For an online game i've been working on part of it requires members to have certain items.
Normally i have no problem with this, but i'm stuck since some of the items you need 1 of 2 possible items.
There are 3 tables
need
CREATE TABLE `need` (
`ID` int(11) NOT NULL auto_increment,
`itemid` int(11) NOT NULL COMMENT 'ID of the item',
`materialid` int(11) NOT NULL COMMENT 'ID of the material',
PRIMARY KEY (`ID`),
KEY `itemid` (`itemid`,`materialid`),
KEY `materialid` (`materialid`),
CONSTRAINT `need_ibfk_1` FOREIGN KEY (`itemid`) REFERENCES `items` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `need_ibfk_2` FOREIGN KEY (`materialid`) REFERENCES `materials` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8 COMMENT='What materials are needed for what item'
materials
CREATE TABLE `materials` (
`ID` int(11) NOT NULL auto_increment COMMENT 'Id of the material',
`name` varchar(255) NOT NULL COMMENT 'The name of the Material',
`type` varchar(10) NOT NULL COMMENT 'The type of material',
`cost` int(11) NOT NULL COMMENT 'The cost of the material',
`url` varchar(255) NOT NULL COMMENT 'URL for the material',
`alttext` varchar(255) NOT NULL COMMENT 'Alttext for the material',
PRIMARY KEY (`ID`),
UNIQUE KEY `name` (`name`),
KEY `type` (`type`,`cost`),
KEY `url` (`url`,`alttext`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
materialstock
CREATE TABLE `materialstock` (
`ID` int(11) NOT NULL auto_increment,
`material` int(11) NOT NULL,
`factory` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `owner` (`material`,`factory`),
KEY `factory` (`factory`),
CONSTRAINT `materialstock_ibfk_2` FOREIGN KEY (`material`) REFERENCES `materials` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `materialstock_ibfk_3` FOREIGN KEY (`factory`) REFERENCES `factories` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='What materials are stocked at which factory'
So far i have got
function smarty_insert_order()
{
global $smarty;
if(isset($_POST['Submit2'])) {
$item = clean($_POST['itemno']);
$packing = clean($_POST['packing']);
$stuffing = clean($_POST['stuffing']);
$amount = clean($_POST['quantity']);
$fact = clean($_GET['factory']);
$ware = clean($_POST['warehouse']);
$msg = '';
$pack = $mat = $stuff = false;
if($packing == '') {
$msg .= 'No packing materials';
$pack = false;
} else {
$pack = true;
$cost = 15 + rand(1,5);
}
// Check what materials are needed to create the item
$sql = "SELECT n.materialid, m.type, ma.ID
FROM need AS n
LEFT JOIN materials AS m ON n.materialid = m.ID
LEFT JOIN materialstock AS ma ON ma.material = m.ID
WHERE n.itemid =".$item;
$result = mysql_query($sql) or die(sql_error($sql));
while($row = mysql_fetch_array($result)) {
if($row['ID'] == '' && $row['type'] <> 'Packing') {
$msg .= '<strong><br/>Not enough materials</strong>';
$mat = false;
} else {
$mat = true;
}
if($row['materialid'] == $stuffing) {
$stuff = true;
$cost = $cost + rand(7, 12);
}
}
//echo "Packing: ".$pack." Materials: ".$mat." Stuffing: ".$stuff."<br/>";
if($pack == true && $mat == true && $stuff == true) {
// Got all items, insert into the stock tabel
$sql = "INSERT INTO stockf (itemid, cost, amount, created, owner, factoryid, completed) VALUES
(".$item.", ".$cost.", ".$amount.", 0, ".clean($_COOKIE['toptycoons']['i']).", ".$fact.", 0)";
mysql_query($sql) or die(sql_error($sql));
// Add which warehouse they're going to
$sql = "INSERT INTO suppliesw (stock, warehouse) VALUES
(".mysql_insert_id().", ".$ware.")";
mysql_query($sql) or die(sql_error($sql));
// Clear cache and go back to the factories page
$smarty->clear_cache('factory.tpl', $_COOKIE['toptycoons']['i']."-".$fact);
$smarty->clear_cache('orderitem-mat.tpl', $_COOKIE['toptycoons']['i']);
header("Location: factory.php?factory=".$fact);
exit();
} else {
$msg = "Not enough stuff";
}
return $msg;
}
}
Which works for anything that has stuffing, but anything that doesn't it fails.
I've been trying to get this working for 2 days now but no luck
All items needs one of the materials of type "Packing"
Only certain items need one of the materials of type "Stuffing"
Hopefully someone can help me with this.
I also need to get the id of the items (except for packing and stuffing) in order to delete them
