StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUse one mysql statement as variable for another mysql statement
primarykey
Id
12593964
data
AcceptedAnswerId
12763787
AnswerCount
2
ClosedDate
CommentCount
2
CommunityOwnedDate
CreationDate
2012-09-26T02:57:58.910
FavoriteCount
0
LastActivityDate
2012-10-06T20:53:06.153
LastEditDate
2012-09-30T22:32:15.417
LastEditorUserId
1602197
OwnerUserId
1602197
ParentId
0
PostTypeId
1
Score
1
ViewCount
302
LastEditorDisplayName
text
Body
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) <pre><code> SELECT staff_tbl.titleID FROM staff_tbl </code></pre> 2nd mysql statement reads <pre><code> SELECT titles_tbl.titleID, titles_tbl.titleName FROM titles_tbl WHERE titles_tbl.titleID = '" . $getTitle . "'" </code></pre> variable is set as ($row_rsOfficeLocation is result of the first mysql statement) <pre><code> $getTitle = $row_rsOfficeLocation['titleID']; </code></pre> 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; <hr> -- Table structure for <code>city_tbl</code> <hr> DROP TABLE IF EXISTS <code>city_tbl</code>; CREATE TABLE <code>city_tbl</code> ( <code>cityID</code> int(11) NOT NULL auto_increment, <code>cityName</code> varchar(255) NOT NULL, PRIMARY KEY (<code>cityID</code>) ) ENGINE=MyISAM AUTO_INCREMENT=83 DEFAULT CHARSET=utf8; <hr> -- Records of city_tbl <hr> INSERT INTO <code>city_tbl</code> VALUES ('1', 'Aloha'); INSERT INTO <code>city_tbl</code> VALUES ('2', 'Ann Arbor'); <hr> -- Table structure for <code>officelocations_tbl</code> <hr> DROP TABLE IF EXISTS <code>officelocations_tbl</code>; CREATE TABLE <code>officelocations_tbl</code> ( <code>locationID</code> int(11) NOT NULL auto_increment, <code>officeName</code> char(255) NOT NULL, <code>address1</code> varchar(285) NOT NULL, <code>address2</code> varchar(285) default NULL, <code>cityID</code> int(11) NOT NULL, <code>stateID</code> int(11) NOT NULL, <code>zipCode</code> int(11) default NULL, <code>officePhone</code> varchar(13) default NULL, <code>contact1</code> int(11) default NULL, <code>contact2</code> int(11) default NULL, <code>partner</code> int(11) default NULL, PRIMARY KEY (<code>locationID</code>), KEY <code>City</code> (<code>cityID</code>), KEY <code>State</code> (<code>stateID</code>), KEY <code>Contact1</code> (<code>contact1</code>), KEY <code>Contact2</code> (<code>contact2</code>), KEY <code>Partner</code> (<code>partner</code>) ) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8; <hr> -- Records of officelocations_tbl <hr> INSERT INTO <code>officelocations_tbl</code> VALUES ('1', 'Atlanta', '5555 Some Ave', '#311', '3', '10', '30041', '', null, null, '26'); INSERT INTO <code>officelocations_tbl</code> VALUES ('2', 'Austin', '5555 Some Ave', '', '4', '43', '78734', '555-555-1212', '72', null, '81'); <hr> -- Table structure for <code>staff_tbl</code> <hr> DROP TABLE IF EXISTS <code>staff_tbl</code>; CREATE TABLE <code>staff_tbl</code> ( <code>staffID</code> int(11) NOT NULL auto_increment, <code>staffID_C2</code> int(11) NOT NULL, <code>staffID_P</code> int(11) NOT NULL, <code>firstName</code> varchar(255) NOT NULL, <code>middleInitial</code> varchar(15) default NULL, <code>lastName</code> varchar(255) NOT NULL, <code>suffix</code> varchar(15) default NULL, <code>accredations</code> varchar(150) default NULL, <code>email</code> varchar(255) NOT NULL, <code>phone</code> varchar(25) NOT NULL, <code>mobilePhone</code> varchar(25) default NULL, <code>officePhone</code> varchar(25) default NULL, <code>faxNumber</code> varchar(25) default NULL, <code>address1</code> varchar(255) NOT NULL, <code>address2</code> varchar(255) default NULL, <code>cityID</code> int(11) NOT NULL, <code>stateID</code> int(11) NOT NULL, <code>zipCode</code> int(11) NOT NULL, <code>titleID</code> int(11) NOT NULL, <code>locationID</code> int(11) NOT NULL, <code>photoURL</code> varchar(255) default NULL, <code>vCardURL</code> varchar(255) default NULL, <code>qRCodeURL</code> varchar(255) default NULL, <code>resumeURL</code> varchar(255) default NULL, <code>biography</code> longtext, <code>dateCreated</code> date NOT NULL, PRIMARY KEY (<code>staffID</code>), KEY <code>State</code> (<code>stateID</code>), KEY <code>Location</code> (<code>locationID</code>), KEY <code>Title</code> USING BTREE (<code>titleID</code>), KEY <code>City</code> USING BTREE (<code>cityID</code>), KEY <code>Staff</code> USING BTREE (<code>staffID</code>) ) ENGINE=MyISAM AUTO_INCREMENT=102 DEFAULT CHARSET=utf8; <hr> -- Records of staff_tbl <hr> INSERT INTO <code>staff_tbl</code> VALUES ('1', '1', '1', 'John', '', 'Doe', '', '', 'johndoe@someemail.com', '5555551212', '', '5555551212', '5555551212', '5555 Some Ave.', 'Suite 8, #317', '21', '42', '55555', '3', '0', '', '', '', '', '1', '2012-08-02'); INSERT INTO <code>staff_tbl</code> VALUES ('2', '2', '2', 'Jane', '', 'Doe', '', '', 'jandoe@someemail.com', '5555551212', '', '5555551212', '5555551212', '555555 SW Some Ave', '', '1', '37', '55555', '3', '0', '', '', '', '', '2', '2012-08-02'); <hr> -- Table structure for <code>state_tbl</code> <hr> DROP TABLE IF EXISTS <code>state_tbl</code>; CREATE TABLE <code>state_tbl</code> ( <code>stateID</code> int(11) NOT NULL auto_increment, <code>state_abreviation</code> char(10) NOT NULL, <code>state_name</code> char(100) NOT NULL, PRIMARY KEY (<code>stateID</code>) ) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8; <hr> -- Records of state_tbl <hr> INSERT INTO <code>state_tbl</code> VALUES ('51', 'DC', 'Washington DC'); INSERT INTO <code>state_tbl</code> VALUES ('1', 'AL', 'Alabama'); <hr> -- Table structure for <code>titles_tbl</code> <hr> DROP TABLE IF EXISTS <code>titles_tbl</code>; CREATE TABLE <code>titles_tbl</code> ( <code>titleID</code> int(11) NOT NULL auto_increment, <code>titleName</code> varchar(255) NOT NULL, PRIMARY KEY (<code>titleID</code>) ) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8; <hr> -- Records of titles_tbl <hr> INSERT INTO <code>titles_tbl</code> VALUES ('1', 'Associate Consultant\r\n'); INSERT INTO <code>titles_tbl</code> VALUES ('2', 'Chief Financial Officer'); INSERT INTO <code>titles_tbl</code> VALUES ('3', 'Consultant'); INSERT INTO <code>titles_tbl</code> VALUES ('4', 'Director, Business Development'); INSERT INTO <code>titles_tbl</code> VALUES ('5', 'LEAVE TITLE BLANK'); INSERT INTO <code>titles_tbl</code> VALUES ('6', 'National Director of Information Technology'); INSERT INTO <code>titles_tbl</code> VALUES ('7', 'National Director of Operations'); INSERT INTO <code>titles_tbl</code> VALUES ('8', 'National Technical Director'); INSERT INTO <code>titles_tbl</code> VALUES ('9', 'Partner'); INSERT INTO <code>titles_tbl</code> VALUES ('10', 'Regional Consultant'); INSERT INTO <code>titles_tbl</code> VALUES ('11', 'Senior Consultant'); INSERT INTO <code>titles_tbl</code> VALUES ('12', 'Vice President'); INSERT INTO <code>titles_tbl</code> VALUES ('13', 'Project Accountant'); INSERT INTO <code>titles_tbl</code> VALUES ('14', ''); Sample Output would be like so: <img src="https://i.stack.imgur.com/IP03C.png" alt="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 <pre><code> 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) </code></pre> 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. <pre><code> 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 </code></pre> 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: <pre><code> $staff_ID == $row_rsOfficeLocation['c3Contact']; </code></pre> 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: <pre><code> <?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> <?php if ($row_myQuery['c2Contact'] == "" && $row_myQuery['c1Contact'] != ""){?>   <?php if ($row_myQuery['c1Contact'] != ""){ echo $staff_ID; $staff_ID == $row_myQuery['c1Contact'];?> <li class="emailGrey"><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></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> <?php } ?> <?php if ($row_myQuery['c3Contact'] != ""){ $staff_ID == $row_myQuery['c3Contact']; $row_myQuery = mysql_fetch_assoc($myQuery); echo $staff_ID; ?>  <li class="emailGrey"><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></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'] != ""){ ?>   <?php if ($row_myQuery['c1Contact'] != ""){ $staff_ID = $row_myQuery['c1Contact']; ?> <li class="emailGrey"><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></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 if ($row_myQuery['c2Contact'] != ""){ $staff_ID = $row_myQuery['c2Contact']; $row_myQuery = mysql_fetch_assoc($myQuery);?> <li class="emailGrey"><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></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 if ($row_myQuery['c3Contact'] != ""){ $staff_ID = $row_myQuery['c3Contact']; $row_myQuery = mysql_fetch_assoc($myQuery); ?>  <li class="emailGrey"><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></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 { ?>  <?php if ($row_myQuery['c3Contact'] != ""){ $staff_ID = $row_myQuery['c3Contact']; ?>  <li class="emailGrey"><?php echo $row_myQuery['firstName']; ?> <?php echo $row_myQuery['middleInitial']; ?> <?php echo $row_myQuery['lastName']; ?> <?php echo $row_myQuery['suffix']; ?></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)); ?> </code></pre>
Tags
<php><mysql>
Title
Use one mysql statement as variable for another mysql statement
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USpuddleJumper
UserOwnerUserId
1. USpuddleJumper
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POUse one mysql statement as variable for another mysql statement
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. COWe can't really advise on your PHP code and your fetch order without seeing the PHP fetch loop.
 singulars
 PostPostId
 POUse one mysql statement as variable for another mysql statement
 UserUserId
 USMichael Berkowski
2. COAdded the php loop to my question
 singulars
 PostPostId
 POUse one mysql statement as variable for another mysql statement
 UserUserId
 USpuddleJumper

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.