2. PHPRunner General questions
  3. Query Problems
This topic is locked

Query Problems

 		3/18/2006 12:59:54 PM
PHPRunner General questions
B
 BluePhoenixNC author

I am trying to show only one (the last added record) in a child table. To make it a little more specific, here is the setup: Mastertable -> ChildTable, linked via AccountNr. When I go to the Child Table, I only want to show the last entry for the Account. I tried to use the following customized SQL Query, but it does not appear to work:

select `ID`,

`Account_Number`,

`Date`,

`Amount`

From `Customer_Payments`

ORDER BY 'Date' DESC

LIMIT 1


How can I achieve that ? Any help is as usual greatly appreciated.

=============================================

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I think I fixed it myself I just went into the generated source and chenged this section

$numrows=GetRowCount($strSQL);

if($numrows)

{

//$maxRecords = $numrows;

//$maxpages=ceil($maxRecords/$nPageSize);

//if($mypage > $maxpages)

// $mypage = $maxpages;

//$maxrecs=$nPageSize;

$strSQL.=" limit 1";

//$strSQL.=" limit ".(($mypage-1)*$nPageSize).",".$nPageSize;

}


But now the calculated totals (Amount) only shows the last amount, too.... I would like for it to still calculate the totals for that customer, regardless of how many rows are displayed.... how can I best do that ?
Sergey Kornilov admin 3/20/2006

Hi,
The totals and data rows use the same query. So if you modify SQL srting for records displaying, totals will change as well.
If you want to display records and to calculate totals independently, you can use the following code snippet:

$totalrs = db_query("select sum(amount) from `Customer_Payments` where account='".$_SESSION[$strTableName."_masterkey"]."'",$conn);

$totaldata=db_fetch_numarray($totalrs);



Then echo $totaldata[0] .