[SOLVED}task SQL to update a calc field in the database

[merlino68]

I have a SQL query that updates the calc field in a table.
The query is this and it works.

update tm_allenamenti SET gg_scad=gg_scad +1 ,stato_allenamento = if(gg_scad<-7,'Attivo',if(gg_scad>0,'Scaduto','In scadenza'))
where gg_scad<100

Unfortunately MySql does not allow me to create the task.
I can use the task function fabrik?
how should I change the query

Thank

 

[cheesegrits]

When you say "task", do you mean you want to set up a scheduled task to do it?

If so, you could set up a PHP scheduled task, don't assign it a list, and run the query ...

$myDb = JFactory::getDbo();
$myQuery = "update tm_allenamenti SET gg_scad=gg_scad +1 ,stato_allenamento = if(gg_scad<-7,'Attivo',if(gg_scad>0,'Scaduto','In scadenza')) where gg_scad<100");
$myDb->setQuery();
$myDb->execute();

 

[merlino68]

I tried .... the task runs but does not update the records, the message is "0 records updated"
I also tried to simplify the query but the error is the same.
The query in simple but ..."0 records updated"
$myDb = JFactory::getDbo();
$myQuery = "update tm_allenamenti SET gg_scad=gg_scad +1");
$myDb->setQuery();
$myDb->execute();

 

[merlino68]

this Work

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "UPDATE tm_allenamenti SET gg_scad = gg_scad+1,stato_allenamento=if(gg_scad<-7,'Attivo',if(gg_scad>0,'Scaduto','In scadenza'))";

$db->setQuery($query);
$result = $db->execute();

 

[cheesegrits]

Woops, yes, my bad, forgot the $myDb->setQuery($myQuery).

-- hugh

 

[merlino68]

No problem, I solved looking in the forum.....here:http://fabrikar.com/forums/index.php?threads/correct-way-to-do-list-update-php-form-plugin.44717/
Thank you

 

[cheesegrits]

BTW, if you are using a simple string for the query, you don't need the getQuery(true), and that's only used with the "query builder". The code for that would be ...

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
    ->update('tm_allenamenti')
    ->set("gg_scad = gg_scad + 1 , stato_allenamento = if(gg_scad < -7, 'Attivo', if(gg_scad > 0, 'Scaduto', 'In scadenza'))")
    ->where('gg_scad<100');
$myDb->setQuery($myQuery);
$myDb->execute();

 

[merlino68]

Ok I immediately change ..... thanks