How can I force dadabik to update all calculated fields in my DB?

richardm

New member
Hello,

I have just added a calculated field into my DB with over 5,000 entries and is working well. The filed is about a calculation of days difference; today less date of entry.

1) How can I force Dadabik to update and save all entries?

2) The field will be changing on a daily basis. How can I update the calculated field automatically? Let us say, whenever one is browsing the result table?

Thank you.



[pre]
PHP Version: 7.1.8
mysql version: 5.5.5-10.1.26-MariaDB
Web server: Apache/2.4.27 (Win32) OpenSSL/1.0.2l PHP/7.1.8
Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101 Firefox/78.0
[/pre]
 

richardm

New member
You are using DaDaBIK version 10.2-Manarola enterprise, installed on 16-07-2020 (installation code: 156855ef91af98bfec), the latest version of DaDaBIK is 10.2-Manarola released on 15-07-2020



richardm Wrote:
-------------------------------------------------------
> Hello,
>
> I have just added a calculated field into my DB wi
> th over 5,000 entries and is working well. The fil
> ed is about a calculation of days difference; toda
> y less date of entry.
>
> 1) How can I force Dadabik to update and save all
> entries?
>
> 2) The field will be changing on a daily basis. Ho
> w can I update the calculated field automatically?
> Let us say, whenever one is browsing the result ta
> ble?
>
> Thank you.
>
>
>
> [pre]
> PHP Version: 7.1.8
> mysql version: 5.5.5-10.1.26-MariaDB
> Web server: Apache/2.4.27 (Win32) OpenSSL/1.0.2l P
> HP/7.1.8
> Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64;
> rv:78.0) Gecko/20100101 Firefox/78.0
> [/pre]
 

Martin68

Well-known member
Hello Richard,

i am not sure if dadabik is the right starting point - i would start from your database!

Please give me some informations, what exactly you need.

Do you always need a column which is from no tow days before, or is it a columns where you at inserting data save the day two days before inserting?

In the first alternative i would presume a select which gives you a dynamic date-column, something like this:

SELECT
*,
NOW() as today,
DATE_SUB(NOW(), INTERVAL 2 DAY) as daybeforeyesterday
FROM dadabik_users

Should word for your database too, as an example!

If you want to generate new values you have stored in your database, you need to UPDATE the fields. Dont know the structure of your table. Let's use a thought up table named Tabelle with the columns: id, outdated_date, reference_date, content

If you want to calculate the outdated_date and store it in your database, you could try something like this:

UPDATE Tabelle
SET outdated_date = DATE_SUB(reference-date, INTERVAL 2 DAY)

If you don't want to update all rows you must think of using a WHERE restriction!

hth!

Kind regards

Martin












richardm Wrote:
-------------------------------------------------------
> Hello,
>
> I have just added a calculated field into my DB wi
> th over 5,000 entries and is working well. The fil
> ed is about a calculation of days difference; toda
> y less date of entry.
>
> 1) How can I force Dadabik to update and save all
> entries?
>
> 2) The field will be changing on a daily basis. Ho
> w can I update the calculated field automatically?
> Let us say, whenever one is browsing the result ta
> ble?
>
> Thank you.
>
>
>
> [pre]
> PHP Version: 7.1.8
> mysql version: 5.5.5-10.1.26-MariaDB
> Web server: Apache/2.4.27 (Win32) OpenSSL/1.0.2l P
> HP/7.1.8
> Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64;
> rv:78.0) Gecko/20100101 Firefox/78.0
> [/pre]
 

richardm

New member
Hi Martin,

Thank you for your reply.

Maybe I was not clear enough. My problem was to update all records in one field.

I have found a solution already. I created a script which is running well: It calls a function to do the calculations. My remaining problem is only that it takes more than say 10 minutes (well beyond the maximum execution time of 300 seconds)  to do the calculations and to update the over 6,000 records! On my local server, I just needed to make some changes in php.ini

When I add a new record or when I update a record, the “Calculated fields” by Dadabik is working very well too.


[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);

[/pre]
 

Martin68

Well-known member
Hi Richard,

i am sorry, i am no professional developer.

Just 2 thoughts:

a) Does your function "dadabik_get_ERs_emissions_reductions" use database actions? could last too long.... open db connection, generate query, execute and close connection again and again.

b) have read about "prepared statements" as a way to speed up database functions - do you know this technic?

Kind regards

Martin
 

richardm

New member
Martin68 Wrote:
-------------------------------------------------------
> Hi Richard,
>
> i am sorry, i am no professional developer.
>
> Just 2 thoughts:
>
> a) Does your function "dadabik_get_ERs_emissions_r
> eductions" use database actions? could last too lo
> ng.... open db connection, generate query, execute
> and close connection again and again.
>
> b) have read about "prepared statements" as a way
> to speed up database functions - do you know this
> technic?
>
> Kind regards
>
> Martin


Hi Martin!

Thank you for your reply and for the ideas. I am also not an expert in PHP coding either.

I also do no know how to use "prepared statements" as a way to speed up database functions.
 

DAF ID3000

New member
IMHO, the best solution is to use triggers directly in MySQL (or MSSQL) DB.
If your calculated field is defined in the same table as inserted/updated record, you need to write two triggers, the first on BEFORE_INSERT event, and the second on BEFORE_UPDATE...
 
Top