Execute additional SQL when form is saved


(Torsten) #1

Hi,
I have a PostgreSQL/PostGIS database. In my table I have columns for latitute and longitude and geometry. In the form the user provides latitute and longitude. Now I want to run the additional SQL command UPDATE epichloe.epichloe_db_main SET geom = ST_GeomFromText('POINT($lat $lon)', 4326) WHERE id = $id when the form is saved.

Where would I include this?

Cheers,
Torsten


(Torsten) #2

I found a solution. In my Controller I added the extra SQL statement here:

public function actionUpdate($id)
{
$model = $this->findModel($id);

if ($model->load(Yii::$app->request->post()) && $model->save()) {            
    $jki_id = $model->jki_id;
    $lat = $model->latitude_dec;
    $lon = $model->longitude_dec;
    Yii::$app->db->createCommand("UPDATE epichloe.epi_main SET geom = ST_GeomFromText('POINT($lat $lon)', 4326) WHERE jki_id = '$jki_id'")->execute();
    
    
    return $this->redirect(['view', 'id' => $model->id]);
}

return $this->render('update', [
    'model' => $model,
]);

}

Cheers,
Torsten