Help on Joomla query

[xtrgeo]

I am trying to write code for a calc element. My query is below:

$query->select('SUM(amount)')->from('sinedries')->where('id_therapeias = '.$id)->and('plirwmi = 2');

Is there something wrong with the writing?
I only get as a result the records from the first element (id_therapeias).
It seems that everything after the and is ignored!!!

I cannot find any specific examples on this kind of joomla queries according to if i need this arrow before and or something else.

Can someone help?

 

[xtrgeo]

Just in case someone want to have a llok to the whole code in the calc element..

$id='{therapeia___id_therapeias_raw}';

$db = JFactory::getDBO();  
$query = $db->getQuery(true);
$query->select('SUM(amount)')->from('sinedries')->where('id_therapeias = '.$id)->and('plirwmi = 2'); 
$db->setQuery($query);  
$paid = $db->loadResult();

return (int)$paid;

I must say that plirwmi is a radio button element where on value i have placed 1 and 2 and in label i have text. But as i can see in the database the field type is text! So...any suggestions?

 

[rackem]

This code example might help get you started:

$db =& JFactory::getDBO(); 
$db->setQuery("SELECT SUM(`column_name`) FROM `table_name`"); 
$result = $db->loadResult(); 
return $result;

It is from this thread: http://fabrikar.com/forums/showthread.php?t=24379

You may need to use "column_name_raw" to access the value of your radiobutton element.

 

[rob]

I think its the

and()

part. I think you would either do

$query->select('SUM(amount)')->from('sinedries')->where('id_therapeias = '.$id . ' and plirwmi = 2');

or

$query->select('SUM(amount)')->from('sinedries')->where('id_therapeias = '.$id)->where('plirwmi = 2');

the where method takes a second paramter which is the glue and defaults to 'AND'

see libraries/joomla/database/databasequery.php line 968 ish:

/**
     * Add a single condition, or an array of conditions to the WHERE clause of the query.
     *
     * @param   mixed   $conditions  A string or array of where conditions.
     * @param   string  $glue        The glue by which to join the conditions. Defaults to AND.
     *
     * @return  JDatabaseQuery  Returns this object to allow chaining.
     *
     * @since   11.1
     */
    public function where($conditions, $glue = 'AND')
    {
        if (is_null($this->where)) {
            $glue = strtoupper($glue);
            $this->where = new JDatabaseQueryElement('WHERE', $conditions, " $glue ");
        }
        else {
            $this->where->append($conditions);
        }

        return $this;
    }

 

[xtrgeo]

Thank u both for your answers. I did it this way finally:

$db->setQuery("SELECT SUM(`amount`) FROM `sinedries` WHERE `plirwmi` = 2 AND `id_therapeias` =".$id);

and it worked just fine. But of course I'll be getting back to this thread for any help i might need.

Thnks again!

 

[confis]

Hi,
I have try to use this code on the calc element but it does not work
please advice

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

$myQuery
    ->select('SUM(requested)')
    ->from('fab_calender')
    ->where('approve = 1');
    ->where('user_id = ' . $myDb->quote('{fab_calender___user_id_raw}'))
$myDb->setQuery($myQuery);
$requested_leave = $myDb->loadResult();
return $requested_leave;

 

[confis]

I managed

$id = '{fab_calender___user_id_raw}';
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

$myQuery->select('SUM(requested)')->from('fab_calender')->where('user_id = '.$id . ' and approve = 1');
$myDb->setQuery($myQuery);
$requested_leave = $myDb->loadResult();
return $requested_leave;

 

[cheesegrits]

Not that this is a 5 year old thread in an unused 'testing' forum. Probably best to post new questions in the Standard forum.

-- hugh