CodeIgniter Forums

Full Version: how to generate queries based on date
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[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`)
)

Confusedtat_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

El Forum

[eluser]mddd[/eluser]
If you want to get all the data for the current month, just use
Code:
SELECT dateflag, COUNT(*) AS num_stats
FROM stat
WHERE MONTH(dateflag)=MONTH(NOW()) AND YEAR(dateflag)=YEAR(NOW())

El Forum

[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

El Forum

[eluser]mddd[/eluser]
Please excuse me, I forgot one line. The query should be
Code:
SELECT dateflag, COUNT(*) AS num_stats
FROM stat
WHERE MONTH(dateflag)=MONTH(NOW()) AND YEAR(dateflag)=YEAR(NOW())
GROUP BY dateflag

El Forum

[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

El Forum

[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