[SOLVED] update col plugin - 'update date' doesn't get local time

[bea]

Hi,
I am using the update_col plugin to update a value on the list. The additional feature of updating user and date works well, but the time is ignoring the settings on the date element (local time).

Also I want to update an element with matched the same condition with post eval script, but this doesn't work. I don't know, if this is made for this....

$db= JFactory::getDbo();
$query=$db->getQuery(true);
$model = $this->getModel();

$fields=array(
   $db->quoteName('checked_sum') . ' = 1'
);

foreach ($ids as $id) {
   $row = $model->getRow($id, true);
   $conditions=array(
      $db->quoteName('machine') . ' = ' . $row->fab_cp1_line_tour___machine_raw,
      $db->quoteName('date') . ' = ' . $row->fab_cp1_line_tour___date_raw,
      $db->quoteName('shift') . ' = ' . $row->fab_cp1_line_tour___shift_raw
   );
   $query->clear()->update($db->quoteName('fab_cp1_line_tour'))->set($fields)->where($conditions);
   $db->setQuery($query);
   $db->execute();
}

Cheers,
Bianka

 

[cheesegrits]

Hmmm, yeah, hadn't thought about that with the date stuff in update_col. I do that somewhere else in another plugin, so I'll look at copying that code.

I think at the point the eval code runs, $ids is a comma separated string, not an array. But you don't need it, 'cos the required row data is already loaded into $data, so the usual ...

foreach ($data as $group) {
   foreach ($group as $row) {
      // $row should be an object with data in $row->yourtable___yourelement (and _raw)
   }
}

-- hugh

 

[bea]

Hi Hugh,
thanks for the information, but i can't get it work.
The update-col plugin should update to elements in row: (checked= 1 and checked_sum=1)
The additional post eval script should also update checked_sum in other rows of the same machine, date and shift.

With this script I don't reach the data of shift, machine and date...(where('shift...). Also if I set fab_cp1_line_tour.shift
I feel confused...
Cheers,
Bianka

$db = JFactory::getDbo();
$query = $db->getQuery(true);
foreach ($data as $group) {
   foreach ($group as $row) {
      $query->clear()
        ->update('fab_cp1_line_tour')
        ->where('shift = ' . (int)$row->fab_cp1_line_tour___shift_raw)
        ->where('date = ' . $row->fab_cp1_line_tour___date_raw)
        ->where('machine = ' . (int)$row->fab_cp1_line_tour___machine_raw)
        ->set('checked_sum = ' . ' = 1');
      $db->setQuery($query);
      $db->execute();
   }
}

 

[cheesegrits]

Have you dumped data to see what's in there?

-- hugh

 

[bea]

Hi Hugh,

the output is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 1 WHERE shift = 2 AND date = 2019-05-30 00:00:00 AND machine = 1' at line 2

I will do something different to display what I want in my boards. But it would be great, to get a sample of 'Pre Eval' and 'Post Eval' script in Wiki for update_col plugin.

Do you have a chance to update the script for local time?

Cheers,
Bianka

PS: Would be great to ping me on Skype, if you have time

 

[bea]

friendly bump

 

[cheesegrits]

That query error is because you didn't quote the date ...

->where('date = ' .  $db->quote($row->fab_cp1_line_tour___date_raw))

Basically, anything which isn't a number needs to be quoted when using it as a value.

I haven't looked at the date thing yet.

-- hugh

 

[bea]

Hi Hugh,
finally I had to quote the checked_sum as well...
Many thanks for your help
Cheers
Bianka

Final code for post eval:

$db = JFactory::getDbo();
$checked = '1';
$query = $db->getQuery(true);
foreach ($data as $group) {
   foreach ($group as $row) {
      $query->clear()
        ->update('fab_cp1_line_tour')
        ->where('shift = ' . (int)$row->fab_cp1_line_tour___shift_raw)
        ->where('date = ' .  $db->quote($row->fab_cp1_line_tour___date_raw))
        ->where('machine = ' . (int)$row->fab_cp1_line_tour___machine_raw)
        ->set('checked_sum = '.$db->quote($checked));
      $db->setQuery($query);
      $db->execute();
   }
}