[solved] No 'MONTH(date_field)' in $this->db->where(); possible?

[eluser]Jan_1[/eluser]
Good Morning,
don't understand what's going wrong (as often... :down
Would be nice to get a hint by someone. Thank you!!!

works fine in PHPMyAdmin:

Code:

SELECT COUNT( ks_betrag ) AS anzahl, SUM( ks_betrag ) AS summe
FROM `ks_buchung`
WHERE wg_id =34
AND MONTH( ks_datum ) = MONTH( CURDATE( ) )

doesn't work in CI as active record:

A Database Error Occurred
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `wg_id` = '34' AND MONTH(ks_datum) = 'MONTH(CURDATE())'' at line 2

SELECT COUNT(ks_betrag) AS anzahl, SUM(ks_betrag) AS summe WHERE `wg_id` = '34' AND MONTH(ks_datum) = 'MONTH(CURDATE())'

Thats the function in my model:

Code:

$wg_id = 34;

$select = array('COUNT(ks_betrag) AS anzahl','SUM(ks_betrag) AS summe');
$where1 = array('wg_id'=>$wg_id, 'YEAR(ks_datum)'=>'YEAR(CURDATE())');
$where2 = array('wg_id'=>$wg_id, 'MONTH(ks_datum)'=>'MONTH(CURDATE())');
$where3 = array('wg_id'=>$wg_id, 'MONTH(ks_datum)'=>'MONTH(SUBDATE(CURDATE(), INTERVAL 1 MONTH)');
$this->db->from('ks_buchung');

$this->db->select($select);
$this->db->where($where1);
$query = $this->db->get();
if ($query->num_rows() > 0) { $row = $query->row(); }
$summe_jahr = $row->summe;
$anzahl_jahr = $row->anzahl;

$this->db->select($select);
$this->db->where($where2);
$query2 = $this->db->get();
if ($query2->num_rows() > 0) { $row2 = $query->row(); }
$dieser_monat = $row2->summe;
$anzahl_dieser = $row2->anzahl;

$this->db->select($select);
$this->db->where($where3);
$query3 = $this->db->get();
if ($query3->num_rows() > 0) { $row3 = $query->row(); }
$letzter_monat = $row3->summe;
$anzahl_letzter = $row3->anzahl;

$data = array(
              'summe_jahr' => $summe_jahr,
              'dieser_monat' => $dieser_monat,
              'letzter_monat' => $letzter_monat,
              'anzahl_jahr' => $anzahl_jahr,
              'anzahl_dieser' => $anzahl_dieser,
              'anzahl_letzter' => $anzahl_letzter
             );
      return $data;

[eluser]Armchair Samurai[/eluser]
You need to add FALSE as the third parameter to stop CI escaping your query, in which case you should escape $wg_id before letting AR creating the query.

[eluser]Jan_1[/eluser]
Thank you, Armchair Samurai.
And sorry, I did'nt get it.

I've tried

Code:

$where1 = array('wg_id'=>$wg_id, 'YEAR(ks_datum)'=>'YEAR(CURDATE())',FALSE);
$where2 = array('wg_id'=>$wg_id, 'MONTH(ks_datum)'=>'MONTH(CURDATE())',FALSE);
$where3 = array('wg_id'=>$wg_id, 'MONTH(ks_datum)'=>'MONTH(SUBDATE(CURDATE(), INTERVAL 1 MONTH)',FALSE);

and the browser answered

A Database Error Occurred
Error Number: 1054
Unknown column '0' in 'where clause'
SELECT COUNT(ks_betrag) AS anzahl, SUM(ks_betrag) AS summe FROM (`ks_buchung`) WHERE `wg_id` = '34' AND YEAR(ks_datum) = 'YEAR(CURDATE())' AND `0` = 0

what I do understand (me idiot...).
So I tried

Code:

$this->db->select($select);
$this->db->where($where1, FALSE);  //<- also '($where1, Null, FALSE)'
$query = $this->db->get();
if ($query->num_rows() > 0) { $row = $query->row(); }
$summe_jahr = $row->summe;
$anzahl_jahr = $row->anzahl;

...
$this->db->where($where2, FALSE);
...
$this->db->where($where3, FALSE);
...

and the browser answered:

A Database Error Occurred
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `wg_id` = '34' AND MONTH(ks_datum) = 'MONTH(CURDATE())'' at line 2
SELECT COUNT(ks_betrag) AS anzahl, SUM(ks_betrag) AS summe WHERE `wg_id` = '34' AND MONTH(ks_datum) = 'MONTH(CURDATE())'

So, how do I have to place it?
I haven't find an example with array in the User Guide

[eluser]Armchair Samurai[/eluser]
AR tries too escape everything, so if you have any database functions in your query, they become corrupted if run through AR. However, if you add FALSE as the third parameter to where, CI will not escape things, so try something like this:

Code:

$wg_id = $this->db->escape(34);

$where1 = array('wg_id'=>$wg_id, 'YEAR(ks_datum)'=>'YEAR(CURDATE())');

$this->db->select($select);
$this->db->from('ks_buchung');
$this->db->where($where1, NULL, FALSE);
$query = $this->db->get();

Now, I've never tried this with arrays, so the third parameter might not work. In that case, you'll need to type out the query:

Code:

$this->db->where('YEAR(ks_datum) = YEAR(CURDATE())', NULL, FALSE);

I just noticed this as well (hooray fro reading skills!): you are missing the from statement which is a major reason why MySQL is complaining!

I hope you understand this as I need to go... I'll elaborate later when I get the time.

[eluser]Jan_1[/eluser]
Thanks a lot, Armchair Samurai!
So,... :
A) I'm not able to put a FALSE to the arguments in the WHERE-Array
B) Everyone of my queries needs a FROM-Information

