Live Update And Yii Framework

Hi all.

I’m working in a small web application where I have to display some sensors data, static and dynamic

I’m using YII as php framework. for the static it’s ok I can display 5 series from different 5 sensors in the same chart with this code

(user should choose a rand of time and the data will be fetched from the corresponding mysql table)

YII controller





public function actionInfo()

	{

		if(isset($_POST['beamId']))

			$beamId = $_POST['beamId'];

			

		$modelBeam=Beam::model()->findBySql('SELECT * FROM poutre where poutre_id = "'.$beamId.'"');

		

		if(count($modelBeam)<1)

			throw new CHttpException(' : Beam not found !','Make sure that you entered a valid beam');

			

		$modelResponsible=Responsible::model()->findBySql('SELECT * FROM responsable where responsable_id = "'.$modelBeam['responsable_id'].'"');		

        $modelHistory=BeamHistory::model()->findAllBySql('SELECT * FROM historique_poutre where poutre_id = "'.$beamId.'" ORDER BY  historique_poutre_date DESC ');		

		

  

		$this->render('info',array('modelBeam'=>$modelBeam,'modelResponsible'=>$modelResponsible,'modelHistory'=>$modelHistory,));

	}




public function actionGetSensorsData($beamId,$sDate,$eDate)

	{

		$result = array();

	

		header('Content-Type: application/json; charset="UTF-8"');

		

		// get all sensors 

		$sensorsIds=Sensor::model()->findAllBySql('SELECT capteur_id FROM capteur where poutre_id="'.$beamId.'";');

		

		foreach ($sensorsIds as $s)

		{	

		

		$sResult = array();

		

			

			$modelSensors = SensorInfo::model()->findAllBySql('SELECT * FROM information_capteur  where capteur_id= "'.$s['capteur_id'].'" and


			"'.$sDate.'" <= information_capteur_temps and 

			

			information_capteur_temps <= "'.$eDate.'" 

			

			order by information_capteur_temps ASC;');

			

			

			foreach($modelSensors as $res){

			

			              	$sResult[] = array(($res['information_capteur_temps']),intval($res['information_capteur_valeur']));

			                                }

											

			$data= array('data'=>$sResult);

			$name = array('name'=>$s['capteur_id']);

			$result[]=array('name'=>$s['capteur_id'],'data'=>$sResult);

			

			

	    }	

			 

		$json = CJSON::encode($result);

		echo $json ;

		Yii::app()->end();		

		

	}



and in my view info.php




$(document).ready(function(){	

	var startDateTextBox = $('#startDate');

	var endDateTextBox = $('#endDate');

	$("#btnSubmit").click(function() {

		if(startDateTextBox.val()!="" && endDateTextBox.val()!=""){

			$.ajax({

				type: "get",

				url: "<?php echo CController::createUrl('beam/GetSensorsData'); ?>",

				data: {"beamId" : "<?php echo $modelBeam['poutre_id'] ;?>" , "sDate": $('#startDate').val() , "eDate": $('#endDate').val()},

				dataType:"json",

				success: function(response, status) {					

					var chart;						

					chart = new Highcharts.Chart({

					 

						chart: {

						 

							renderTo: 'container',

							type: 'spline'

						},

						title: {

							text: 'Snow depth in the Vikjafjellet mountain, Norway'

						},

						subtitle: {

							text: 'An example of irregular time data in Highcharts JS'

						},

						xAxis: {

							type: 'datetime',

							  dateTimeLabelFormats: { 

							  second: '%H:%M:%S',

	                          minute: '%H:%M',

	                          hour: '%H:%M',

								 // month: '%e. %b',

								  //year: '%b'

							 },

							tickPixelInterval: 150,

				             maxZoom: 10 * 1,

						   

						},

						yAxis: {

							title: {

								text: 'Snow depth (m)'

								 

							},

						 

						},

						tooltip: {

							formatter: function() {

									return '<b>'+ 'Lead' + this.series.name +'</b><br/>'+

									Highcharts.dateFormat('%H:%M:%S:%m',this.x) +'<br/>'+

									Highcharts.numberFormat(this.y,0);

							}

						},

						

						series: response

					});						

				},

				error: function (response, status) {

						alert("An error occured while loading data!");

				}

			});

		} 

		

		else 

		

		{

			alert("Choose date range!")

		       }

	});

//


////



for dynamic update using ajax call (from only one sensor ) I get always an error with this code

error here PHP Error [8] Undefined index: information_capteur_temps , information_capteur_temps is the column where I store the time

controller




public function actionLive()

	{

		if(isset($_POST['beamId']))

			$beamId = $_POST['beamId'];

			

		$modelBeam=Beam::model()->findBySql('SELECT * FROM poutre where poutre_id = "'.$beamId.'"');

		

		if(count($modelBeam)<1)

			throw new CHttpException(' : Beam not found !','Make sure that you entered a valid beam');

			

			

			

			$modelSensors = SensorInfo::model()->findAllBySql('SELECT * FROM information_capteur where  capteur_id= "I" order by information_capteur_temps ASC;' );

			

			

		 

		

		 $this->render('live',array('modelSensors'=>$modelSensors ,'modelBeam'=>$modelBeam)) ;

	}

public function actionGetSensorsDataLive($beamId,$sensId='I')

	{

		$result = array();

	

		header('Content-Type: application/json; charset="UTF-8"');

	 

	 	$sResult = array();

		

			$modelSensors = SensorInfo::model()->findAllBySql('SELECT * FROM information_capteur  where  capteur_id= "I" ');

			

	 

			 $sResult[] = array(($modelSensors['information_capteur_temps']),intval($modelSensors['information_capteur_valeur'])); // error here  PHP Error [8] Undefined index: information_capteur_temps

		 	 

			

			$data= array('data'=>$sResult);

			

			//$name = array('name'=>$s['capteur_id']);

			

						$result[]=array('data'=>$sResult);


	   

	

			 	

		$json = CJSON::encode($result);

		echo $json ;

		Yii::app()->end();		

		

	}






my view live.php




 function requestData() {

     $.ajax ({

                type:"get" ,

				url: "<?php echo CController::createUrl('beam/GetSensorsDataLive'); ?>",

				data: {"beamId" : "<?php echo $modelBeam['poutre_id'] ;?>"},

			 	dataType: "json",

				

              success: function(response,point) {

              

              var series = chart.series[0],

			  

			  shift = series.data.length > 20;// shift if the series is longer than 20

              chart.series[0].addPoint( eval(point),true, false); // add the point

              setTimeout(requestData, 1000);    // call it again after one second

           

                                       },

                              

          cache: false

        });

       

		  }  

		  

		

	 

	  $(document).ready(function() {

	  	 var chart;

		 

            chart = new Highcharts.Chart({

            chart: {

                renderTo: 'graph',

                type: 'spline',

                marginRight: 70,

                events: {

                    load: requestData   

                }

            },

         

         title: {

                text: 'ID 1000000 sensors 0'

            },

            xAxis: {

              type: 'datetime',

                //tickPixelInterval: 100

			tickPixelInterval: 150,

				maxZoom: 10 * 1


            },

            yAxis: {

                title: {

                    text: 'Peaks Values'

                },

				

              /*     plotLines: [{


                    value: 0,


                    width: 1,


                    color: '#808080'


                }]; */


            },

			

			  tooltip: {


                formatter: function() {


                        return '<b>'+ this.series.name +'</b><br/>'+


                        Highcharts.dateFormat('%H:%M:%S:%m', this.x) +'<br/>'+


                        Highcharts.numberFormat(this.y, 2);


                }

       

            },  

		 

			

                        series: [{

                name: 'Live Data   ',

                 data:[] 

            }]

         

        });

 


    });



Any help or comments would be appreciated

I’m pretty sure that is related to my db query but I couldn’t find it

many thanks in advance

Hi, if I understood correctly, you are getting error because in the second controller you are accessing a field directly from an array of models ($modelSensors) which is wrong! you should write the same code as you did in your first controller: Iterate through $modelSensors by a foreach statement and fetch ‘information_capteur_temps’ of each model and add it to $sResult array. Then you can do anything you like with $sResult. something like this:




public function actionGetSensorsDataLive($beamId,$sensId='I')

{

 .....

 $modelSensors = SensorInfo::model()->findAllBySql('SELECT * FROM information_capteur  where  capteur_id= "I" ');

 foreach($modelSensors as $res){

  $sResult[] = array(($res->information_capteur_temps),intval($res->information_capteur_valeur));

 }

 .....



As a side note your SQL queries are wide open to injection attacks. I suggest to use CDbCriteria instead and use its params property.

Many thanks faridplus for your comment and suggestion

I fixed the error as you requested but is not really what I want to have as output for this querie.

In my DB I have a sensors table that contains 5 sensors and a information sensors table that contains the data,with the fixed code I cant get all data for just one sensors




public function actionGetSensorsDataLive($beamId )

	{

		$result = array();

	

		 header('Content-Type: application/json; charset="UTF-8"');

		

		$sensorsIds=Sensor::model()->findAllBySql('SELECT capteur_id FROM capteur where poutre_id="'.$beamId.'" and capteur_name = "sensors_1" ;' );

	 

	 foreach ($sensorsIds as $s)

		{	

	 	$sResult = array();

		

	$modelSensors = SensorInfo::model()->findAllBySql('SELECT * FROM information_capteur  where capteur_id= "'.$s['capteur_id'].'" order by information_capteur_temps ASC;');

			

	 

			 foreach($modelSensors as $res){

			  

        $sResult[] = array(($res['information_capteur_temps']),intval($res['information_capteur_valeur']));

             

		 	 }

			$data= array('data'=>$sResult);

			$name = array('name'=>$s['capteur_id']);

			$result[]=array('name'=>$s['capteur_id'],'data'=>$sResult);

	   }

	 	

		$json = CJSON::encode($result);

		echo $json ;

		

		Yii::app()->end();		

		

	}






[{"name":"sensor_1","data":[["2012-12-01 15:00:00.070",45],["2012-12-01 15:00:00.281",95],["2012-12-01 15:00:00.375",-825],["2012-12-01 15:00:00.422",1496],["2012-12-01 15:00:00.469",-776],["2012-12-01 15:00:00.578",39],["2012-12-01 15:00:00.883",104] ........]}]



but I want to get just the last entry in this table for the corresponding sensors like this.




[{"name":"sensor_1","data":[["2012-12-01 15:00:00.070",45]}]



that means at every ajax call the this function I get as output the last point in my DB.

I tried to add a while loop but I get error.

any idea ?

Regarding the sql injection , I’m really newbie (it’s my first php project ::) )and I tried to follow the description that you gave me but it seems difficult !

Thanks again

I’m confused you first said that you can’t get all data for just one sensors but then you just want the last entry! if you want the entry with the oldest date like this [2012-12-01 15:00:00.070] compared to [2012-12-01 15:00:00.281] or others then you didn’t need ‘findAllBySql’ at the first place, it’s better to use 'findBySql’ to just get one row from DB table.If I’m right then forget the whole thing i just said about foreach statement and use this code instead:




public function actionGetSensorsDataLive($beamId )

        {

                $result = array();

        

     			header('Content-Type: application/json; charset="UTF-8"');

                

                $sensorsIds=Sensor::model()->findAllBySql('SELECT capteur_id FROM capteur where poutre_id="'.$beamId.'" and capteur_name = "sensors_1" ;' );

 		

 		foreach ($sensorsIds as $s)

                {   	

                $sResult = array();

                

        $modelSensor = SensorInfo::model()->findBySql('SELECT * FROM information_capteur  where capteur_id= "'.$s['capteur_id'].'" order by information_capteur_temps ASC;');                                                          

                          

        $sResult[] = array($modelSensor->information_capteur_temps,intval($modelSensor->information_capteur_valeur));                                      

        $name = array('name'=>$s['capteur_id']);

        $result[]=array('name'=>$s['capteur_id'],'data'=>$sResult);

   		}

                

                $json = CJSON::encode($result);

                echo $json ;

                

                Yii::app()->end();              

                

        }



btw I wonder why it is named ‘information_capteur_temps’ but not simply ‘information_capture_time’ !! it is more readable :)

Thanks again farid

Let me clarify, in my first post when I said I can get data I meant for all data in all sensors (in Json format), and in my second post I meant all data for just one sensors when I specified the sensor name (sensors_1).

But as I said I want just the oldest date. In other words at each Ajax call to this function I get the oldest date for the sensors that I specify (sensors_1 in my case)

Anyway, many thanks for your help it’s work and I can get the oldest data

but something strange when I checked the received data with firebug, the output is




[{"name":"sensors_1","data":[["2012-12-01 15:00:00.070",45]]}]



but the format is not marked as Json.