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.