how to generate queries based on date - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: how to generate queries based on date (/showthread.php?tid=32653) |
how to generate queries based on date - El Forum - 07-30-2010 [eluser]tusukgigi[/eluser] how to generate queries based on date. i have a stat table structure table : CREATE TABLE IF NOT EXISTS `stat` ( `stat_id` int(255) unsigned NOT NULL auto_increment, `addr_ip` varchar(100) NOT NULL, `dateflag` date NOT NULL default '0000-00-00' PRIMARY KEY (`stat_id`) ) tat_id || addr_ip || dateflag: 1 :: 127.0.0.1 :: 2010-07-22 2 :: 127.0.0.2 :: 2010-07-22 3 :: 127.0.0.3 :: 2010-07-22 4 :: 127.0.0.4 :: 2010-07-23 5 :: 127.0.0.5 :: 2010-07-24 6 :: 127.0.0.6 :: 2010-08-01 7 :: 127.0.0.7 :: 2010-08-02 8 :: 127.0.0.8 :: 2010-08-02 9 :: 127.0.0.9 :: 2010-08-02 10 :: 127.0.0.9 :: 2010-08-02 11 :: 127.0.0.9 :: 2010-08-02 I want to integrate with openflashchart and CodeIgniter and I got like this ------------------------------------------ (flash player) ------------------------------------------ x| 9| 8| 7| [22] 6| o 5| 4| 3| 2| 1| 0| -------------------0------------------------ [22] = if mouse over this is my model function getStat(){ $th = date("Y"); $bl = date("m"); $tg = date("d"); $waktu = "$th-$bl-$tg"; $sql = "SELECT dateflag, count(distinct stat_id)as jml FROM .stat where dateflag BETWEEN '$th-$bl-1' AND '$th-$bl-31'"; return $this->db1->query($sql); } this is my controller $dataStat = array(); $query = $this->MStats->getStat(); foreach ($query->result() as $row){ $date=substr($row->dateflag,-2,2); $dataStat[] = intval($date); } $default_dot = $this->OFCL->create('dot'); $default_dot->size(5)->colour('#ccc'); $line_dot = $this->OFCL->create('line'); $line_dot->set_default_dot_style($default_dot); $line_dot->set_width( 2 ); $line_dot->set_colour( '#eee' ); $line_dot->set_values( $dataStat ); $line_dot->set_key( "Line 1", 12 ); can anyone help me..please how to generate queries based on date - El Forum - 07-30-2010 [eluser]mddd[/eluser] If you want to get all the data for the current month, just use Code: SELECT dateflag, COUNT(*) AS num_stats how to generate queries based on date - El Forum - 07-30-2010 [eluser]tusukgigi[/eluser] if anyone other than this query? SELECT dateflag, COUNT(*) AS num_stats FROM stat WHERE MONTH(dateflag)=MONTH(NOW()) AND YEAR(dateflag)=YEAR(NOW()) result : dateflag | num_stats 2010-07-22 | 800 how to generate queries based on date - El Forum - 07-30-2010 [eluser]mddd[/eluser] Please excuse me, I forgot one line. The query should be Code: SELECT dateflag, COUNT(*) AS num_stats how to generate queries based on date - El Forum - 07-30-2010 [eluser]tusukgigi[/eluser] SELECT dateflag, COUNT(*) AS num_stats FROM stat WHERE MONTH(dateflag)=MONTH(NOW()) AND YEAR(dateflag)=YEAR(NOW()) GROUP BY dateflag IT'S OK........................... but,how do I enter the code in my controller: foreach ($query->result() as $row){ $dataStat[] = intval(num_stats); } i got error in my openflashchart if debug in phpmyadmin its ok and produce like this: dateflag num_stats 2010-07-22 2 2010-07-23 4 2010-07-24 1 2010-07-25 6 2010-07-26 2 2010-07-27 6 2010-07-28 3 2010-07-29 415 2010-07-30 361 how to generate queries based on date - El Forum - 08-02-2010 [eluser]tusukgigi[/eluser] help me !!! $sql = "SELECT dateflag, COUNT(dateflag) AS num_stats FROM stat WHERE MONTH(dateflag)=MONTH(NOW()) AND YEAR(dateflag)=YEAR(NOW()) GROUP BY dateflag"; return $this->db1->query($sql); echo intval($list['num_stats'])."<br />"; i got error: Fatal error: Cannot use object of type stdClass as array in D:\xampp\htdocs\learn\system\application\controllers\statistik.php on line 127 |