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();
$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?
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 ?
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 ?
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?!