Update column with condition without saving row by row

[Maud67]

Hi,

Question:
What is the best solution for the following:

I have a list with imported data.
I use the update column plugin multiple times.

With other words: I don't use the save button in the form.

Now I added a ccd and a related calc element.
Situation is now that I have to update every row and hit the save button to get the data.
That is not preferable.

I searched the forum and I'm not sure what to do:
PHP Form : not an option in this case I think.
PHP List: creates a button which is not preferable.
PHP Events list: I'm not sure if this the right solution according to the examples.
I also found a threat using a cron job.

Can somebody give me some directions what to do best and how?

Thanks a lot!

 

[troester]

If you add new columns (elements) to existing tables containing data you must fill those columns manually (except you have a calc element which is calculating "on the fly" and uses only existing fields).
You can do it directly in your database with eg phpMyAdmin or create a Fabrik scheduled task with a php plugin doing the database modification (which is in this case no "scheduled" task but just a task you can run manually from the backend; you can keep it disabled).

In any case backup your table.

 

[Maud67]

Hi Troester,

Thanks for responding.
Due to your answer I did re read the threat with the php cron job again.
You suggested to use this code:

Code (Text):
$data =$data[0];

foreach ($data as $row) {

//$calcresult = your calc code adapted

//set query: update your-table set calc = $calcresult where id = $row->your-table___id_raw
//echo query for debug
//don't run execute if debugging

}
exit;

I will try this out but the only question I have is:
Can I run this automatically once a week on prefiltered data?

I guess it will have impact on the performance but I will try it out anyway.

Thanks a lot!

 

[juuser]

Hi,

I'm using similar script that you have in your last post in the list "php_events" plugin (onPreLoadData). List has several hundred records and I cannot notice decrease in performance. List load time increases probably some milliseconds.

That way the list is always up to date.

 

[cheesegrits]

@Maud67 what exactly does your calc do? If it only reference data from the same row in the table, there's no need to loop round every row, updating each row individually (which could be a very costly operation). For example, if you were concatenating a "first_name" and "last_name" field into a single "full_name" field, you would just do ...

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update('mytable')->set('last_name = CONCAT(first_name, " ", last_name)');
$db->setQuery($query);
$db->execute();

... which would update all rows in one query.

-- hugh

 

[Maud67]

Hi both,

Thank you for responding.

This is what is happening:

A new import is added to the list every week. Average 150 new rows.
The list will be edited using the update column plugin (multiple buttons).
Meaning selecting multiple rows and update by clicking a button.
So, no save button is used.

One column contains date and time.
One column contains project number.
Now the request is to convert this date and time to an estimated time in the future based on distance + 1 hour (date and time + distance in time + 1 hour).

The distance in time is calculated in an other list.
That list contains info per project, used throughout the application.
Here you find a.o. project number, distance and calculated distance in time.

I added a ccd for fetching the right distance in time per row in the import list. Look up is project number.
I also added a calc. element to add 1 hour.

The result of the calc element "on the fly" is good enough.
However, I need the distance in time in the db to get the result of the calc element.
But the rows will not be edited. The save button is not used.

In the ccd + calc element situation I basically need to update the "distance in time" column where project number = project number.

But I do understand that when I use PHP Event, I probably can skip the ccd and update just one column.

So, I think PHP Event - onPreLoadData may be the right solution?
Maybe not

But if so, what would have less impact:
Updating the ccd column (and keep the calc element column) or updating the calc element column (and skip the ccd column)?

Thanks a lot!