问题：

使用一个mysql语句作为另一个mysql语句的变量

宗政燕七

2023-03-14

我有2个mysql语句，其中一个从4个链接表中获取所有信息，供员工使用。我正在尝试为另一个表中的每个staff member获取标题，但我无法将其添加到1语句中，因为它与所选的主表无关。我已经尝试了mysql语句的20个变体，但都没有成功。我确实让它通过第二条语句中的一个变量拉出标题并输出它，但它给每个语句都赋予了相同的标题，当它再次循环时似乎并没有更新变量。

第一条mysql语句读到（简化版很长）

    SELECT staff_tbl.titleID FROM staff_tbl

第二条mysql语句为

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

变量设置为($ROW_RSOfficeLocation是第一个mysql语句的结果）

    $getTitle = $row_rsOfficeLocation['titleID'];

有没有人知道，当它每次在第一条语句中更新时，在第二条语句中请求它时，如何使它更新$GetTitle varaible？

编辑：这里是数据库结构和一些值

设置FOREIGN_KEY_CHECKS=0；

--city_tbl的表结构

删除表（如果存在city_tbl）；创建表city_tbl(cityidint(11)不为NULL auto_increment，citynamevarchar(255)不为NULL，主键(cityid))engine=myisam auto_increment=83 DEFAULT charset=utf8；

--city_tbl记录

插入city_tbl值('1','ALOHA')；插入city_tbl值（“2”、“Ann Arbor”）；

--OfficeLocations_TBL的表结构

删除表（如果存在OfficeLocations_TBL）；创建表OfficeLocations_TBL(LocationIdint(11)不为空auto_increment，OfficeNamechar(255)不为空，Address1varchar(285)不为空，Address2varchar(285)默认为空，CityIdint(11)不为空，StateIdint(11)不为空，Zipcodeint(11)默认

--officelocations_tbl的记录

插入OfficeLocations_TBL值('1','Atlanta','5555 Some Ave','#311','3','10','30041','',null,null,'26')；插入OfficeLocations_TBL值('2','Austin','5555 Some Ave','','4','43','78734','555-555-1212','72',null,'81')；

--staff_tbl的表结构

如果存在staff_tbl,则删除表；创建表staff_tbl(staffidint(11)NOT NULL auto_increment,staffid_c2int（11）不为空,staffid_pint(11)不为空,firstnamevarchar(255)不为空,middleinitialvarchar(15)默认为空,lastnamevarchar(255)不为空,suffixvarchar(15)默认为空,认证varchar(150)默认为空,emailvarchar(255)不为空,phonevarchar(25)不为空,mobilephonevarchar(25)默认为空,OfficePhonevarchar(25)默认为空,faxnumbervarchar(25)默认为空，address1varchar(255)非空，cityidint(11)非空，stateidint(11)非空，zipcodeint(11)非空，titleidint(11)非空，locationidint(11)非空，photourlvarchar(255)默认为空

--staff_tbl记录

插入staff_tbl值('1','1','1','john',''','doe',''',插入staff_tbl值('2','2','2','jane','','doe','','','jandoe@someemail.com','55555551212',''55555551212','55555551212','5555555125SW Some Ave',''','1','37','555555','3','0','','','',''2','2012-08-02')；

--state_tbl的表结构

如果存在state_tbl,则删除表；创建表state_tbl(stateidint(11)NOT NULL auto_increment，state_abreviationchar(10)NOT NULL，state_namechar(100)NOT NULL，主键(stateid))engine=myisam auto_increment=54 DEFAULT charset=utf8；

--state_tbl的记录

插入state_tbl值('51','DC','Washington DC')；插入state_tbl值('1'、'Al'、'Alabama')；

--titles_tbl的表结构

删除表（如果存在titles_tbl）；创建表titles_tbl(titleidint(11)不为空auto_increment，titlenamevarchar(255)不为空，主键(titleid))engine=myisam auto_increment=15默认charset=utf8；

--titles_tbl的记录

插入titles_tbl值('1','Associated Consultantor\r\n')；插入titles_tbl值('2',‘首席财务官')；插入titles_tbl值('3','Consultant')；插入titles_tbl值('4','Director,Business Development')；插入titles_tbl值('5','leave TITLE blank')；在titles_tbl值('6','National Director of Information Technology')中插入；插入titles_tbl值('7','National Director of Operations')；插入titles_tbl值('8','National Technology Director')；插入titles_tbl值('9','partner')；插入titles_tbl值('10','Regional Consultant')；插入titles_tbl值('11',‘高级顾问')；插入titles_tbl值('12','Vice President')；插入titles_tbl值('13','Project Accountar')；插入titles_tbl值('14','')；

示例输出如下所示：

这是我使用的第一条sql语句，其中包括联接的所有别名

    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)

很抱歉代码很长，但我要花一个小时来缩进所有代码。

编辑：好的，所以我重新修改了我的mysql语句，让它现在从数据库中提取所有信息。但我有个小问题。在循环的每三次迭代中，它会切换我的联系人1和合作伙伴在列表中的位置。它似乎只是把其中的两个切换出了顺序。所以如果我有3个触点，它会切换contact1和contact2，在只有2个触点的触点上，它会切换它们的顺序。

下面是我正在使用的新mysql语句。

    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

对于我的显示，我有一个if语句来检查是否有contact1、contact2和Partner。如果联系人2为空，则只显示联系人1和合作伙伴，而如果联系人2存在，则显示所有3个联系人，如果没有联系人1或联系人2，则只显示合作伙伴。php循环通过这是完美的工作，所以没有错误。导致我的错误的是对和/或还不太正确的sql语句的引用。对于通过的每个循环，我允许语句运行，并且当它命中第二个（如果有一个）联系人时再次获取行。我设置了一个等于staff_id的变量，以便可以让它拉出与每个位置相关联的正确人员。在获取行之前，我将staff id设置为特定的联系人示例：

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

它正确地读取赋值，但由于某种原因，它似乎在将赋值读取到人员ID之前敲击了fetch assoc调用。实际上将staff id设置为列表中的第二个名称而不是第一个名称。

有什么想法吗？

编辑以包含php循环：

    <?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个答案

孔海超

2023-03-14

好吧，我想出了我自己的问题。我创建了更多的表，并将它们用作获取标题和位置信息的访问点。感谢您的所有帮助：）

对于其他人来说，下面是我使用的sql语句。

    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

可以看到，我创建的两个额外表是location_tbl和staff_title_tbl。每个都有两列，一列用于locationID（或titleID），一列用于staffID。使用这些，我能够获得所需的所有信息。这里有一个小问题，只是作为一个附带说明，我还没有完全解决问题，如果工作人员与多个位置相关联，它将多次显示该工作人员的完全相同的信息，只是更改了位置。但至少我已经解决了大部分问题。

再次感谢你的帮助。

宦翔

2023-03-14

这是一个基本的内部联接:

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'

也可以通过where子句中的子查询来实现：

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

或者如果第一个查询要返回多行而不是只返回一行，则需要使用in()子查询

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

使用一个mysql语句作为另一个mysql语句的变量

共有2个答案

相关问答

相关文章

相关阅读

相关工具

相关文档