Question on SQL queries

[gydi]

There are 2 tables
1. Athletes names, team (id from table 2) and points - table pirgos2017 field:teams2017 field:sum

2. Name of the team and the sum of the points of the team (from 1 table) - table:teams field:id field:sumteam


Can a good soul write SQL

Which by "id" from table 2 "teams" sums from the 1st table the points of the team of the "sum" field and write them in 2 tables "teams" in the "sumteam" field ?

Something like
$ Db = JFactory :: getDbo ();
$ Query = $ db-> getQuery (true);
...............................

for plugin php_event onPreloadData

http://pirgos.resort.name/bg/

 

[cheesegrits]

The easier way would be to do it on submission of table 1 (athletes). So whenever an athlete row is changed, you just re-calc all the team totals.

So a PHP plugin, running 'onAfterProcess':

$myDb = JFactory::getDbo();
$myQuery = "
UPDATE teams AS t
INNER JOIN(
   SELECT team_id, SUM(sum) as total
   FROM athletes
   GROUP BY team_id
) AS a ON t.id = a.team_id
SET t.sumteam = a.total
";
$myDb->setQuery($myQuery);
$myDb->execute();

Test that query by hand in phpMyAdmin. Obviously change the table and field names as appropriate.

-- hugh

 

[gydi]

The easier way would be to do it on submission of table 1 (athletes). So whenever an athlete row is changed, you just re-calc all the team totals.

So a PHP plugin, running 'onAfterProcess':

$myDb = JFactory::getDbo();
$myQuery = "
UPDATE teams AS t
INNER JOIN(
   SELECT team_id, SUM(sum) as total
   FROM athletes
   GROUP BY team_id
) AS a ON t.id = a.team_id
SET t.sumteam = a.total
";
$myDb->setQuery($myQuery);
$myDb->execute();

Test that query by hand in phpMyAdmin. Obviously change the table and field names as appropriate.

-- hugh

Tenks!

I just made a new field - a type of "Cal"
I put this code and things are getting, however, does not want to arrange from great to little -no order work.

$team_id='{teams___id}';
if($team_id > 1){

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

//SELECT sum( `sum` ) FROM `pirgos2017` WHERE `teams2017` =4

$query->select('sum('.$db->quoteName('sum').')');
$query->from($db->quoteName('pirgos2017'));
$query->where($db->quoteName('teams2017'). ' = ' . $team_id);

$db->setQuery($query);
//echo $query;
$results = $db->loadResult();
return $results;
}
else{
return '0';
}

I'll try your code too

 

[gydi]

In list, plugin, php and plugin php_events, There is no events 'onAfterProcess' in php_event There is "proces"

 

[gydi]

you cod work. Tenks! put "onPreLoadData"

$team_id='{teams___id}';
if($team_id > 1){

$myDb = JFactory::getDbo();
$myQuery = "
UPDATE teams AS t
INNER JOIN(
  SELECT teams2017, SUM(sum) as total
  FROM pirgos2017
  GROUP BY teams2017
) AS a ON t.id = a.teams2017
SET t.sumteam = a.total
";
$myDb->setQuery($myQuery);
//echo $myQuery;
$myDb->execute();
}

http://pirgos.resort.name/bg/teams-2017

 

[cheesegrits]

The easier way would be to do it on submission of table 1 (athletes). So whenever an athlete row is changed, you just re-calc all the team totals

I was suggesting doing it in a form submission script. Assuming the only time those individual athlete sums get changed is when the athlete form is submitted, then that's the only time you need to recalculate the team sums. Doing it every time you display the list is unnecessary.

-- hugh