[SOLVED] summing another table row

[tusa]

Am I doing anything wrong her:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

$myQuery
    ->select('SUM(elementer_duration)')
    ->from('nemmanus_elementer')
    ->where('elementer_manus_id = ' . $myDb->quote('{nemmanus_manus___manus_titel}'));

$myDb->setQuery($myQuery);
$sumDur = $myDb->loadResult();
return $sumDur;

Just returns an empty field.

 

[lousyfool]

Am I doing anything wrong her:

If there should be a result: yes, then you're doing something wrong here.
Review your code for typos and/or syntax errors.
Watch out for error messages (J!'s error reporting set to max).
Use var_dump($xyz);exit; to check and verify variables and "in between" results.
Always do so carefully before posting here ... it'll save you and us a lot of posts... thank you.

 

[tusa]

If there should be a result: yes, then you're doing something wrong here.
Review your code for typos and/or syntax errors.
Watch out for error messages (J!'s error reporting set to max).
Use var_dump($xyz);exit; to check and verify variables and "in between" results.
Always do so carefully before posting here ... it'll save you and us a lot of posts... thank you.

Do you se any typos and/or syntax errors?
Do you have an example how to use var_dump?

 

[lousyfool]

From your other thread, you now know that you cannot simply add up time fields such as elementer_duration without some conversion first.

Syntax: if in doubt, see https://docs.joomla.org/Selecting_data_using_JDatabase for proper J! query syntax.

And for PHP functions, Google or W3Schools or similar are your friends.

 

[tusa]

Now I use this code:

// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

// Select all records from the nemmanus_elementer table where elementer_manus_id same as manus_titel.
$query->select($db->quoteName(array('elementer_duration')));
$query->from($db->quoteName('nemmanus_elementer'));
$query->where($db->quoteName('elementer_manus_id') . ' = ' . $db->quote('{nemmanus_manus___manus_titel}'));

// Reset the query using our newly populated query object.
$db->setQuery($query);

// Load the results as a list of stdClass objects (see later for more options on retrieving data).
$results = $db->loadObjectList();

var_dump($results);
exit;

Then I see this text on page:
array(0) { }

does that seems right?

 

[lousyfool]

1. loadObjectList() already creates an object (= sort of array) of the results. So, your "select" for a single column should probably look like

$query->select($db->quoteName('elementer_duration'));

2. Your "where" condition compares with a placeholder from another list/table. Are you sure that this can work at all? Simply put, it can only work if this code is used in e.g. a calc element of the nemmanus_manus list. Elsewhere it might not produce result, indeed.

 

[troester]

var_dump your placeholder
var_dump the query
If placeholders are working at all here you may have to use _raw (depending on your element type, details link settings etc).

 

[tusa]

var_dump your placeholder
var_dump the query
If placeholders are working at all here you may have to use _raw (depending on your element type, details link settings etc).

When I use
var_dump('{nemmanus_manus___manus_titel}');
I get:

string(12) "Udsendelse 1"

When I use
var_dump ($query)
I get:

object(JDatabaseQueryMysqli)#671 (25) { ["offset":protected]=> int(0) ["limit":protected]=> int(0) ["db":protected]=> object(JDatabaseDriverMysqli)#23 (23) { ["name"]=> string(6) "mysqli" ["serverType"]=> string(5) "mysql" ["connection":protected]=> object(mysqli)#24 (18) { ["affected_rows"]=> int(0) ["client_info"]=> string(14) "mysqlnd 7.4.28" ["client_version"]=> int(70428) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["field_count"]=> int(1) ["host_info"]=> string(25) "Localhost via UNIX socket" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(29) "5.5.5-10.3.32-MariaDB-cll-lve" ["server_version"]=> int(100332) ["sqlstate"]=> string(5) "00000" ["protocol_version"]=> int(10) ["thread_id"]=> int(19247944) ["warning_count"]=> int(0) } ["nameQuote":protected]=> string(1) "`" ["nullDate":protected]=> string(19) "0000-00-00 00:00:00" ["_database":"JDatabaseDriver":private]=> string(29) "tusadk_nemmanus-midlertidigdk" ["count":protected]=> int(10) ["cursor":protected]=> NULL ["debug":protected]=> bool(false) ["limit":protected]=> int(0) ["log":protected]=> array(0) { } ["timings":protected]=> array(0) { } ["callStacks":protected]=> array(0) { } ["offset":protected]=> int(0) ["options":protected]=> array(9) { ["driver"]=> string(6) "mysqli" ["host"]=> string(9) "localhost" ["user"]=> string(29) "tusadk_nemmanus-midlertidigdk" ["password"]=> string(18) "xxxxxxxxxxxxxxxxxx" ["database"]=> string(29) "tusadk_nemmanus-midlertidigdk" ["prefix"]=> string(6) "d61h5_" ["select"]=> bool(true) ["port"]=> int(3306) ["socket"]=> NULL } ["sql":protected]=> *RECURSION* ["tablePrefix":protected]=> string(6) "d61h5_" ["utf":protected]=> bool(true) ["utf8mb4":protected]=> bool(true) ["errorNum":protected]=> int(0) ["errorMsg":protected]=> string(0) "" ["transactionDepth":protected]=> int(0) ["disconnectHandlers":protected]=> array(0) { } } ["sql":protected]=> NULL ["type":protected]=> string(6) "select" ["element":protected]=> NULL ["select":protected]=> object(JDatabaseQueryElement)#672 (3) { ["name":protected]=> string(6) "SELECT" ["elements":protected]=> array(1) { [0]=> string(20) "`elementer_duration`" } ["glue":protected]=> string(1) "," } ["delete":protected]=> NULL ["update":protected]=> NULL ["insert":protected]=> NULL ["from":protected]=> object(JDatabaseQueryElement)#673 (3) { ["name":protected]=> string(4) "FROM" ["elements":protected]=> array(1) { [0]=> string(20) "`nemmanus_elementer`" } ["glue":protected]=> string(1) "," } ["join":protected]=> NULL ["set":protected]=> NULL ["where":protected]=> object(JDatabaseQueryElement)#674 (3) { ["name":protected]=> string(5) "WHERE" ["elements":protected]=> array(1) { [0]=> string(37) "`elementer_manus_id` = 'Udsendelse 1'" } ["glue":protected]=> string(5) " AND " } ["group":protected]=> NULL ["having":protected]=> NULL ["columns":protected]=> NULL ["values":protected]=> NULL ["order":protected]=> NULL ["autoIncrementField":protected]=> NULL ["call":protected]=> NULL ["exec":protected]=> NULL ["union":protected]=> NULL ["unionAll":protected]=> NULL ["selectRowNumber":protected]=> NULL }

 

[lousyfool]

So then, do as advised:
• Try appending _raw to your placeholder in the "where" condition, like so: {nemmanus_manus___manus_titel_raw}.
Because "elementer_manus_id" is an integer ID, not a string, right?
• Not that crucial, but simplifying things:

1. loadObjectList() already creates an object (= sort of array) of the results. So, your "select" for a single column should probably look like

$query->select($db->quoteName('elementer_duration'));

Then see if it works, or do your var_dump checks again.

 

[tusa]

"nemmanus_elementer___elementer_manus_id" is a databasejoin to "nemmanus_manus___manus_id" (inetrenalid) and uses "nemmanus_manus___manus_title" (field) as label

But how can I get data from the array?
I expect to get some durations (H:i:s) that I want to add together.

 

[lousyfool]

Obviously, your "where" clause doesn't/can't work.
Not sure what it's supposed to do, but what if you do the query without the "where" clause? Guess a var_dump of $results will get you a number of values, right?
So, you need to think carefully of how to formulate the "where" clause depending on your needs. (While please keeping an eye on syntax, correct placeholders, etc...)

 

[tusa]

Obviously, your "where" clause doesn't/can't work.
Not sure what it's supposed to do, but what if you do the query without the "where" clause? Guess a var_dump of $results will get you a number of values, right?
So, you need to think carefully of how to formulate the "where" clause depending on your needs. (While please keeping an eye on syntax, correct placeholders, etc...)

Stupid me!
Found the reason... I learned something today!
The databasejoin was pointing to an internalid field, only the label was text field with the title, but in the table it stores the id.
The "where" clause works fine now