Use one mysql statement as variable for another mysql statement

1
votes

I have 2 mysql statements one gets all the information from 4 linked tables for staff. I am attempting to get the title for each staff member which is in another table but I can't add to the 1 statement because it is unrelated to the primary table being selected. I have tried about 20 variations on the mysql statement with no success. I did get it to pull the title and output it via a variable in the second statement but it is giving every one the same title, it doesn't seem to be updating the variable when it loops through again.

1st mysql statement reads (simplified version it is very long)

    SELECT staff_tbl.titleID FROM staff_tbl

2nd mysql statement reads 

    SELECT titles_tbl.titleID, titles_tbl.titleName FROM titles_tbl WHERE titles_tbl.titleID = '" . $getTitle . "'"

variable is set as ($row_rsOfficeLocation is result of the first mysql statement)

    $getTitle = $row_rsOfficeLocation['titleID'];

Does anyone know how to make it update the $getTitle varaible when it is requested in the second statement every time it updates in the first statement?

EDIT: Here is the database structure and some values

SET FOREIGN_KEY_CHECKS=0;

-- Table structure for city_tbl

DROP TABLE IF EXISTS city_tbl; CREATE TABLE city_tbl ( cityID int(11) NOT NULL auto_increment, cityName varchar(255) NOT NULL, PRIMARY KEY (cityID) ) ENGINE=MyISAM AUTO_INCREMENT=83 DEFAULT CHARSET=utf8;

-- Records of city_tbl

INSERT INTO city_tbl VALUES ('1', 'Aloha'); INSERT INTO city_tbl VALUES ('2', 'Ann Arbor');

-- Table structure for officelocations_tbl

DROP TABLE IF EXISTS officelocations_tbl; CREATE TABLE officelocations_tbl ( locationID int(11) NOT NULL auto_increment, officeName char(255) NOT NULL, address1 varchar(285) NOT NULL, address2 varchar(285) default NULL, cityID int(11) NOT NULL, stateID int(11) NOT NULL, zipCode int(11) default NULL, officePhone varchar(13) default NULL, contact1 int(11) default NULL, contact2 int(11) default NULL, partner int(11) default NULL, PRIMARY KEY (locationID), KEY City (cityID), KEY State (stateID), KEY Contact1 (contact1), KEY Contact2 (contact2), KEY Partner (partner) ) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

-- Records of officelocations_tbl

INSERT INTO officelocations_tbl VALUES ('1', 'Atlanta', '5555 Some Ave', '#311', '3', '10', '30041', '', null, null, '26'); INSERT INTO officelocations_tbl VALUES ('2', 'Austin', '5555 Some Ave', '', '4', '43', '78734', '555-555-1212', '72', null, '81');

-- Table structure for staff_tbl

DROP TABLE IF EXISTS staff_tbl; CREATE TABLE staff_tbl ( staffID int(11) NOT NULL auto_increment, staffID_C2 int(11) NOT NULL, staffID_P int(11) NOT NULL, firstName varchar(255) NOT NULL, middleInitial varchar(15) default NULL, lastName varchar(255) NOT NULL, suffix varchar(15) default NULL, accredations varchar(150) default NULL, email varchar(255) NOT NULL, phone varchar(25) NOT NULL, mobilePhone varchar(25) default NULL, officePhone varchar(25) default NULL, faxNumber varchar(25) default NULL, address1 varchar(255) NOT NULL, address2 varchar(255) default NULL, cityID int(11) NOT NULL, stateID int(11) NOT NULL, zipCode int(11) NOT NULL, titleID int(11) NOT NULL, locationID int(11) NOT NULL, photoURL varchar(255) default NULL, vCardURL varchar(255) default NULL, qRCodeURL varchar(255) default NULL, resumeURL varchar(255) default NULL, biography longtext, dateCreated date NOT NULL, PRIMARY KEY (staffID), KEY State (stateID), KEY Location (locationID), KEY Title USING BTREE (titleID), KEY City USING BTREE (cityID), KEY Staff USING BTREE (staffID) ) ENGINE=MyISAM AUTO_INCREMENT=102 DEFAULT CHARSET=utf8;

-- Records of staff_tbl

