2. PHPRunner General questions
  3. Fault on Number of records on complex queries
This topic is locked

Fault on Number of records on complex queries

 		10/29/2007 11:23:39 AM
PHPRunner General questions
S
 smaris author

Hi,
i'm using quiet a lot of complex SQL statements, especially to create overview summaries. These queries quite often contain one or more joins over different tables and do contain "group by" statements on calculated fields (mostly SQL SUM fields); eg:
"Select pm_projects.projid, pm_domains.pdomain, pm_projects.title, pm_projects.typeid, pm_customers.custname, pm_projects.budgetf, pm_projects.closed, SUM(pm_invoices.rvalue), pm_projects.budgetf As pl1, pm_projects.budgetf As pl2

From pm_invoices Inner Join pm_projects ON pm_invoices.projid = pm_projects.projid Inner Join pm_customers on pm_projects.custid = pm_customers.custID Inner Join pm_domains on pm_projects.pdomain=pm_domains.pdomainid

Where pm_projects.ordered=1

Group By pm_projects.projid"
The number of records returned by the functions 'GetRowCount' and 'gSQLRowCount' are not correct. My guess is that there is an interference between the count() and the SUM clause in when running the SQL in these functions.
I've currently bypassed this problem by running the real full query in the above functions, and returning the number of records found (db_numrows). I know this is less perfomant on large datasets, but it does return the correct number of records found. I've modified this in the phpRunner/source implementation of 'commonfunctions' to maintain this fix on rebuilding projects.
I suggest either to choose for the 'safe' way and perform the full query in the above functions, or to have a proper, non-conflicting count() implementation (or either a combination of both). Unfortunately I do not have the time myself (yet) to re-implement the functions.
Regards
Stefan
Sergey Kornilov admin 10/29/2007

Stefan,
you can post your application to Demo Account (last tab in PHPRunner) and send the URL to support@xlinesoft.com for investigation.
M
 		mlichtenstein2 10/28/2009

Here is a good sulution to the COUNT() problem. A SELECT () with a GROUP BY will give you the count of all the detail records that make up the GROUP BY and

not the number of rows returned. So simply look for GROUP BY in the query and call a custom function to get the real count. Put this in the commonfunctions.php file:
// get count of rows from the query

function GetRowCount($strSQL)

{

global $conn;

$strSQL=str_replace(array("\r\n","\n","\t")," ",$strSQL);

$tstr = strtoupper($strSQL);

// For queries with a SUM and GROUP BY use gSQLRowCountGroupBy.

// gSQLRowCountGroupBy does not use "SELECT COUNT() " which will

// count all records that make up a group of records, but counts the

// records returned from a GROUP BY.

$pos = strpos($tstr,"GROUP BY"); // Does the query have a GROUP BY?

if ($pos > 0)

{

$numrows=gSQLRowCountGroupBy($tstr);

return $numrows;

}

$ind1 = strpos($tstr,"SELECT ");

$ind2 = my_strrpos($tstr," FROM ");

$ind3 = my_strrpos($tstr," GROUP BY ");

if($ind3===false)

{

$ind3 = strpos($tstr," ORDER BY ");

if($ind3===false)

$ind3=strlen($strSQL);

}

$countstr=substr($strSQL,0,$ind1+6)." count() ".substr($strSQL,$ind2+1,$ind3-$ind2);

$countrs = db_query($countstr,$conn);

$countdata = db_fetch_numarray($countrs);

return $countdata[0];

}
// Get the row count using "GROUP BY"

// Loop to get the number of rows.

function gSQLRowCountGroupBy($strSQL)

{

global $conn;

//echo "gSQLRowCountGroupBy: strSQL = ". $strSQL. "
";

$rs = db_query($strSQL,$conn);

$count = 0;

// Loop to get the number of rows because COUNT(*) will return

// the total number of records that make up the GROUP BY and not

// the number of rows returned with the query.

while($data=db_fetch_numarray($rs))

{

$count = $count + 1;

}

//echo "gSQLRowCountGroupBy: returning ". $count. "
";

return $count;

}