Code:

function get_ks_summen()
    {
      $wg_id = 34; //short version. normaly done by a sql-query
      $db = "ks_buchung";
      $select = array('COUNT(ks_betrag) AS anzahl','SUM(ks_betrag) AS summe');
// query 1 (this year)
      $this->db->select($select);
      $this->db->where('wg_id', $wg_id);
      $this->db->where('YEAR(ks_datum)','YEAR(CURDATE())', FALSE);
      $this->db->from($db);
      $query1 = $this->db->get(); if ($query1->num_rows() > 0) { $row1 = $query1->row(); }
      $summe_jahr = $row1->summe;
      $anzahl_jahr = $row1->anzahl;
//query 2 (this month)
      $this->db->select($select);
      $this->db->where('wg_id', $wg_id);
      $this->db->where('MONTH(ks_datum)','MONTH(CURDATE())', FALSE);
      $this->db->from($db);
      $query2 = $this->db->get(); if ($query2->num_rows() > 0) { $row2 = $query2->row(); }
      $dieser_monat = $row2->summe;
      $anzahl_dieser = $row2->anzahl;
// query 3 (last month)
      $this->db->select($select);
      $this->db->where('wg_id', $wg_id);
      $this->db->where('MONTH(ks_datum)','MONTH(SUBDATE(CURDATE(), INTERVAL 1 MONTH))', FALSE);
      $this->db->from($db);
      $query3 = $this->db->get(); if ($query3->num_rows() > 0) { $row3 = $query3->row(); }
      $letzter_monat = $row3->summe;
      $anzahl_letzter = $row3->anzahl;

      $data = array(
                    'summe_jahr' => $summe_jahr,
                    'dieser_monat' => $dieser_monat,
                    'letzter_monat' => $letzter_monat,
                    'anzahl_jahr' => $anzahl_jahr,
                    'anzahl_dieser' => $anzahl_dieser,
                    'anzahl_letzter' => $anzahl_letzter
                   );
      return $data;
    }

If somebody finds out how to bring FALSE-Argument in an Where-Array pleeeaase contact me.