Caching update error

[cantlep]

Hi Steve,

I have some code in my app delegate class that allows for a "Total Number of something to be calculated". i.e I have a list of companies and everytime I create a new site that has that company name assigned to it then in my "Customers" table, it auto updates the number to show the totals. Here's the code: (Code also shows equivs for other totals)

Code: Select all

// Allow the SiteData cache table to be grafted to the Customers table
// to show number of sites each customer has
function beforeHandleRequest(){
    $modification_times = Dataface_Table::getTableModificationTimes();
    if ( $modification_times['CustomerStats'] < $modification_times['SiteData'] ){
        $res = mysql_query("replace into CustomerStats (CompanyName, TotalSites) select CompanyName, count(*) as TotalSites from SiteData group by CompanyName", df_db());
        if ( !$res ) throw new Exception(mysql_error(df_db()));
    }
// Allow the ContractData cache table to be grafted to the Customers table
// to show number of Contracts each customer has
    $modification_times = Dataface_Table::getTableModificationTimes();
    if ( $modification_times['ContractStats'] < $modification_times['ContractData'] ){
        $res = mysql_query("replace into ContractStats (CompanyName, TotalContracts) select CompanyName, count(*) as TotalContracts from ContractData group by CompanyName", df_db());
        if ( !$res ) throw new Exception(mysql_error(df_db()));
    }
// Allow the MPANData cache table to be grafted to the Customers table
// to show number of MPANs each customer has
    $modification_times = Dataface_Table::getTableModificationTimes();
    if ( $modification_times['MPANStats'] < $modification_times['MPANData'] ){
        $res = mysql_query("replace into MPANStats (CompanyName, TotalMPANs) select CompanyName, count(*) as TotalMPANs from MPANData group by CompanyName", df_db());
        if ( !$res ) throw new Exception(mysql_error(df_db()));
    }
}


Adding in new objects works fine and makes the counter increase...however, when I remove an object (e.g. delete a site), the figure doesn't go down. The only way I can get the figure to go down is to remove it from the caching table (i.e. CustomerStats). I think this used to work fine but I can't be 100%

Does everything look OK to you?

Cheers

[cantlep]

ah, I've found the issue. It's working fine....until....the counter is supposed to display "0". If I add new contracts, MPANs, or Sites, the counter increases (as expected). When I remove them, it also decreases (also as expected)..but when I remove all records so that the table is empty, all figures say "1" rather than "0". Is there some checking that can be added to each section to say if table = empty then display 0, type of thing?

Thanks