datatime filter in calc element

[mogy74]

Hi to all
i need a litle help .. again

i start reuse fabrik after long time of stop and i found a problem making a filter in a query :
i need to know the total of the "tot_uscita" field from bache out table , but i also need to filter them
from "sede" and a period set by tro datatime picker
i use calc element for do this and i have a datetime for start date the same for end date
so i set 3 varabiles as "sede" and the 2 date "start" "end"

it work until i try to do a "where" in the querry of the date , i think is by the syntax but i can't realy find the right way to write it
someone can help?

here is the code

$sede={c___sede};
$pagato=1;
$inizio= new DateTime('{c___data_di_partenza}');
$fine= new DateTime('{c___data_finale}');


$db = JFactory::getDBO();
$query = $db->getQuery(true);

$query
->select('SUM(tot_uscita)')
->from('banche_out')
->where('sede = ' . $db->quote($sede))
->where('pagato = ' . $db->quote($pagato));
->where('data_pagamento >= ' . $db->quote($inizio)) "this is the wrong line ..
->where('data_pagamento <= ' . $db->quote($fine)); "data_pagamento" is a datetime in database.

$db->setQuery($query);
$risultato = $db->loadResult();
return $risultato;

thanks to all of any help
hugs

 

[cheesegrits]

$inizio and $fine are DateTime objects, not strings. So you'll need to format them as strings, with the format that MySQL expects, in your where clause, like ... $db->quote($inizio->format('Y-m-d H:i:s'))

-- hugh

 

[mogy74]

Thanks for your reply ,
in my many try i did what you suggest also , but still don't wanna work
maybe the problem is in the data_pagamento also a datetime field , or maybe is the where <= format , i don't know
i'm going surrender ... i know it is possible because fabrik filter doing the same very well i just need to reproduce it in my list
the new code is here , i can't find any issue
$sede={c___sede};
$pagato=1;
$inizio= new DateTime('{c___data_di_partenza}');
$fine= new DateTime('{c___data_finale}');


$db = JFactory::getDBO();
$query = $db->getQuery(true);

$query
->select('SUM(tot_uscita)')
->from('banche_out')
->where('sede = ' . $db->quote($sede))
->where('pagato = ' . $db->quote($pagato));
->where('data_pagamento >= ' . $db->quote($inizio->format('Y-m-d H:i:s')))
->where('data_pagamento <= ' . $db->quote($fine->format('Y-m-d H:i:s')));

$db->setQuery($query);
$risultato = $db->loadResult();
return $risultato;

many thanks for your time

 

[mogy74]

so sorry ..... my eyes are tired .... now works i just forgot to leve ; after ->where('pagato = ' . $db->quote($pagato));

thanks for your help!!