clueless about Relational DB's
Results 1 to 2 of 2

Thread: clueless about Relational DB's

  1. #1
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    Exclamation clueless about Relational DB's

    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:

    PHP Code:
    CREATE TABLE `CallsToDispatch`.`Techs` (
        `
    IdNumbigint(4NOT NULL AUTO_INCREMENT,
        `
    TechNumbigint NOT NULL,
        `
    FirstNamevarchar(20NOT NULL,
        `
    LastNamevarchar(20NOT NULL,
        `
    SystemNumvarchar(11NOT NULL,
        `
    Supervisorint(3NOT NULL,
        
    PRIMARY KEY (`TechNum`),
        
    UNIQUE `TechUSING BTREE (TechNum)
    ENGINE=`InnoDBCOMMENT=''

    And the second table:

    PHP Code:
    CREATE TABLE `CallsToDispatch`.`Supervisors` (
        `
    SystemNumvarchar(11NOT NULL,
        `
    Systemvarchar(20NOT NULL,
        `
    LongSystemvarchar(40NOT NULL,
        `
    Contractorvarchar(4),
        
    PRIMARY KEY (`SystemNum`),
        
    CONSTRAINT `LookupSysFOREIGN KEY (`SystemNum`) REFERENCES `CallsToDispatch`.`Techs` (`SystemNum`)   ON UPDATE CASCADE ON DELETE NO ACTION,
        
    UNIQUE `SysIndexUSING BTREE (SystemNum)
    ENGINE=`InnoDBCOMMENT=''
    And the third:
    PHP Code:
    CREATE TABLE `CallsToDispatch`.`Systems` (
        `
    SystemNumvarchar(11NOT NULL,
        `
    Systemvarchar(20NOT NULL,
        `
    LongSystemvarchar(40NOT NULL,
        `
    Contractorvarchar(4),
        
    PRIMARY KEY (`SystemNum`),
        
    CONSTRAINT `Pull_SystemFOREIGN KEY (`SystemNum`) REFERENCES `CallsToDispatch`.`Techs` (`SystemNum`)   ON UPDATE CASCADE ON DELETE NO ACTION,
        
    INDEX `SystemNum` (SystemNum)
    ENGINE=`InnoDBCOMMENT=''
    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:



    PHP Code:
    $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":

    PHP Code:
    $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.
    Last edited by timstring; 04-02-2013 at 04:07 PM.

  2. #2
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    I got the duck. The second query referenced the wrong table.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •