Member Avatar for JayJ

Hi,

I require some help in making the following possible with mySQL.

My [simplified] database layout is as follows

UserID
Timestamp
League
Field 1
Field 2
Field 3
Field 4
Field 5
Field 6

Fields 1-6 values are either "yes" or "no"

Basically a user submits a form selecting the League they wish to update. They tick the checkboxes to state whether something has been done or not.

e.g.

UserID, Timestamp, League 1, Yes, No, No, No, Yes, No

Another user can then submit a form with what they have done but it may read:

UserID, Timestamp, League 1, No, Yes, Yes, No, No, No

Obviously, from those 2 submissions I can see that Fields 1, 2, 3 and 5 have all had a 'Yes' value stating those items have been completed. Fields 4 and 6 have on both occassions not been done (and reads "no").

User 3 may then come along and update League 2 which has no impact on League 1.

What I am looking for is a mySQL query that outputs the rows as a collective with "Yes" for each League (League 1, League 2, League 3, League 4) so the query would come back with one string:

League 1, Yes, Yes, Yes, No, Yes, No
League 2, No, No, No, No, No, No

Any assistance would be greatly appreciated

Kind Regards


JJ

  • 3 Contributors
  • 5 Replies
  • 118 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by JayJ

Dani AI

Generated

Good call by — reduce the history rows per league into one summary row where each field is "yes" if any submission for that league ever had a yes. That gives the exact per‑league view you want while keeping the submission history intact. 's point is also valid: if history is not needed, keeping a single current row per league and updating it on each submission is simpler and faster.

Practical notes and choices:

  • Store the flags as booleans (0/1) rather than free text. It makes aggregation cheap and avoids string-comparison quirks.
  • To derive the summary, convert each field to a numeric 0/1 and do a per-league reduction so the summary shows "yes" when the reduction is positive (any yes). This can be exposed as a view so reads are simple.
  • If you also need to know who set a particular field to yes (first or last), query the history table for the min/max timestamp where that field = yes and join back to the user.

Schema and performance suggestions:

  • Normalizing (one row per league+field+submission) makes the aggregate logic easier if the number of fields may change.
  • If the history table grows large and reads are frequent, maintain a small summary table (one row per league) that you update transactionally on insert, or refresh periodically. This avoids scanning the full history on every read.
  • Add indexes on the league column and on (league, timestamp) if you query latest/earliest changes.

If keeping history, prefer the aggregate view + history log pattern. If you only ever need current state, use the single-row-per-league update approach that suggested.

Recommended Answers

All 5 Replies

Member Avatar for griswolf

If you don't need to keep track of the history of the changes, then if you merely UPDATE the row instead of adding a new row each time, your problem is solved.

To keep a history, you can store a "running total" so that your "view" query only looks at the latest row (order by timestamp desc limit 1)

Member Avatar for JayJ

The issue there would be that an UPDATE query would overwrite all fields? As such if organiser 1 was to update Yes Yes No No No Yes and organiser 2 was to update with all No - then it would update all to No?

Keeping track of the changes was more of an administration thing to keep track of who is doing what and who has applied which change.

Member Avatar for smantscheff

Maybe you mean something like this:

drop table leagues;
create table leagues (id integer, f1 enum('no', 'yes'), f2 enum('no','yes'));
insert into leagues values 
(1,'no','no'),
(1,'yes','no'),
(2,'no','no');
select id, 
if(sum(if(f1 = 'no', 0, 1)) = 0, 'no', 'yes') as field1, 
if(sum(if(f2 = 'no', 0, 1)) = 0, 'no', 'yes') as field2
from leagues
group by id;

+------+--------+--------+
| id   | field1 | field2 |
+------+--------+--------+
|    1 | yes    | no     |
|    2 | no     | no     |
+------+--------+--------+
commented: Thankyou very much for your assistance - this worked :) +6
Member Avatar for JayJ

That could be it, smantscheff - I will try that tomorrow and will update you accordingly :)

Member Avatar for JayJ

Thankyou smantscheff :) Exactly what I was looking for in the end

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.