• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

How to update fields in a list submitting values in another One?

pie

Member
Hi, I have to put together an amateur football ranking list.

I have a list (club list) containing each "club" And its ranking values:
Club name (textfield),goal scored (textfield), goal conceded (textfield), match won( (textfield), matches lost (textfield), matches draw (textfield), points (calc)

One list containing matches (match_list):
Club1(dbjoin with club list),club2(dbjoin with club list), goals scored by club 1(textfield),goals scored by club 2(textfield), final result(yes/no)

I'd like to update the club_list when updating goals scored by club1(result1) and goals scored by club 2(result2) in table matches list (if final result=yes)

I believe it can be done, but I need some hint on how to start.

My "logic" would be something that is triggered on matches list submit, if final result = yes.
Here is what I believe should happen:
add result1 to club_list[club1][goal scored]
add result2 to club_list[club1][goal conceded]

do the same for club2 and compare result1 & result2 to determine if the match is won, lost or draw, and add 1 to the relative element in club_list[club]



Thank you

[edit: additional info added]
 
Last edited:
My suggestion would be to have a MySQL view to display the club results. A view is kind of a dynamic table, built from a query. Fabrik can use views to build lists from, although they are read only.

So you'd have two lists built from tables, clubs (just the club details, no goal or win info) and matches (as you described), and one built on a view for club_results, which calculates the goals and wins / losses in a view query.

I'm on my phone, on the road atm, so I can't help build the view query, but you could do a little research while I get where I'm going and have my laptop connected.


Sent from my HTC6545LVW using Tapatalk
 
Thanks, I took a look at mysql views and if I get it they are basically saved/stored queries to retrieve tables, so that asking mysql directly we don't waste resources on the website?
I'd like to add a feature though, how do I handle different tournaments? In my "projection" I would have
added a tournament element in the club table
then made a new club entry for each tournament in which they partecipate:
ie
WHITE TEAM - Basic League
RED TEAM - Basic League
RED TEAM - League of the Kings

keeping track of each win/score on that table would have allowed to filter by tournament and keep rankings.
In your "model" instead the tournament would be selected from the match table?
I am still trying to figure out the mysql logic (not even the syntax..) to use here :)
p.s. how should the club_results table be composed in your opinion?

thank you
 
Last edited:
Yes, a view is essentially a stored query that generates the "table" on the fly. But Mysql is pretty good at at caching, so it typically only has to run the view query after any of the tables it selects from are changed.

If you Google for something like "mysql teams matches wins loss" you'll find a number of Stack Overflow threads with queries to build the result set you want. Play around in phpMyAdmin till you have a query that works, then you can turn that query into a view ("create view myviewname as select ..." ), and create a Fabrik list on that (it'll just show up in the dropdown of available tables when you create a list).

I'd create another table for tournaments, and add a join element to that on the results table. Than you can "group by" that as well in your view query, so you get a row per team per tournament.

I'm still on the road (moving house) so can't help with the query atm, but there's a lot of good examples on Stack Overflow, easy to find with some creative search term on Google.


Sent from my HTC6545LVW using Tapatalk
 
Ok, thank you for the tips!
I am almost there but I may need your help again:

I have two views: the first does half of the calculations (scores, won, lost, draw from the result of each game) and outputs a "raw table", while the second one should clean it up, but I have 2 things I need to fix:

1) I have "many" tournaments, and each team may compete in many of them.
Right now the query doesn't keep count of that, and groups every recurring team overwriting the tournament value.
I guess there should be a way to check if the tournament value is the same one when grouping, but I don't know how to do it.


2)If the first issue is fixed I may have the same team listed twice, or more, so i will no longer have a primary key.
I assume that the primary key is mandatory, is it possible to assign one while running the query?
I would still need the tournament colums to filter it later in fabrik.

rankings_full table
Code:
Team     P     W     D     L     F     A     GD     tournament     PTS
1     1     0     0     1     1     5     -4     1     0
1     1     1     0     0     3     1     2     1     3
2     1     1     0     0     2     1     1     1     3
2     1     1     0     0     2     1     1     1     3
3     1     0     1     0     1     1     0     1     1
5     1     0     1     0     2     2     0     2     1
6     1     1     0     0     4     1     3     2     3
3     1     1     0     0     5     1     4     1     3
2     1     0     0     1     1     3     -2     1     0
3     1     0     0     1     1     2     -1     1     0
1     1     0     0     1     1     2     -1     1     0
2     1     0     1     0     1     1     0     1     1
4     1     0     1     0     2     2     0     2     1
5     1     0     0     1     1     4     -3     2     0

This is the second query, which should become the base view to build the rankings fabrik list.
Code:
SELECT
  Tournament, tname AS Team, Sum(P) AS P,Sum(W) AS W,Sum(D) AS D,Sum(L) AS L,
  SUM(F) as F,SUM(A) AS A,SUM(GD) AS GD,SUM(Pts) AS Pts
FROM rankings_full as tot
JOIN teams t ON tot.Team=t.id
GROUP BY Team
ORDER BY SUM(Pts) DESC ;

thank you
 
Last edited:
Allright, I think I got it working.
It seems that I don't need a unique primary key here, I needed a primary key but not unique (for the records, error 500 without it).
Can you see "apocalypse coming" in a near future for my website from this inconsistence?

Thank you
 
It kinda depends what you are doing with the list. As long as you never load form or details views of it, and only use it as a simple list display, and maybe link to team and match records (on their respective form ids, for which you'd need to include those in the view with appropriate names), it will probably be OK.



Sent from my HTC6545LVW using Tapatalk
 
If it does turn into an issue, there is a way to create a pseudo PK in a view, using a stored procedure to generate the incrementing ID. Usually to have an incrementing variable in a select, you'd just use variables ... but you can't use variables in a view's select, so you have to create a stored procedure to do it. This Stack Overflow Q&A has an example:

https://stackoverflow.com/questions/15891993/create-a-view-with-column-num-rows-mysql

Now it's very much a pseudo PK, as the values could change between rendering a list and clicking on a link. But it would be a band aid if you did run into issues when rendering the list without a unique PK.

-- hugh
 
  • Like
Reactions: pie
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top