INSERT INTO staff_tbl VALUES ('1', '1', '1', 'John', '', 'Doe', '', '', '[email protected]', '5555551212', '', '5555551212', '5555551212', '5555 Some Ave.', 'Suite 8, #317', '21', '42', '55555', '3', '0', '', '', '', '', '1', '2012-08-02'); INSERT INTO staff_tbl VALUES ('2', '2', '2', 'Jane', '', 'Doe', '', '', '[email protected]', '5555551212', '', '5555551212', '5555551212', '555555 SW Some Ave', '', '1', '37', '55555', '3', '0', '', '', '', '', '2', '2012-08-02');

-- Table structure for state_tbl

DROP TABLE IF EXISTS state_tbl; CREATE TABLE state_tbl ( stateID int(11) NOT NULL auto_increment, state_abreviation char(10) NOT NULL, state_name char(100) NOT NULL, PRIMARY KEY (stateID) ) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8;

-- Records of state_tbl

INSERT INTO state_tbl VALUES ('51', 'DC', 'Washington DC'); INSERT INTO state_tbl VALUES ('1', 'AL', 'Alabama');

-- Table structure for titles_tbl

DROP TABLE IF EXISTS titles_tbl; CREATE TABLE titles_tbl ( titleID int(11) NOT NULL auto_increment, titleName varchar(255) NOT NULL, PRIMARY KEY (titleID) ) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

-- Records of titles_tbl

INSERT INTO titles_tbl VALUES ('1', 'Associate Consultant\r\n'); INSERT INTO titles_tbl VALUES ('2', 'Chief Financial Officer'); INSERT INTO titles_tbl VALUES ('3', 'Consultant'); INSERT INTO titles_tbl VALUES ('4', 'Director, Business Development'); INSERT INTO titles_tbl VALUES ('5', 'LEAVE TITLE BLANK'); INSERT INTO titles_tbl VALUES ('6', 'National Director of Information Technology'); INSERT INTO titles_tbl VALUES ('7', 'National Director of Operations'); INSERT INTO titles_tbl VALUES ('8', 'National Technical Director'); INSERT INTO titles_tbl VALUES ('9', 'Partner'); INSERT INTO titles_tbl VALUES ('10', 'Regional Consultant'); INSERT INTO titles_tbl VALUES ('11', 'Senior Consultant'); INSERT INTO titles_tbl VALUES ('12', 'Vice President'); INSERT INTO titles_tbl VALUES ('13', 'Project Accountant'); INSERT INTO titles_tbl VALUES ('14', '');

Sample Output would be like so:

Sample Output It is working except for the fact that all the titles it outputs are the same for every staff member. The code I am using is the one I posted earlier for the second select statement. I don't know if it would be easier if I could just figure out how to join the titles_tbl to the main sql statement. I have tried several variations from a Union to just the second select within the first one and none of them seem to work or they throw an error.

This is the first sql statement I have been using including all aliases for my joins

    SELECT staff_tbl.staffID, staff_tbl.staffID_C2, staff_tbl.staffID_P, staff_tbl.firstName, staff_tbl.middleInitial, staff_tbl.lastName, staff_tbl.suffix, staff_tbl.accredations, staff_tbl.email, staff_tbl.phone, staff_tbl.mobilePhone, staff_tbl.officePhone, staff_tbl.faxNumber, staff_tbl.address1, staff_tbl.address2, staff_tbl.cityID, staff_tbl.stateID, staff_tbl.zipCode, staff_tbl.titleID, staff_tbl.locationID, staff_tbl.photoURL, staff_tbl.vCardURL, staff_tbl.qRCodeURL, staff_tbl.resumeURL, staff_tbl.biography, staff_tbl.dateCreated, officelocations_tbl.locationID, officelocations_tbl.officeName, officelocations_tbl.address1, officelocations_tbl.address2, officelocations_tbl.cityID, officelocations_tbl.stateID, officelocations_tbl.zipCode, officelocations_tbl.officePhone, officelocations_tbl.contact1, officelocations_tbl.contact2, officelocations_tbl.partner, city_tbl.cityID, city_tbl.cityName, state_tbl.stateID, state_tbl.state_abreviation, state_tbl.state_name, titles_tbl.titleID, titles_tbl.titleName,  contact1.firstName AS c1Firstname, contact1.lastName AS c1lastName, contact1.middleInitial AS c1middleInitial, contact1.suffix AS c1suffix,  contact1.accredations AS c1accredations, contact1.phone AS c1Phone, contact1.faxNumber AS c1FaxNumber, contact1.mobilePhone AS c1Mobile,  contact1.email AS c1Email, contact1.titleID AS c1Title,  contact2.firstName AS c2Firstname, contact2.lastName AS c2lastName, contact2.middleInitial AS c2middleInitial, contact2.suffix AS c2suffix,  contact2.accredations AS c2accredations, contact2.phone AS c2Phone, contact2.faxNumber AS c2FaxNumber, contact2.mobilePhone AS c2Mobile,  contact2.email AS c2Email, contact2.titleID AS c2Title,  partner.firstName AS c3Firstname, partner.lastName AS c3lastName, partner.middleInitial AS c3middleInitial, partner.suffix AS c3suffix,  partner.accredations AS c3accredations, partner.phone AS c3Phone, partner.faxNumber AS c3FaxNumber, partner.mobilePhone AS c3Mobile,  partner.email AS c3Email, partner.titleID AS c3Title  FROM officelocations_tbl INNER JOIN staff_tbl ON staff_tbl.staffID = officelocations_tbl.contact1 INNER JOIN state_tbl ON state_tbl.stateID = officelocations_tbl.stateID INNER JOIN titles_tbl ON titles_tbl.titleID = staff_tbl.titleID INNER JOIN city_tbl ON city_tbl.cityID = officelocations_tbl.cityID LEFT OUTER JOIN staff_tbl contact1 ON (contact1.staffID = officelocations_tbl.contact1)  LEFT OUTER JOIN staff_tbl contact2 ON (contact2.staffID = officelocations_tbl.contact2)  LEFT OUTER JOIN staff_tbl partner ON (partner.staffID = officelocations_tbl.partner)

