2. PHPRunner General questions
  3. display count field from other table in a list
This topic is locked

display count field from other table in a list

 		6/20/2011 05:20:33
PHPRunner General questions
M
 muzzoshah author

hi everyone,
I'm using PHPRunner 5.3

My Intention is to create of report :
i have a list of table A which is related to table B on DeptID.

On the last column of the list, i would like to display Count value from table B which has the same DeptID as listed in the row of the list.

Which mean that my current view is like this :
Dept Name | Year | Month | Total Services Added (for the month) | Total Services supposed to be added for the month (get from count value at table <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=17218&image=1&table=forumtopics' class='bbc_emoticon' alt='B)' />
My problem is i can't do that in query, i try to use event but have no idea on how to do it. Please help guide me.
Thank you.
C
 		cgphp 6/20/2011

muzzoshah,
you have to add two fields to the SQL SELECT in the query of table A and make them to appear only in the list page.
Then, set them as custom field to retrieve the total values from the table B.
Cheers

CGphp
M
 		muzzoshah author 6/20/2011

Thanks cgphp,
However there is some mistake on the list display explanation. Please refer below..
Dept Name | Year | Month | Total Services Added (get from count value at table A base on 4 combined PK = Services ID+DeptID+Year+Month) | Total Services supposed to be added for the month (get from count value at table B which has same DeptID with Table A )
Your idea to use custom field is good, however when i add Table B in Table A Query, the count of Total Services Added getting crazy.
Roughly what is my idea is like this on what i done on hardcode PHP..
while (table A not end) {

display Dept Name

display Year

display Month

display COUNT(ServiceID)

display count of Table B related to DeptID

(SELECT COUNT(id) FROM table B where DeptID=TableA.DeptID)

}end
Hopefully it helps..however how do use custom field without added the Table B in query Table A? Please help me on example..
This is sample of my custom field created under the loop of list from Table A :
$sql = "select count(id) as c from t_Table B where DeptID=$DeptID_value";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

echo "Number of Real Services: " . $data["c"];
then i got error



muzzoshah,
you have to add two fields to the SQL SELECT in the query of table A and make them to appear only in the list page.
Then, set them as custom field to retrieve the total values from the table B.
Cheers

CGphp
C
 		cgphp 6/21/2011

Custom fields are in Table_A. Right ?
To access a field of Table_A in the "custom field" code you use $data['FieldName'].
Your query should be:

$sql = "select count(id) as c from `t_Table B` where DeptID=".$data['DeptID'];

$rs = CustomQuery($sql);

$record = db_fetch_array($rs);

echo "Number of Real Services: " . $record["c"];


If you have spaces in the table name, you have to use ` as enclosure.
Cheers

CGphp