PHP + Mysql：动态表名从第一个查询执行第二个查询 [英] PHP + Mysql: dynamic table name from first query for execution of second query

问题描述

我有三个表，第一个是存储应用程序的表，第二个是存储不同在线表单（不同类型的应用程序）的表，第三个是存储实际表单数据的表：

  TABLE应用程序========= 
 -applicationID（PK）
 -formID（FK）
 -formRecordID 
 ==================== 
 
表格形式========= 
  - formID（PK）
 -formName 
 -tableName（可以是form_businessLicense，eventLicense等）
 ================= === 
 
 TABLE form_businessLicense ===== 
 -recordID（PK）
 -dateSubmitted 
  - （一大堆其他数据）
 
  

formRecordID指向form_businessLicense或eventLicense中的recordID。因为它可以引用任何表，它不能是一个外键。因此，我从表单表中抓取了tableName，然后构建一个查询来获取所有的应用程序数据，例如form_businessLicense。

所以我需要得到来自，例如所有应用程序的数据，以及填写的申请表中的一些数据（例如：form_businessLicense）。我只是要粘贴我的代码（我实际上是在给定的一组ID中查询所有应用程序）：

  $ application = $ this-> selectAll（
SELECT applicationID，formName，tableName，fieldIdentifier，formRecordID，dateSubmitted，DATE_FORMAT（dateSubmitted，'％c /％e /％Y'）AS dateSubmittedFormat 
 FROM applications AS a 
 JOIN表单AS f 
 ON a.formID = f.formID 
 WHERE a.applicationID IN（。$ applicationIDs）
 ORDER BY dateSubmitted ASC
）; （$ a = 0; $ a $（$应用程序）; $ a ++）{
 $ form = $ this-> select（SELECT$应用程序[$ a] ['fieldIdentifier']。AS identifierName 
 FROM。$ applications [$ a] ['tableName']。
 WHERE recordID =。$ applications [$ a] ['formRecordID' ] 
）; 
 $ applications [$ a] ['identifierName'] = $ form ['identifierName']; 
} 
  

有没有办法将这两个查询合并成一个，所以我不必须循环所有结果，并为每个结果运行单独的查询？我觉得我可以用一个JOIN来做到这一点，但是我不知道如何引用tableName和formRecordID用于同一个SQL语句。

解决方案

您需要将连接应用于三个表，并为第三个表的PK添加一个group by子句，并选择第三个表的count（PK）。

注意：用于主键的PK

I have three tables, the first is a table storing applications, the second is a table storing different online forms (different types of applications), the third is a table that stores actual form data:

    TABLE applications=========
    -applicationID (PK)
    -formID (FK)
    -formRecordID
    ====================  

    TABLE forms=========
    -formID (PK)
    -formName
    -tableName (could be 'form_businessLicense','eventLicense',etc)
    ====================

    TABLE form_businessLicense=====
    -recordID (PK)
    -dateSubmitted
    -(a whole bunch of other data)
    ===============================

"formRecordID" points to "recordID" in "form_businessLicense" or "eventLicense". Since it could reference any table, it can't be a foreign key. So instead I grab the tableName from the "forms" table, then build a query to get all the application data from, say "form_businessLicense".

So I need to get data from, say, all applications plus a bit of data from the application form filled out (ex:form_businessLicense). I'm just going to paste my code (I'm actually querying all applications in a given set of IDs):

$applications = $this->selectAll(
            "SELECT applicationID, formName, tableName, fieldIdentifier, formRecordID, dateSubmitted, DATE_FORMAT(dateSubmitted,'%c/%e/%Y') AS dateSubmittedFormat
            FROM applications AS a 
            JOIN forms AS f
            ON a.formID = f.formID
            WHERE a.applicationID IN (".$applicationIDs.")
            ORDER BY dateSubmitted ASC"
        );

        for($a=0;$a<count($applications);$a++){
            $form = $this->select("SELECT ".$applications[$a]['fieldIdentifier']." AS identifierName
                FROM ".$applications[$a]['tableName']."
                WHERE recordID = ".$applications[$a]['formRecordID']
            );
            $applications[$a]['identifierName'] = $form['identifierName'];
        }

Is there any way to merge these two queries into one so I don't have to loop over all results and run a separate query for each result? I feel like I could maybe do this with a JOIN but I'm not sure how to reference the "tableName" and "formRecordID" for use in the same SQL statement.

解决方案

You need to apply join to three tables, and select count(PK) of third table while adding a group by clause for the PK of third table.

Note: PK used for Primary Key

这篇关于PHP + Mysql：动态表名从第一个查询执行第二个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！