Greetings to friends PHPRunner.
I speak Spanish, but I will use google translator to write.
I have a master table "catalogo_precios" with the following structure:
CREATE TABLE catalogo_precios (
id_precio int(11) NOT NULL AUTO_INCREMENT,
usuario varchar(20) COLLATE utf8_spanish_ci NOT NULL,
producto varchar(150) COLLATE utf8_spanish_ci NOT NULL,
marca varchar(150) COLLATE utf8_spanish_ci NOT NULL,
modelo varchar(100) COLLATE utf8_spanish_ci NOT NULL,
unidad_medida varchar(50) COLLATE utf8_spanish_ci NOT NULL,
cantidad float NOT NULL,
p_min float DEFAULT NULL,
p_prom float DEFAULT NULL,
observacion varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
fecha_act datetime NOT NULL,
PRIMARY KEY (id_precio)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
And a detail table "detalle_cotizaciones" which has the following structure:
CREATE TABLE detalle_cotizaciones (
id bigint(20) NOT NULL AUTO_INCREMENT,
id_precio bigint(20) NOT NULL,
usuario varchar(20) COLLATE utf8_spanish_ci NOT NULL,
proveedor varchar(150) COLLATE utf8_spanish_ci NOT NULL,
precio double NOT NULL,
fecha_cotizacion date NOT NULL,
observaciones varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
fecha_act date NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
The graph shows the appearance of the application:
What I want to achieve is that the "p_prom" and "p_min" master table "catalogo_precios" is automatically updated with the average and minimum respectively of the entries in the table "quotes detail" at the time of recording registration.
I tried to create code in the event "after add" the button "ADD PAGE" the table "catalogo_precios" which is as follows:
global $dal;
//Calculo de Minimo
$tblUsers = $dal->Table("catalogo_precios");
$tblUsers->Param["id_precio"]=$values["id_precio"];
$sql = "select min(detalle_cotizaciones.precio) as precio_minimo from detalle_cotizaciones WHERE detalle_cotizaciones.id_precio=".$values["id_precio"];
$rs = CustomQuery($sql);
$data = db_fetch_array($rs);
$tblUsers->Value["p_min"]=$data["precio_minimo"];
$tblUsers->Update();
//Calculo de Promedio
$tblUsers = $dal->Table("catalogo_precios");
$tblUsers->Param["id_precio"]=$values["id_precio"];
$sql = "select avg(detalle_cotizaciones.precio) as precio_promedio from detalle_cotizaciones WHERE detalle_cotizaciones.id_precio=".$values["id_precio"];
$rs = CustomQuery($sql);
$data = db_fetch_array($rs);
$tblUsers->Value["p_prom"]=$data["precio_promedio"];
$tblUsers->Update();
return true;
It does not work, according to my conclusion this is because the field $ values [id_precio], as has not yet been stored in the database, has no value (since it is auto increment type) and therefore the entire statement sql returns nothing.
But to put the same code on the "Edit Page" in the event "after add" table "catalogo_precios" if it works, that is if you upgrade.
global $dal;
//Calculo de Minimo
$tblUsers = $dal->Table("catalogo_precios");
$tblUsers->Param["id_precio"]=$values["id_precio"];
$sql = "select min(detalle_cotizaciones.precio) as precio_minimo from detalle_cotizaciones WHERE detalle_cotizaciones.id_precio=".$values["id_precio"];
$rs = CustomQuery($sql);
$data = db_fetch_array($rs);
$tblUsers->Value["p_min"]=$data["precio_minimo"];
$tblUsers->Update();
//Calculo de Promedio
$tblUsers = $dal->Table("catalogo_precios");
$tblUsers->Param["id_precio"]=$values["id_precio"];
$sql = "select avg(detalle_cotizaciones.precio) as precio_promedio from detalle_cotizaciones WHERE detalle_cotizaciones.id_precio=".$values["id_precio"];
$rs = CustomQuery($sql);
$data = db_fetch_array($rs);
$tblUsers->Value["p_prom"]=$data["precio_promedio"];
$tblUsers->Update();
return true;
What am I doing wrong or what's missing in the code?.
Can it be that the event "after add" not working correctly?
At the time of execution of the event "after add", the record has already been stored in the database?
I hope your answers friends.
