2. PHPRunner General questions
  3. [SOLVED]  Displaying Fields from different tables in list
This topic is locked
[SOLVED]

 Displaying Fields from different tables in list

 		8/20/2014 1:01:06 PM
PHPRunner General questions
C
 cyberslugg author

Hello,
I've tried searching the forums for this, because I assume should be fairly simple.
In my database there are three tables, Member, Employer, and Employment. These tables are linking based on MemberID, and EmployerID, with the Employment table acting as a bridge.
I am having difficulty displaying information from Employer on the Member's page. I have tried adding tables to the Member table's query editor but because not every Member always has an employment record associated with their MemberID it is breaking most of the system.
Is there a way to easily pull the data in using the editor, or should I be using PHP code snippets to write my own queries on the page?
I am working on phprunner 7.1
Sergey Kornilov admin 8/21/2014

I guess this is just a matter of bulding correct SQL query that would pull data from both tables. If not all members have Employer or Employment records consider using LEFT JOIN in SQL:

http://www.w3schools.com/sql/sql_join_left.asp
C
 		cyberslugg author 8/22/2014

Sorry, I should have been more clear in my original post.



SELECT

Member.MemberID,

// omitted fields //

Declaration.DeclarationYear,

Employer.CompanyNum,

Employer.CompanyName

FROM Member

LEFT OUTER JOIN Declaration ON Member.MemberID = Declaration.MemberId

LEFT OUTER JOIN Employment ON Member.MemberID = Employment.MemberID

LEFT OUTER JOIN Employer ON Employment.EmployerID = Employer.EmployerID

GROUP BY MemberID


The issue here is that each member can be employed by multiple employers (which should have been understood when I mentioned I was using a bridge table). So I do not want to get 2 identical member results with 1 employment record each. Hence I am using GROUP BY.
These are the problems I am having. In a Custom PHP Snippet I cannot access $data for the page, so I cannot use this:



$sql = "SELECT Member.MemberID,\n"

. " Employment.MemberID,\n"

. " Employment.EmployerID,\n"

. " Employer.EmployerID,\n"

. " Employer.CompanyName,\n"

. " Employer.CompanyNum\n"

. " FROM Member, Employment, Employer\n"

. " WHERE Member.MemberID = " . $data["MemberID"] . " AND Member.MemberID = Employment.MemberID AND Employment.EmployerID = Employer.EmployerID";
$result = CustomQuery($sql);
while($rows[]=db_fetch_array($result));
foreach ($rows as $employer) {

echo($employer["CompanyName"] . " " . $employer["CompanyNum"]);

}


and in the FieldName, ViewAs, Custom I cannot seem to run this:



$sql = "SELECT Member.MemberID,\n"

. " Employment.MemberID,\n"

. " Employment.EmployerID,\n"

. " Employer.EmployerID,\n"

. " Employer.CompanyName,\n"

. " Employer.CompanyNum\n"

. " FROM Member, Employment, Employer\n"

. " WHERE Member.MemberID = " . $data["MemberID"] . " AND Member.MemberID = Employment.MemberID AND Employment.EmployerID = Employer.EmployerID";
$result = CustomQuery($sql);
while($rows[]=db_fetch_array($result));
foreach ($rows as $row) {

$value . $row["CompanyName"] . " " . $row["CompanyNum"];

}
C
 		cyberslugg author 8/22/2014

And yes, I have tried passing this information in using session variables, but I am doing this on the List page and I think this is causing some issue.
C
 		cyberslugg author 8/22/2014

I literally have no idea what I did to fix this.
But I have gotten this working with this code in FieldName, ViewAs, Custom



$sql = "SELECT Member.MemberID,\n"

. " Employment.MemberID,\n"

. " Employment.EmployerID,\n"

. " Employer.EmployerID,\n"

. " Employer.CompanyName,\n"

. " Employer.CompanyNum\n"

. " FROM Member, Employment, Employer\n"

. " WHERE Member.MemberID = " . $data["MemberID"] . " AND Member.MemberID = Employment.MemberID AND Employment.EmployerID = Employer.EmployerID";
$result = CustomQuery($sql);
while($rows[]=db_fetch_array($result));

$value = "";//LEAVE THIS HERE,
foreach ($rows as $row) {

$value = $value . $row["CompanyName"] . " " . $row["CompanyNum"];

}