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

Re: Select query help 80 80

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)

Re: Select query help 80 80

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.

Re: Select query help 80 80

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
Re: Select query help 80 80

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

Re: Select query help 80 80

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 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.