[DonVito]

hi all,

I want to update a table from a select result , I couldn't find similar to findbysql

I create a model Last_point but I couldn't use it with the calass that I found in the help , updateByPK.....

$modelSensor = SensorInfo2::model()->findBySql('SELECT * FROM information_sensors  where sensors_id=       
                              "'.$s['sensors_id'].'"  ; ');    
  
	    
       $sResult = array(intval($modelSensor->information_time ),intval($modelSensor-
                                                                                >information_value ));
      
	  			foreach ($modelSensor as $up) // for each value that I get from previous query 
                                                                  I want to use it to update a row in another 
                                                                    table
 				
 {
 
 // what I have to put here 

   'UPDATE last_point  SET last_point_info = "'.$up['information_time'].'"'
 }

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

                echo $json ;
                
                Yii::app()->end(); 

the result of my select query will be used also.

Many thanks in advance

[Emily Dickinson]

$sensorsId = $s['sensors_id'];
$criteria=new CDbCriteria;
$criteria->compare('sensors_id', $sensorsId);
$sensors = SensorInfo2::model()->findAll($criteria);

foreach ($sensors as $sensor)  {
   $lastPointId = 99;  // Obviously, replace with actual value
   $lastPoint = LastPoint::model()->findByPk($lastPointId);
   $lastPoint->last_point_info = $sensor->information_time;
   if ( ! $lastPoint->save()) {
        throw new CException('Unable to save last point.');
   }
}

p.s. I've taken to using camelCase for database tables and column names, instead of lower case/underscores. It means there's always a direct mapping between the SQL representation and the CActiveRecord representation. So in this case, my table name would be sensor, the PK for the sensor table would be sensorId, etc.

Also, (just curious!), your last_point table strikes me as a table which contains values derived from other tables. Is it really needed, or can the values that it stores be derived on the fly?

[DonVito]

Many thanks Emily for your Help !

I am really newbie in php database and all web design staff, I use them for research purpose to display some data from sensors, that's why my post seems a little bit strange!

Any way, I have an action in my controller, that return the last entry in my table sensor every second by an ajax call, at the same time my table is updated with new data. So to make my action return the entries in order , I make this last_point table and I store on it the last retrieved data (after select), like this I can add a condition where to my initial query to compare with the last retrieved data. I a right or there is a better solution ?

thank you again

[Emily Dickinson]

To make your entries get returned in order, do

ORDER BY information_time ASC # Ascending sort
or
ORDER BY information_time DESC # Descending (reverse) sort - i.e., newest first

IF all you need is the FIRST result (after either sorting), then add
LIMIT 1

to the end of the query.

And get a basic MySQL book. :-)

[DonVito]

Many thanks !
I get my table last point updated.

I still have one problem , how can I select from different
models ? select from sensor model where the condition last point is in another model ?
(select from sensor where last_point_time <information_time )

[Emily Dickinson]

Fetch the condition last point from your first model.
See CDbCriteria for fetching a CActiveRecord based on search criteria, using find() or findAll() method of CActiveRecord.
Set variable $value to the column desired from search result.

Use that value to search the sensor table, also using CDbCriteria.

Loads of examples here on the forum; search for CDbCriteria

[DonVito]

I do the same as you requested (I hope so) but I get the fetched point in disorder when I simulate the update of my sensor table

$criteria1=new CDbCriteria;
$sensorsIds = $s['sensor_id'];
$criteria1->compare('sensor_id', $sensorsIds);
$point = Lastpoint::model()->find($criteria1);
$value= ($point->last_point_time);
	


$criteria2=new CDbCriteria;
$criteria2->compare('lead_id', $sensorsIds);
$criteria2->addcondition('information_time'> $value);
$criteria2->order = "information_time asc";
$sensors = SensorInfo2::model()->find($criteria2);
$sResult = array(($sensors->information_time ),(float)($sensors->information_value ));

  foreach ($sensors as $sensor)  {
   $lastPointId = 1;  // Obviously, replace with actual value
   $lastPoint = Lastpoint::model()->findByPk($lastPointId);
   $lastPoint->last_point_time = $sensors->information_time;
   if ( ! $lastPoint->save()) {
        throw new CException('Unable to save last point.');
   }
 		}

I suppose that I have 100 entries, after that I start my simulation update , since the time of the entry 100 is saved in the last_point table
my query should find that time of entry 101 > last_point and retrieved me entry 101 and so on, but I get 102 , 104 , 107 ... . The condition that I added doesn't work. What 's wrong ?

Thanks again

[Emily Dickinson]

I'm going to exit here. Getting into some issues as to how you structure your database, etc.; beyond the scope of what I do here on the forum. Perhaps someone else would like to tackle this?!