I wrote the following that works perfectly and gives the exact results I am looking for. What I am struggling with is doing the same exact thing in 100% Mysql. I have struggled for many hours and tried many query variations with no luck. Any help appreciated.
[php]<?php
try
{
$sql = “SELECT backsplash, carpet, tile, wood FROM lot WHERE lot_id=10”;
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
$sql = "SELECT
material_tracking.lot_id,
material_tracking_to_material.material_type_id
FROM
material_tracking
INNER JOIN material_tracking_to_material ON material_tracking_to_material.material_tracking_id = material_tracking.material_tracking_id
WHERE
material_tracking.lot_id = 10";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result2 = $stmt->fetchAll();
}
catch (PDOException $e)
{
include_once(’./config/pdo_catch_error.php’);
}
echo “<select name=“material_type”>”;
foreach ($result as $row)
{
if ($row[‘backsplash’] == 1)
{
//Is backsplash in material tracking? If not: Show Backsplash in Dropdown
if ($result2[0][‘material_type_id’] != 4)
{
echo "<option value=“4”>Backsplash ";
}
}
if ($row['carpet'] == 1)
{
if ($result2[0]['material_type_id'] != 1)
{
echo "<option value=\"1\">Carpet</option> ";
}
}
if ($row['tile'] == 1)
{
if ($result2[0]['material_type_id'] != 2)
{
echo "<option value=\"2\">Tile</option> ";
}
}
if ($row['wood'] == 1)
{
if ($result2[0]['material_type_id'] != 3)
{
echo "<option value=\"3\">Wood</option> ";
}
}
echo "</select>";
} // End Foreach
?>[/php]
LOT DDL
CREATE TABLE lot (
lot_id int(11) NOT NULL AUTO_INCREMENT,
block_id int(11) DEFAULT NULL,
lot_number varchar(50) DEFAULT NULL,
lot_type_id int(1) DEFAULT NULL,
address_id varchar(255) DEFAULT NULL,
model_id int(11) DEFAULT NULL,
active tinyint(1) NOT NULL,
lot_street_address varchar(255) DEFAULT NULL COMMENT ‘This field should be removed later for address table ID’,
slab_date date DEFAULT NULL,
dried_in_date date DEFAULT NULL,
order_by_date date DEFAULT NULL,
confirm_date tinyint(1) DEFAULT ‘0’,
reminder_date date DEFAULT NULL,
backsplash tinyint(1) DEFAULT NULL,
carpet tinyint(1) DEFAULT NULL,
tile tinyint(1) DEFAULT NULL,
wood tinyint(1) DEFAULT NULL,
PRIMARY KEY (lot_id),
UNIQUE KEY lot_id (lot_id,block_id),
KEY block_id (block_id),
KEY model_id (model_id),
KEY lot_ibfk_2 (lot_type_id),
CONSTRAINT lots_ibfk_1 FOREIGN KEY (block_id) REFERENCES block (block_id),
CONSTRAINT lot_ibfk_1 FOREIGN KEY (model_id) REFERENCES model (model_id),
CONSTRAINT lot_ibfk_2 FOREIGN KEY (lot_type_id) REFERENCES lot_type (lot_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
Material Tracking DDL
CREATE TABLE material_tracking (
material_tracking_id int(11) NOT NULL AUTO_INCREMENT,
lot_id int(11) DEFAULT NULL,
warehouse_id int(11) DEFAULT NULL,
order_date date DEFAULT NULL,
complete_date date DEFAULT NULL COMMENT ‘Actually receive_date’,
PRIMARY KEY (material_tracking_id),
KEY lot_id (lot_id),
KEY warehouse_id (warehouse_id),
CONSTRAINT material_tracking_ibfk_1 FOREIGN KEY (lot_id) REFERENCES lot (lot_id),
CONSTRAINT material_tracking_ibfk_2 FOREIGN KEY (warehouse_id) REFERENCES warehouse (warehouse_id)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
Material Tracking To Material DDL
CREATE TABLE material_tracking_to_material (
material_tracking_id int(11) NOT NULL,
material_type_id int(11) NOT NULL,
KEY material_tracking_id (material_tracking_id),
KEY material_type_id (material_type_id),
CONSTRAINT material_tracking_to_material_ibfk_1 FOREIGN KEY (material_tracking_id) REFERENCES material_tracking (material_tracking_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT material_tracking_to_material_ibfk_2 FOREIGN KEY (material_type_id) REFERENCES material_type (material_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Material Type DDL
CREATE TABLE material_type (
material_type_id int(11) NOT NULL AUTO_INCREMENT,
material_type_description varchar(255) DEFAULT NULL,
PRIMARY KEY (material_type_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;