Sorry for the long section of code but it would take me an hour to indent it all.

EDIT: Ok so I reworked my mysql statement and got it to pull all the information from the database now. but I have a minor issue with it. On every 3rd iteration of the loop I have it switches the place of my contact 1 and partner in the lists. It only seems to be switching two of them out of order. So if I have 3 contacts it switches the contact1 and contact2 and on the ones where I only have 2 contacts it is switching the order of them.

Here is my new mysql statement I'm using.

    SELECT staff_tbl.staffID, staff_tbl.firstName, staff_tbl.middleInitial, staff_tbl.lastName,
     staff_tbl.suffix, staff_tbl.accredations, staff_tbl.email, staff_tbl.phone,
     staff_tbl.mobilePhone, staff_tbl.officePhone, staff_tbl.faxNumber, staff_tbl.titleID, 
    staff_tbl.locationID, titles_tbl.titleID, titles_tbl.titleName,    
    officelocations_tbl.locationID, officelocations_tbl.officeName, officelocations_tbl.address1,
     officelocations_tbl.address2, officelocations_tbl.cityID, officelocations_tbl.stateID, 
    officelocations_tbl.zipCode, officelocations_tbl.officePhone, officelocations_tbl.contact1,
     officelocations_tbl.contact2, officelocations_tbl.partner, state_tbl.stateID, 
    state_tbl.state_abreviation, state_tbl.state_name, city_tbl.cityID, city_tbl.cityName, 
    officelocations_tbl.contact1 AS c1Contact, officelocations_tbl.contact2 AS c2Contact, 
    officelocations_tbl.partner AS c3Contact FROM staff_tbl INNER JOIN titles_tbl ON 
    titles_tbl.titleID = staff_tbl.titleID INNER JOIN officelocations_tbl ON officelocations_tbl.contact1 = staff_tbl.staffID  OR officelocations_tbl.contact2 = staff_tbl.staffID
      OR officelocations_tbl.partner = staff_tbl.staffID  
    OR staff_tbl.locationID = officelocations_tbl.locationID 
    INNER JOIN state_tbl ON state_tbl.stateID = officelocations_tbl.stateID 
    INNER JOIN city_tbl ON city_tbl.cityID = officelocations_tbl.cityID

For my display I have an if statement to check whether there is a contact1, contact2 and partner. If the contact 2 is empty it will only display contact 1 and partner, while if contact 2 is present it will show all 3 contacts, and an else statement that will show only the partner if there is no contact1 or contact2. The php looping through this works perfectly so there is no error in that. It is the reference to and/or the sql statement that isn't quite right yet that is causing my error. For each loop through I allow the statement to run and when it hits the second (if there is one) contact to fetch the row again. I set a variable equal to the staff_id so that I can have it pull the correct people associated with each location. Before I have it fetch the row I set the staff id to the particular contact example:

    $staff_ID == $row_rsOfficeLocation['c3Contact'];

