I have a database table with about 7k rows/entries. There is one column, which I have to fill it in with calculated values. I have therefore created a PHP script to do this for each row by calling a function to do the calculation and then UPDATE the entry, but it takes quite long to finish. I am therefore asking for ways to make it faster.
NB: I have also put the function in custom_functions.php for automatic updating of field during insert or update event.
Thank you in advance.
Richie
[pre]
```
// Create connection to Database
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
//Prepare SQL guerry for selecting a few columns;
$sql = "SELECT `dadabik_id`, `sales_agreement_number`, `sales_date`, `ERs` FROM $ccer_table WHERE 1 ORDER BY `dadabik_id`";
$result = mysqli_query($conn, $sql);
// Start working on rows - output data of each row
if (mysqli_num_rows($result) > 0)
{
while ($row = mysqli_fetch_assoc($result))
{
$sales_date = $row["sales_date"];
$dadabik_id = $row["dadabik_id"];
$end_of_monitoring_period = "2020-07-30";
$ERs = dadabik_get_ERs_emissions_reductions($sales_date, $end_of_monitoring_period);
$sql = "UPDATE $ccer_table SET `ERs`= $ERs WHERE `dadabik_id` = $dadabik_id";
if (mysqli_query($conn, $sql))
{
echo " Record updated successfully on table " . $ccer_table . " with ID = " . $dadabik_id . "<br>";
}
else
{
echo " Error updating record: " . mysqli_error($conn);
}
}
}
else
{
echo "0 results";
}
mysqli_close($conn);
function dadabik_get_ERs_emissions_reductions($sales_date, $end_of_monitoring_period)
{
$NCV_biomass = 0.015;
$EF_projected_fossilfuel = 81.6;
$fNRB_y = 77 / 100;
$N_old = 10;
$N_new_xyear_old = 28.59;
$N_new_1year_old = 28.59;
$N_new_2year_old = 28.59; // asumptions only!
$N_new_3year_old = 28.59; // asumptions only!
//$Fuel Savings=(1 - $N_old / $N_new_xyear_old) = 65%;
$Fuel_Savings = 65 / 100;
$C_y_fueltype_region_old = 2.275; //GACC Case Project Baseline Study Report for Rwanda (Aug. 2008)
$U = 100 / 100; // Usage Survey
$L = 95 / 100; //AMS II.G./Version 03 clause 23
$Applied_Weighted_Factor_for_Continued_Use_of_Baseline = 99.99 / 100;
$Days_in_a_Full_Year = 365;
$Commissioning_Date_Delay = 9;
$sales_date = $sales_date;
$sales_date_s = strtotime($sales_date);
$end_of_monitoring_period_s = strtotime($end_of_monitoring_period);
$datediff = $end_of_monitoring_period_s - $sales_date_s;
$Crediting_Days = round($datediff / (60 * 60 * 24)) - $Commissioning_Date_Delay;
$t_fraction = $Crediting_Days / $Days_in_a_Full_Year;
$N = $U * $t_fraction;
$B_old = $N * $C_y_fueltype_region_old * $L;
$B_y_savings = $B_old * $Fuel_Savings; // simplified
$B_y_savings = $B_old * (1 - $N_old / $N_new_xyear_old);
$ER_y = $B_y_savings * $fNRB_y * $NCV_biomass * $EF_projected_fossilfuel;
// English notation without thousands separator
$ER_y_english_format_number = number_format($ER_y, 2, '.', '');
return $ER_y_english_format_number;
}
```
[/pre]
NB: I have also put the function in custom_functions.php for automatic updating of field during insert or update event.
Thank you in advance.
Richie
[pre]
```
// Create connection to Database
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}
//Prepare SQL guerry for selecting a few columns;
$sql = "SELECT `dadabik_id`, `sales_agreement_number`, `sales_date`, `ERs` FROM $ccer_table WHERE 1 ORDER BY `dadabik_id`";
$result = mysqli_query($conn, $sql);
// Start working on rows - output data of each row
if (mysqli_num_rows($result) > 0)
{
while ($row = mysqli_fetch_assoc($result))
{
$sales_date = $row["sales_date"];
$dadabik_id = $row["dadabik_id"];
$end_of_monitoring_period = "2020-07-30";
$ERs = dadabik_get_ERs_emissions_reductions($sales_date, $end_of_monitoring_period);
$sql = "UPDATE $ccer_table SET `ERs`= $ERs WHERE `dadabik_id` = $dadabik_id";
if (mysqli_query($conn, $sql))
{
echo " Record updated successfully on table " . $ccer_table . " with ID = " . $dadabik_id . "<br>";
}
else
{
echo " Error updating record: " . mysqli_error($conn);
}
}
}
else
{
echo "0 results";
}
mysqli_close($conn);
function dadabik_get_ERs_emissions_reductions($sales_date, $end_of_monitoring_period)
{
$NCV_biomass = 0.015;
$EF_projected_fossilfuel = 81.6;
$fNRB_y = 77 / 100;
$N_old = 10;
$N_new_xyear_old = 28.59;
$N_new_1year_old = 28.59;
$N_new_2year_old = 28.59; // asumptions only!
$N_new_3year_old = 28.59; // asumptions only!
//$Fuel Savings=(1 - $N_old / $N_new_xyear_old) = 65%;
$Fuel_Savings = 65 / 100;
$C_y_fueltype_region_old = 2.275; //GACC Case Project Baseline Study Report for Rwanda (Aug. 2008)
$U = 100 / 100; // Usage Survey
$L = 95 / 100; //AMS II.G./Version 03 clause 23
$Applied_Weighted_Factor_for_Continued_Use_of_Baseline = 99.99 / 100;
$Days_in_a_Full_Year = 365;
$Commissioning_Date_Delay = 9;
$sales_date = $sales_date;
$sales_date_s = strtotime($sales_date);
$end_of_monitoring_period_s = strtotime($end_of_monitoring_period);
$datediff = $end_of_monitoring_period_s - $sales_date_s;
$Crediting_Days = round($datediff / (60 * 60 * 24)) - $Commissioning_Date_Delay;
$t_fraction = $Crediting_Days / $Days_in_a_Full_Year;
$N = $U * $t_fraction;
$B_old = $N * $C_y_fueltype_region_old * $L;
$B_y_savings = $B_old * $Fuel_Savings; // simplified
$B_y_savings = $B_old * (1 - $N_old / $N_new_xyear_old);
$ER_y = $B_y_savings * $fNRB_y * $NCV_biomass * $EF_projected_fossilfuel;
// English notation without thousands separator
$ER_y_english_format_number = number_format($ER_y, 2, '.', '');
return $ER_y_english_format_number;
}
```
[/pre]