nusilmar
Member
Sorry to bother you all with this question but i'm struggling on making this work and still wasn't able to.
What i have is the folowing :
Table A ( id, customer, pendingvalue) - a customer table
Table B (id,customerid,docpendvalue) - a document table with a pending value per document
i'm trying to update pendingvalue on Table A ( as a sum of all docpendvalue from Table B)
This is what i'm trying to do ( on Schedule , php : )
I know that is on the query that i have the problem, what i cannot figure it out is to transform from my sql query ( tested and working) to the query string in php
My sql query is this :
What i have is the folowing :
Table A ( id, customer, pendingvalue) - a customer table
Table B (id,customerid,docpendvalue) - a document table with a pending value per document
i'm trying to update pendingvalue on Table A ( as a sum of all docpendvalue from Table B)
This is what i'm trying to do ( on Schedule , php : )
PHP:
// Get the db object
$db = JFactory::getDbo();
// Build the query ? Tables names = CASE SENSITIVE
$query = $db->getQuery(true);
$query
-> update('fab_customers AS a')
-> join('INNER','(SELECT ('customerid, SUM(docpendvalue) AS totpend') FROM ('fab_penddocs')
GROUP BY ('customerid')) AS b')
-> ON ('a.id = b.customerid')
-> set('a.pendingvalue = (b.totpend)')
$db->setQuery($query);
// Run the query
$found = (int) $db->execute();
My sql query is this :
Code:
UPDATE a
SET a.pendingvalue=b.totpend
FROM fab_customers AS a INNER JOIN
(SELECT customerid, SUM(docpendvalue) AS totpend
FROM fab_penddocs
GROUP BY customerid) AS b ON a.id = b.customerid