I have a table that is supposed to be pulling information from two others. I cannot get them to mesh.'
Here's the building blocks of the first table:
CREATE TABLE `CallsToDispatch`.`Techs` (
`IdNum` bigint(4) NOT NULL AUTO_INCREMENT,
`TechNum` bigint NOT NULL,
`FirstName` varchar(20) NOT NULL,
`LastName` varchar(20) NOT NULL,
`SystemNum` varchar(11) NOT NULL,
`Supervisor` int(3) NOT NULL,
PRIMARY KEY (`TechNum`),
UNIQUE `Tech` USING BTREE (TechNum)
) ENGINE=`InnoDB` COMMENT='';
And the second table:
CREATE TABLE `CallsToDispatch`.`Supervisors` (
`SystemNum` varchar(11) NOT NULL,
`System` varchar(20) NOT NULL,
`LongSystem` varchar(40) NOT NULL,
`Contractor` varchar(4),
PRIMARY KEY (`SystemNum`),
CONSTRAINT `LookupSys` FOREIGN KEY (`SystemNum`) REFERENCES `CallsToDispatch`.`Techs` (`SystemNum`) ON UPDATE CASCADE ON DELETE NO ACTION,
UNIQUE `SysIndex` USING BTREE (SystemNum)
) ENGINE=`InnoDB` COMMENT='';
And the third:
CREATE TABLE `CallsToDispatch`.`Systems` (
`SystemNum` varchar(11) NOT NULL,
`System` varchar(20) NOT NULL,
`LongSystem` varchar(40) NOT NULL,
`Contractor` varchar(4),
PRIMARY KEY (`SystemNum`),
CONSTRAINT `Pull_System` FOREIGN KEY (`SystemNum`) REFERENCES `CallsToDispatch`.`Techs` (`SystemNum`) ON UPDATE CASCADE ON DELETE NO ACTION,
INDEX `SystemNum` (SystemNum)
) ENGINE=`InnoDB` COMMENT='';
I managed to get the foreign keys to relate the fields in Techs. However, I'm clueless as to how to get the data out as one table. I tried this:
$stmt = $mysqli->prepare("SELECT * FROM Techs
INNER JOIN Systems
ON Systems.SystemNum = Techs.SystemNum");
$stmt->execute();
$GrabSystem = $stmt->get_result();
{
while ($row = $GrabSystem->fetch_assoc())
{
$SystemNum = $row["SystemNum"];
$System = $row["System"];
$LongSystem = $row["LongSystem"];
$Contractor = $row["Contractor"];
echo $LongSystem . ' !! ' . $System . ' !! <br>' ;
}
}
However, this one returns "Fatal error: Call to a member function execute() on a non-object in /Library/WebServer/Documents/DispatchReports/Retrieve.php on line 50":
$stmt = $mysqli->prepare("SELECT * FROM Techs
INNER JOIN Supervisors
ON Systems.Supervisor = Techs.Supervisor");
$stmt->execute();
$GrabSupervisor = $stmt->get_result();
{
while ($row = $GrabSupervisor->fetch_assoc())
{
$SuperFirstName = $row['FirstName'];
$SuperLastName = $row['LastName'];
echo $SuperFirstName . ' ' . $SuperFirstName . '<br>';
}
}
I'll keep hunting ducks and hopefully my dog want be chasing rabbits.