It reads the assignment correct but for some reason it seems to be hitting the fetch assoc call before it reads the assignment to the staff id. Effectively setting the staff id to the second name in the list rather than the first.

Any ideas?

Edit to include the php loop:

    <?php do { 
$staff_ID = $row_myQuery['staffID'];
?>        
    <ul>
              <li><?php echo $row_myQuery['address1']; ?> <?php echo $row_myQuery['address2']; ?></li>
              <li><?php echo $row_myQuery['cityName']; ?>, <?php echo $row_myQuery['state_abreviation']; ?> <?php echo $row_myQuery['zipCode']; ?></li>
              <br />


              <?php
                if ($row_myQuery['c2Contact'] == "" && $row_myQuery['c1Contact'] != ""){?>
                    <!-- if contact 2 is empty display only contact 1 and partner -->

                     <!-- contact 1 information -->  
                     <?php if ($row_myQuery['c1Contact'] != ""){    
                     echo $staff_ID;                      
                         $staff_ID == $row_myQuery['c1Contact'];?>
                        <li class="emailGrey"><strong><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></strong></li>
                        <li>Title: <?php echo $row_myQuery['titleName']; ?></li>
                          <li>Tel: <?php echo format_phone($row_myQuery['phone']);?></li>
                          <li>Fax: <?php echo format_phone($row_myQuery['faxNumber']); ?></li>
                          <li><a href="mailto:<?php echo $row_myQuery['email']; ?>">Email: <?php echo $row_myQuery['email'];  echo $row_myQuery['staffID']; ?></a></li>
                          <br />
                       <?php }
                   ?>
                       <?php if ($row_myQuery['c3Contact'] != ""){  

                         $staff_ID == $row_myQuery['c3Contact'];
                         $row_myQuery = mysql_fetch_assoc($myQuery);
                          echo $staff_ID;   ?>

                          <!-- partner information -->  
                          <li class="emailGrey"><strong><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></strong></li>
                          <li>Title: <?php echo $row_myQuery['titleName']; ?></li>
                          <li>Tel: <?php echo format_phone($row_myQuery['phone']); ?></li>
                          <li>Fax: <?php echo format_phone($row_myQuery['faxNumber']); ?></li>
                          <li><a href="mailto:<?php echo $row_myQuery['email']; ?>">Email: <?php echo $row_myQuery['email']; ?></a></li>
                     <?php }  ?>




                <?php }else if ($row_myQuery['c2Contact'] != ""){ ?>
                 <!-- if contact 2 is not empty display all contacts -->
                <!-- contact 1 information -->  
                 <?php if ($row_myQuery['c1Contact'] != ""){                          
                 $staff_ID = $row_myQuery['c1Contact'];
                 ?>

                <li class="emailGrey"><strong><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></strong></li>
                <li>Title: <?php echo $row_myQuery['titleName']; ?></li>
                  <li>Tel: <?php echo format_phone($row_myQuery['phone']);?></li>
                  <li>Fax: <?php echo format_phone($row_myQuery['faxNumber']); ?></li>
                  <li><a href="mailto:<?php echo $row_myQuery['email']; ?>">Email: <?php echo $row_myQuery['email']; ?></a></li>
                  <br />
                   <?php }
               ?>

              <?php if ($row_myQuery['c2Contact'] != ""){                         
                 $staff_ID = $row_myQuery['c2Contact'];
                 $row_myQuery = mysql_fetch_assoc($myQuery);?>

                <li class="emailGrey"><strong><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></strong></li>
                <li>Title: <?php echo $row_myQuery['titleName']; ?></li>
                  <li>Tel: <?php echo format_phone($row_myQuery['phone']);?></li>
                  <li>Fax: <?php echo format_phone($row_myQuery['faxNumber']); ?></li>
                  <li><a href="mailto:<?php echo $row_myQuery['email']; ?>">Email: <?php echo $row_myQuery['email']; ?></a></li>
                  <br />
                   <?php }
               ?>

               <?php if ($row_myQuery['c3Contact'] != ""){  

                 $staff_ID = $row_myQuery['c3Contact'];
                 $row_myQuery = mysql_fetch_assoc($myQuery);
                 ?>

                  <!-- partner information -->  
                  <li class="emailGrey"><strong><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></strong></li>
                  <li>Title: <?php echo $row_myQuery['titleName']; ?></li>
                  <li>Tel: <?php echo format_phone($row_myQuery['phone']); ?></li>
                  <li>Fax: <?php echo format_phone($row_myQuery['faxNumber']); ?></li>
                  <li><a href="mailto:<?php echo $row_myQuery['email']; ?>">Email: <?php echo $row_myQuery['email']; ?></a></li>

               <?php }
               ?>       




                <?php } else { ?>
                <!-- if only partner display only partner --> 
               <?php if ($row_myQuery['c3Contact'] != ""){  

                    $staff_ID = $row_myQuery['c3Contact'];
                 ?>

                  <!-- partner information -->  
                  <li class="emailGrey"><strong><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></strong></li>
                  <li>Title: <?php echo $row_myQuery['titleName']; ?></li>
                  <li>Tel: <?php echo format_phone($row_myQuery['phone']); ?></li>
                  <li>Fax: <?php echo format_phone($row_myQuery['faxNumber']); ?></li>
                  <li><a href="mailto:<?php echo $row_myQuery['email']; ?>">Email: <?php echo $row_myQuery['email']; ?></a></li>

               <?php }
      } 
     } while ($row_myQuery = mysql_fetch_assoc($myQuery)); ?>
