FOREACH和UPDATE mysql无法正常工作

I have a select query followed by a an update query inside a foreach loop.

我有一个select查询,后跟foreach循环中的更新查询。

I can get the data from the select and I use it as json to draw a chart, but for the other table that should be updated with the same data from select, the specified filed become 0 sometimes 1 or 2 !!
I tried to use a select for update and it OK (update .... (select ....)) but inside foreach it doesn't work. I tried also to use other columns but the same result, always I get 0. Any idea please ? Many thanks in advance

我可以从select中获取数据并将其用作json来绘制图表,但是对于应该使用select中相同数据更新的另一个表,指定的文件有时会变为0或1或2!我试图使用选择更新,它确定(更新....(选择....))但在foreach内部它不起作用。我也尝试使用其他列,但结果相同,我总是得到0.有什么想法吗?提前谢谢了

this is my code

这是我的代码

public function actionGetSensorsDataLive($beamId) {
    header('Content-Type: application/json; charset="UTF-8"');
    $sensorsIds = Sensor::model()->findAllBySql('SELECT sensor_id FROM sensor where 
                             node_id="' . $beamId . '" and sensor_name = "I" ;');

    foreach ($sensorsIds as $s) {
        $sResult = array();
       $modelSensor = SensorInfo2::model()->findBySql('SELECT * FROM information_sensor  
             where sensor_id= "' . $s['sensor_id'] . '"');

        $sResult = array(($modelSensor->information_sensor_time),
            ($modelSensor->information_sensor_value));
        /////////////// update//////////////// 
        // for every information_sensor_time that I get from the previous query  
         //  I want
        //  to update a row in another table //

        foreach ($modelSensor as $up) {
            $connection = yii::app()->db;

            $sql = 'UPDATE last_point  SET last_point_time = "' . 
                             $up['information_sensor_time'] . '"
            WHERE sensor_id= "' . $s['sensor_id'] . '"  ';

            $command = $connection->createCommand($sql);
            $command->execute();
        }
        /////update end///////               
    }
    echo json_encode($sResult);
    Yii::app()->end();
}

3 个解决方案

#1

 $sensorsIds=Sensor::model()->findAllBySql('SELECT sensor_id FROM lead where 
                         node_id="'.$beamId.'" and sensor_name = "I" ;' );

Remove ;

 $sensorsIds=Sensor::model()->findAllBySql('SELECT sensor_id FROM lead where 
                         node_id="'.$beamId.'" and sensor_name = "I"' );

Now try this.

现在尝试一下。

#2

You are using findBySql()` which only returns one record and then doing a foreach on tha makes no sense. so cheng the line

你正在使用findBySql()`它只返回一个记录,然后在tha上做foreach是没有意义的。所以行了

 $modelSensor = SensorInfo2::model()->findBySql('SELECT * FROM information_sensor  
             where sensor_id= "' . $s['sensor_id'] . '"');

$modelSensor = SensorInfo2::model()->findAllBySql('SELECT * FROM information_sensor  
             where sensor_id= "' . $s['sensor_id'] . '"');

And you shoud use querybuilder which is far secure as suggested by Alex

并且你应该使用如Alex建议的那么安全的querybuilder

#3

finally I used this code

最后我使用了这段代码

foreach ($sensors as $sensor)  {
$lastPointId = 1;  
$lastPoint = LastPoint::model()->findByPk($lastPointId);
$lastPoint->last_point_info = $sensor->information_time;
if ( ! $lastPoint->save()) {
    throw new CException('Unable to save last point.');
}
}

Thanks for all

谢谢大家

#1

 $sensorsIds=Sensor::model()->findAllBySql('SELECT sensor_id FROM lead where 
                         node_id="'.$beamId.'" and sensor_name = "I" ;' );