2
phpmysql
We can't really advise on your PHP code and your fetch order without seeing the PHP fetch loop. - Michael Berkowski
Added the php loop to my question - puddleJumper

2 Answers

5
votes

This is a rudimentary INNER JOIN:

SELECT
  titles_tbl.titleID, 
  titles_tbl.titleName
FROM
  titles_tbl 
  JOIN staff_tbl ON titles_tbl.titleID = staff_tbl.titleID 
WHERE
  staff_tbl.titleID = 'some value from your long query'

It can also be done with a subquery in the WHERE clause:

SELECT
  titles_tbl.titleID, 
  titles_tbl.titleName
FROM
  titles_tbl
WHERE
  titleID = (SELECT titleID FROM staff_tbl WHERE <whatever from your long query>)

Or if the first query is intended to return multiple rows rather than exactly one row, you will need to use an IN() subquery 

/* Replace the WHERE clause above with */
WHERE
  titleID IN(SELECT titleID FROM staff_tbl WHERE <whatever from your long query>)
0
votes

Ok So I figured out my own question. I created a couple more tables and used them as access points to get to the title and location information. Thank you for all your help :)

For anyone else try to accomplish this below is the sql statement I used.

    SELECT 
    staff_tbl.staffID, staff_tbl.firstName, staff_tbl.middleInitial, 
    staff_tbl.lastName, staff_tbl.suffix, staff_tbl.accredations, staff_tbl.email,
    staff_tbl.phone, staff_tbl.mobilePhone, staff_tbl.officePhone, staff_tbl.faxNumber,
    staff_tbl.address1, staff_tbl.address2, staff_tbl.cityID, staff_tbl.stateID, 
    staff_tbl.zipCode, location_tbl.locationID, 
    location_tbl.staffID, officelocations_tbl.locationID, 
    officelocations_tbl.officeName, staff_title_tbl.title_ID, staff_title_tbl.staff_ID,
     titles_tbl.titleID, titles_tbl.titleName, city_tbl.cityID, city_tbl.cityName, 
    state_tbl.stateID, state_tbl.state_abreviation, state_tbl.state_name 

    FROM staff_tbl 

    INNER JOIN location_tbl ON location_tbl.staffID = staff_tbl.staffID 
    INNER JOIN officelocations_tbl ON location_tbl.locationID = officelocations_tbl.locationID
    INNER JOIN staff_title_tbl ON staff_title_tbl.staff_ID = staff_tbl.staffID 
    INNER JOIN titles_tbl ON staff_title_tbl.title_ID = titles_tbl.titleID 
    INNER JOIN city_tbl ON city_tbl.cityID = staff_tbl.cityID 
    INNER JOIN state_tbl ON state_tbl.stateID = staff_tbl.stateID

As you can see the two extra tables I created are location_tbl and staff_title_tbl. Each of these has 2 columns one for the locationID (or titleID) and one for the staffID. Using these I was able to get all the information needed. there is a minor issue with this just as a side note, which I haven't quite worked the kinks out of, if the staff member is associated with more than one location it will display the exact same information for that staff member multiple times with only the location changed. But at least I have most of it worked out.

Thanks again for your help.